17 Excel Shortcuts for 2017 - Excel Campus
87

17 Excel Shortcuts for 2017

Bottom line: Learn 17+ Excel shortcuts to help you work faster in the new year.

Skill level: Beginner

17 Excel Shortcuts Save Time Post Thumb

17 Excel Tips & Shortcuts

There are an endless number of tips and shortcuts for Excel.  I think that is what makes it so fun and challenging.  So, I wanted to start 2017 off with 17 Excel tips & shortcuts that will save you time with your job.

The following tips are in no particular order.  These are just some of the most popular tips from the Excel Campus blog and YouTube channel, and some of my favorites.  Please leave a comment at the end of the post with a favorite tip of your own.  We can never have enough Excel tips! 🙂

Note: All the shortcuts below will work on the Windows versions of Excel from 2007 and beyond, unless specified otherwise.  I added notes at the bottom of each tip for the Mac equivalent shortcut where applicable.

Download the Guide

Here is a printable PDF version of the article.

17 Excel Tips Guide - Excel Campus.pdf (624.6 KB)

#1 – Make a Duplicate Copy of a Worksheet

This first one I use A LOT.  Especially when I want create a backup copy of a sheet, or duplicate a sheet so I can make changes without screwing up the original.

The quickest way I’ve found to make a duplicate copy of a sheet is to:

How to Copy a Worksheet with Ctrl Left Click

  1. Left-click and hold on the sheet you want to copy.
  2. Press and hold the Ctrl key.  A plus symbol will appear in the sheet mouse icon.
  3. Drag the sheet to the right until the down arrow appears to the right of the sheet.
  4. Release the left mouse button.  Then release the Ctrl key.

It sounds like a lot, but once you get the hang of it you will wonder how you ever lived without this trick.  It’s much faster than right-clicking the tab and going to the Move or Copy… menu.

You can also first select multiple sheets with the Shift key, then use the same method to copy multiple sheets at the same time.

My Tab Hound add-in also has a feature that adds a command to the sheet tab’s right-click menu to make a duplicate copy of the sheet with one click.

Tab Hound Right Click Menu

Bonus tip: This Ctrl & Drag method also works to make duplicate copies of shapes or charts.  Select a shape/chart and then hold Ctrl while moving it.  Release the mouse button and a copy of the object will be placed on the sheet.  Release the Ctrl key after releasing the mouse button.

I recently shared this tip on the MyExcelOnline podcast.  Checkout episode #13 to hear 23 Excel tips & tricks from different Excel experts.

#2 – Ctrl+Enter to Fill Multiple Cells

This keyboard shortcut can save time when entering the same formula in multiple cells.

Ctrl Enter to Copy Excel Formula or Value to All Selected Cells

  1. Select the cells that the formula will be inserted in.
  2. Type or insert the formula or text in the active cell.
  3. Hold the Ctrl key and press Enter.

The formula or text will be copied to all the selected cells.

Mac shortcut: Ctrl+Enter or Cmd+Enter

As you probably know, there are a TON of ways to copy or fill formulas.  This technique works best when you already have the range selected that you want to insert or modify formulas in.  This tends to happen when we are modifying formulas or fixing them for errors.

Bonus tip: If you have a single cell selected, then Ctrl+Enter can be used to keep that cell selected after you edit the formula.  Typically the next cell will be selected after hitting Enter.  Press Ctrl+Enter to keep the same cell selected.

#3 – Ctrl+T to Create a Table

If you are using Excel Tables then you won’t need the Ctrl+Enter shortcut as often.  That’s because Excel Tables automatically fill the formulas down a column for you.

It’s just one of the many great benefits of using Excel Tables.  I’m a huge fan of them.

Format Data as an Excel Table - Ctrl T

The keyboard shortcut to format your data as a Table is Ctrl+T.  The shortcut is different in different language versions of Excel, so hover over the Table button on the Insert tab of the ribbon to see what the shortcut is for you.

Hover the Ribbon Button to See Excel Keyboard Shortcut in Screentip

Checkout my video on a Beginners Guide to Excel Tables to get started with this awesome feature of Excel.

Bonus tip: If you have tried Tables but don’t like the weird formulas (called structured reference formulas), you can turn those OFF.  Here is an article that explains how to turn off the Table formulas.

table-formula-references-can-be-turned-off-example

#4 – Apply & Clear Table Formatting

One nice features of Excel Tables is the styling or formatting that is applied when you insert the Table.  You can quickly make your data look very nice and organized.  Every other row of the Table is shaded (banded) to give it a clean look that is easier to read.

If your range already has some formatting in the header row, then sometimes your Table can look a little ugly after creating it.  The Table formatting does not get fully applied to the header row for some reason.

Before and After Apply and Clear Table Formatting

Fortunately, there is a quick fix:

Apply and Clear Table Style Formatting

  1. Select a cell inside the Table.
  2. Go to the Design tab in the Ribbon.
  3. Right-click one of the Table styles in the Styles Gallery.
  4. Choose Apply & Clear Formatting.

This will clear all the existing formatting in the range and apply the Table style.

Bonus tip: You can do this all in one step by going to the Home tab of the ribbon, clicking the Format as Table drop-down, and right-click>Apply & Clear Formatting on any style.  This will create the Table for your range and clear the existing formatting all at the same time.

#5 – AutoFit Column Width

After entering a formula, inserting a Table, or pasting data, your column widths might need to be adjusted to fit the new contents.

The keyboard shortcut to auto fit the column widths is: Alt,h,o,i

Press and release each key in order.

AutoFit Column Width Button Resizes Column Based on Selected Cell Contents

This will automatically expand the width of the column to fit the contents of the cells that are currently selected.  This is important to note.  If you want to resize the column to only fit a specific cell or group of cells, then select those cells first and press the keyboard shortcut.

Mac shortcut: Unfortunately the 2016 version for Mac does not have the Alt key shortcut combinations.  I don’t believe there is a shortcut key for this.  Please leave a comment below if you know it.

Checkout my article on 5 Keyboard Shortcuts for Rows & Columns for more details on this tip.

#6 – Select the Corners of a Range

Have you ever pasted some data over existing data, then wondered if the new data is long enough or wide enough to paste over the existing data?

If so, the Ctrl+. (period) keyboard shortcut will save you from scrolling all the way down the sheet.

Pressing Ctrl+. (hold the Ctrl key and press the period key) will select the next corner of the selected range.  After pasting a range of data, press Ctrl+. to select the top-right cell of the selected range.  Then press Ctrl+. again to select the bottom-right cell.

Ctrl Period to Select Corners of Selected Range

This will get you down to the bottom of the pasted range where you can quickly see if you pasted over the existing data.

You can also use this shortcut to jump down to the bottom of a single column.

Ctrl Period to Go to Last Selected Cell in a Column

Mac shortcut: Ctrl+. (same as Windows)

These tips are from my eBook, “Navigate Excel with the Keyboard”.  The eBook is included with my Tab Hound Add-in.

#7 – Select Visible Cells

When our data contains hidden rows or columns, or has filters applied, copy and paste can produce unexpected results.  Sometimes we copy a range expecting to only copy the visible cells.  Then when we paste, all of the hidden rows or columns are pasted too.  Argh!

Copy Paste Select Visible Cells in Excel

When this happens, we first need to select the visible cells.  The keyboard shortcut to select visible cells is Alt+; (semicolon).  Press this shortcut key after selecting the range, to only select the visible cells.

Select Visible Cells Only in Excel

Mac shortcut: Cmd+Shift+Z

Checkout my article and video on how to copy and paste the visible cells only.  My Paste Buddy Add-in also has a Paste Visible feature that allows you to paste to a filtered range.  Something you can’t normally do in Excel.

#8 – Go To a Range from a Formula

When editing formulas with range references on other sheets, it can sometimes be difficult to navigate to those sheets to find the range.  Especially when your workbook has a lot of sheets.

One quick tip to navigate to a range on another sheet is to:

6-Go To Reference Guide to navigate to range from formula

  1. Select the sheet and range reference in the formula with the screentip hyperlink.
  2. Press F5 or Ctrl+G on the keyboard to open the GoTo Window.  The sheet and range reference will be placed in the Reference box.
  3. Hit Enter or OK to go to that sheet and see the range selected.

Mac shortcut: F5 or Ctrl+G (same as Windows).

Note: You might have to press Fn+F5 if you are on a laptop with function keys.

This tip is from my Excel Pro Tips Ebook.

#9 – 3 Uses for Alt+Down Arrow

The Alt+Down Arrow keyboard shortcut opens drop-down menus.  This works in Excel and most other applications as well (including web browsers).

To perform the shortcut you simply hold down the Alt key and press the down arrow key on the keyboard.  Here’s what it can do in Excel

#1 – Open data validation lists (in-cell drop-down lists)

Select a cell that contains data validation and press Alt+Down Arrow to open the data validation list.

Alt Down Arrow to Open Data Validation List

#2 – Create a drop-down list of items in a column

If the cell does NOT contain data validation, then we can press Alt+Down Arrow to create a drop-down list of all the unique items in that column.  This is great for doing data entry because it allows you to select from a list of items in the column, and prevents typos.

Alt Down Arrow to View List of Unique Values from Cells in Column

#3 – Open the Filter Drop-down Menu

Select a cell in the header row of a filtered range and press Alt+Down Arrow to open the filter drop-down menu.

Alt+Down Arrow Excel Display Filter Drop Down Menu

Mac shortcut: Alt+Down Arrow works the same on the Mac for all 3 tips above.

Checkout this video on my favorite keyboard shortcuts for the filter drop-down menus.

#10 – Lock Drawing Mode to Create Multiple Shapes

Have you ever wanted to draw a bunch of the same shape (lines, boxes, circles) on a sheet, and repeatedly had to go to the Insert >Shapes menu?  If so, this little shortcut can save a bunch of time.

Lock Drawing Mode to Create Multiple Shapes in Excel

  1. Go to the Insert tab and press the Shapes menu.
  2. Right-click the shape you want to insert.
  3. Select “Lock Drawing Mode”.
  4. Draw the shape on the sheet
  5. Then draw another shape.  You can continue to draw as many of the same shape as you’d like

The drawing mode is locked and it will continue to let you draw multiple shapes.  Hit the Escape key on the keyboard when you are done.

Mac shortcut: I don’t believe there is any way to lock drawing mode on the Mac version.  Please leave a comment below if you know of one.

#11 – Lock the Format Painter

The Format Painter is one of those handy tools that allows us to quickly copy and paste the formatting of an object.  This can be a cell, shape, chart, pivot table, etc.

It’s a very simple tool to use.

  1. Select the object you want to copy the formatting from.
  2. On the Home tab of the ribbon, press the Format Painter button.
  3. Then select the object you want to paste the formatting to.

Now, what if you want to apply formatting to more than one object.  In step 2 above, double-click the Format Painter button.  This will lock the format painter and allow you to select multiple objects to apply formatting to.

Double Click Format Painterto Lock It

When finished, press the Escape key on the keyboard or press the Format Painter button again.  This tip is from my eBook, “Copy & Paste Pro Tips”.  It is included with my Paste Buddy Add-in.

Mac shortcut: This works the same on the Mac version

#12 – Close the Last Excel Workbook, But Leave Excel Open

In Excel 2013 for Windows the application changed to a Single Document Interface (SDI).  That means we no longer have Excel workbooks open inside of one application window.  Instead, we have one application window open per workbook.

When we close the last workbook we have open by pressing the “X” (close button) in the top-right corner of the application window, the entire Excel application closes.

Sometimes we don’t want this if we are working on an add-in, personal macro workbook, or just don’t want completely restart Excel.

To leave the application window open, press Ctrl+W on the keyboard to close the workbook only.  This will close the workbook without closing the application window.

Press Ctrl W to Close Last Excel Workbook and Leave Application Open

We can also add the Close Window button to the Quick Access Toolbar, to preform this operation with the mouse.

Bonus tip: Ctrl+W also works to close tabs in your web browser window.

Mac shortcut: Ctrl+W or Cmd+W works on the Mac version.  The behavior of the SDI is a little different.  The app window will close but the app will remain open in the task bar.

#13 – Create Keyboard Shortcuts for any Command with the Quick Access Toolbar

The Quick Access Toolbar (QAT) was introduced with the ribbon in Excel 2007, and allows us to create buttons for our most commonly used commands.  This saves us from having to navigate through the tabs in the ribbon to find a button.

Each button in the QAT has a keyboard shortcut assigned to it.

  1. With any cell or object selected, press and release the Alt key on the keyboard.
  2. You will see numbers appear above the buttons on the QAT.  These are the shortcuts to press the buttons.

Alt Number Key for Keyboard Shortcuts for the Quick Access Toolbar

So Alt+1 is the keyboard shortcut to press the first button in the QAT.  Put your favorite command in that position and you now have a keyboard shortcut for it.  This is great for commands that don’t have dedicated keyboard shortcuts.

Mac shortcut: Unfortunately, the Mac version does not have the Alt shortcut keys for the QAT.

#14 – Add Document Location/File Path to the QAT

Sometimes when we have an Excel file open we want to quickly see which folder or drive the file is saved on.  This is especially true if you are working with files on different servers or mapped drives.

We can add a box called the Document Location to the QAT to see the file path of the file that is currently open.

View Document Location in Quick Access Toolbar - Excel File Path

  1. Right-click the ribbon or QAT and select “Customize the Quick Access Toolbar…”
  2. In the drop-down menu in the top left of the Window select Commands Not in the Ribbon.
  3. Scroll down in the list box below to find Document Location.
  4. Double-click it to add it to the QAT and press OK.

You will now see the Document Location box in the QAT.  This will appear every time you open Excel.  It will also change every time you open or activate a different Excel file.

Bonus tip: You can also see the document’s folder path on the File menu.  Left-click the path to see options on copying the path or opening the folder in Windows Explorer.

View File Path and Copy to Clip Board Open Folder Options on Excel File Menu

#15 – Open the Visual Basic Editor

The Visual Basic Editor (VB Editor) is the application where we write macros and create userforms.  This application comes with Excel, and unlocks a whole new world of programming and automating Excel with VBA.

The keyboard shortcut to open the VB Editor is Alt+F11.  We can also open the VB Editor by pressing the Visual Basic button on the Developer Tab of the ribbon.  Once in the VB Editor, you can press Alt+F11 again to get back to Excel.

Open the Visual Basic Editor - Alt F11

If you are new to macros and VBA or just want to learn more about it, I have a free webinar coming up on The 7 Steps to Getting Started with Macros & VBA.  Also checkout this video on how to create your personal macro workbook and enable the Developer tab.

Mac shortcut: Unfortunately, the Mac does not have this shortcut key.  The current VB Editor for the Mac 2016 version is pretty limited on it’s capabilities.  Hopefully that will change in the future and you can vote on it here.

#16 – Open the VBA Immediate Window

The VBA Immediate window is an amazing tool that can help us with all kinds of tasks.  We use it frequently when writing and debugging macros.  But it can also be used to run one line of code or get some information about objects in the application.

To open the Immediate Window, press Alt+F11 to open the VB Editor, then press Ctrl+G to open the Immediate Window.

Open Visual Basic Editor and Immediate Window Excel

From here you can type a line of code and then press enter to run the code.  A good example is removing the page break lines that appear after running print preview.  You can type or copy/paste the following code into the Immediate Window, then hit Enter, to clear the page breaks lines.

ActiveSheet.DisplayPageBreaks = False

Here is an article with 5 Ways to Use the VBA Immediate Window.

#17 – Add a Search Box to Your Slicers

Unfortunately, we can’t actually add a search box to our slicers.  However, I created a bit of a workaround that gets the job done.

Add a Search Box to the Slicer to Filter it Quickly

This post and video has become very popular.  It might help you out next time you insert a slicer with a long list of items in it.  These slicers can be difficult to navigate and require the user to do a lot of scrolling.  Adding the search box eliminates the scrolling and makes your users happy.

Checkout the video and article on how to add a search box to your slicers.

Download the Guide

Here is a printable PDF version of the article.

17 Excel Tips Guide - Excel Campus.pdf (624.6 KB)

What Is Your Favorite Excel Tip?

Well, there are 17+ tips for the New Year.  Learning all those should speed up your work by 17%, plus or minus… 😉

There are exactly 15,765,241 additional tips that I did not get to cover in this article.  So, what is one of your favorite Excel tips that you have learned recently?  Please leave a comment below and share a tip.  It’s a great way to learn from everyone.

I wish you all the best in 2017, and am happy you are joining me for another awesome year of learning Excel.  Thanks again for all your support! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 87 comments
deepak Gauniyal - March 12, 2017

thanxs john your tips are amazing. here is a short key for clear formating just select entire rows and columns and press these keys…..Alt+EAA

Reply
Stephen - February 19, 2017

Use the N() function to add a comment inside a formula.

Rather than use the Excel Comments boxes, which can be a bit clumsy and/or intrusive, I do this:

eg =SUM(A1:A12)+N(“Sum of monthly sales for the year”)

The N() function can yield several values, but N(“any text string”) has a value of zero.

So, in this example, it adds 0 to the sum, ie doesn’t alter it… but it leaves the comment inside the formula 🙂

There are a couple of other uses I have discovered for N()…

HTH
Stephen

Reply
Sheetal Wadhwa - February 19, 2017

Awesome !!! Loved each and every advice 🙂

Reply
Dennis Balce - February 2, 2017

Hey Jon, thanks so much! These are going to be really time-saving for me. I will start using #1, 7, 8, 11 and 14 immediately (like tomorrow!) 🙂

Dennis

Reply
    Jon Acampora - February 4, 2017

    Hey Dennis,
    Great to hear from you my friend! I’m happy to hear you will be putting these to good use. We should catch up sometime soon. Thanks again! 🙂

    Reply
kajal Mittal - January 30, 2017

Hi Jon,

All the tips are awesome and helpful.
I really like tip no. 6 i.e. to select the corners of a range..
Thanks for sharing.

Regards,
Kajal Mittal

Reply
Meni Porat - January 24, 2017

Tip #3:
You can create a table with CTRL+L, in addition to the CTRL+T that you mentioned.

Thank you for excellent, very useful tips.

Reply
    Jon Acampora - January 24, 2017

    Hi Meni,

    Thanks for the tip! Some language versions use Ctrl+L instead of Ctrl+T. Both work in the English version. You can also hover over the Insert Table button to see the keyboard shortcut in the screen tip.

    Reply
      Meni Porat - January 24, 2017

      Hi Jon,
      I don’t think that the CTRL+L is related to language issues.
      The explanation is much simpler: the List object in Excel 2003 is considered the forerunner of the 2007/10/13/16 Table object.
      The CTRL+L was (in 2003) the shortcut for creating a list. When the new version of Excel was released (i.e. 2007), they decided to keep the CTRL+L shortcut for compatibility.

      Reply
        Jon Acampora - January 25, 2017

        Thanks Meni. Here is a comment from Stefan on another post who uses the Dutch version of Excel. “I don’t know if it is a regional thing, but the short-cut for creating a table is CTRL+L instead of CTRL+T in my version of Excel (Dutch).”

        I’m not sure that Ctrl+T carried over to all language versions.

        Reply
          Meni Porat - January 25, 2017

          Hi Jon,
          Perhaps I was wrong about the language issue, but the reason that in the English interface you can use either CTRL+T or CTRL+L is the one I specified.

          Reply
yashoda chhatri - January 21, 2017

Hi Jon,

Really I liked it a lot because mostly I have to work with excel. I loved the 2nd tips .

Thanks & regards
Yashoda Chhetri

Reply
Sen Wang - January 16, 2017

Hi Jon,

Great Tips and thanks for put them together.

I will love ALT features and especially you can set up your own ALT functions!

Reply
Jon Peltier - January 13, 2017

Ctrl+1 (numeral one) opens the Format dialog (or task pane) for the selected object.

Reply
George - January 13, 2017

Do any work for Mac?

Reply
Jack - January 13, 2017

As usual a great job Jon. #8 works great when I can remember to use it.

My Tip
I prefer /IC for inserting a column or /IR for inserting a row to the more current Shift+Control++ with column or row selected. /ic inserts a column to the left of the selected cell. /ir inserts a row above the selected cell. You can also select multiple cells to insert multiple rows or columns without selecting the whole rows or columns.
Select a cell or multiple cells and then
Type /, then
Type i or I and then
Type r or R for row, or c or C for column.

It’s not that selecting an entire column (Ctrl+SpaceBar) or row (Shift+SpaceBar) is particularly difficult it is just an extra step.

Reply
Emma Vidacovich - January 13, 2017

Jon,

I have learned so much by attending your webinars in the last few weeks. The extra tips you always send are a big help. Get up the good work you do to help others.

Thank you!

Reply
Shveta - January 13, 2017

Thankyou John for these wonderful tips. This help me to work faster.
I will looking forward for more tips from your end

Reply
Carine Hough - January 13, 2017

Hi Jon

Thanks for these tips, a lot of them I have learned from you previously 🙂 It has saved lots of time to date.

The search bar for slicers is a favourite that I started using after reading the post when it published a while ago. Great idea!

I didn’t know about locking format painter or the drawing tool. So simple yet so useful.

Reply
silvana - January 13, 2017

Hi John, I love all of them, just a bit worried how to memorize them. is there a possibilities, I could save them somewhere or print them all?
thanks a lot for sharing all this with us.

Reply
duduku sudhakar - January 12, 2017

double clicking format painter to apply the formatting multiple objects.

Reply
Shreepad SM Gandhi - January 12, 2017

Thank you very much Jon. I wasn’t aware about the Ctrl+. (Control+dot) as well as ‘Lock Drawing mode’ and Ctrl+T (To create a table) keyboard shortcuts. This is definitely going to speed up my work. Thanks again. And yes, wish you all the very best and congratulations too for your would-be new member in your family. God bless.

Reply
Sk - January 12, 2017

Thanks Jon! You always do great work! Congratulations to you n to your family. Have a fantastic year ahead with your new born.

Reply
Annie - January 12, 2017

Thanks for sharing Jon. They are all very good tips. I loved #1. Much easier and quicker doing this way! I will share this with my friends too.

Reply
Patricia Ovens - January 12, 2017

I love all of these tips. Actually started using some as I was reading through them (multi format paste & table shortcut and formatting.

John I especially liked the Heat map (conditional formatting) tips you posted in your earlier newsletter. That is awesome 🙂

oh and the music video from the seminar? lol I posted it to all my work colleagues, they loved it

Reply
Joe - January 12, 2017

Jon
I am a big fan of yours and have been a subscriber and learner, of yours, for years.
Your first tip has been in my arsenal for as long as I can remember.
I have embellished your tip by adding something to the sheet name. I have taught my team to label the initial input tab “PURE” and the copy, for which I will be working on “WORK”.

Please keep up the great work helping us get better
Thanks
Joe

Reply
Marco - January 12, 2017

I like the lock drawing tips, this is new to me. Will try & use it in future, thanks & happy new year.

Reply
alex - January 12, 2017

Jon thank you for another wonderfully educational free tips email, great work.
My favourite new tip is the document location add on to the qat, excellent.
You deserve much success for your diligence, clear and pleasant speech in your videos.

The only tip i profer is that i use excel to draw plans for building.
To make the plans quite accurate i set up the rows and columns to be the same size and depending on the overall dimensions adjust the new squares on the worksheet to be 1 metre or 500mm or whatever works.

kind regards
Alex

Reply
Anh - January 12, 2017

My fav probably the CTR+T. I’ve never created a table this way.

Thank you for sharing these with us.

Your tips on filter are also great and so helpful.

Many thanks
Anh

Reply
Janine - January 12, 2017

Great job, Jon, as usual. I use most of these regularly. #8 (go to a range from a formula) was new to me, though. One of my favorites not mentioned here is double-clicking the autofill handle (instead of clicking and dragging) to fill items to the bottom of a list. This eliminates dragging too far, then reversing and going too far again, etc.!

Reply
Neale Blackwood - January 12, 2017

Thanks for sharing Jon

I learned a couple of new tips.

Tip #1 – the Ctrl + drag also works for cells and selected ranges – hold the Ctrl key down whilst dragging with the arrow cursor shape (a small plus appears)- this copies instead of cut and paste.

You can also use the Ctrl key with the fill handle – changes copy to increment and increment to copy.

I use a few Alt key shortcuts (pressed in sequence, not held down)

Alt a c – removes all the filters in a list/table
Alt w f f – applies/removes freeze panes
Alt a v v – opens the data validation dialog
Alt n v Enter – creates a blank pivot table from the selected data

Regards
Neale

Reply
    Neale Blackwood - January 12, 2017

    Tip #8 is a bit flaky

    Example: select Sheet2!C1 in the formula bar as part of the formula, press F5 and Sheet2!A:A in entered in the Goto reference.

    Sometimes when you highlight a reference (eg as part of a logical test) it doesn’t work, but as a separate argument in the formula it seems to work ok.

    Seems to work well with named ranges.

    Reply
      Jon Acampora - January 13, 2017

      Hey Neale,
      Great observation! I believe the GoTo menu is interpreting the reference as a R1C1 reference before a range reference. So in your example, it’s evaluating C1 as column 1 on Sheet2 and returning the A:A reference for column 1. If you had a C2 reference it would return B:B for column 2.

      I believe this will only happen when the reference is in column C.

      If the reference is absolute with any $ signs ($C1, C$1, $C$1) then it will return the correct reference in the GoTo menu.

      All other reference in other columns should work fine. Good catch though! 🙂

      Reply
        Neale Blackwood - January 13, 2017

        Thanks for the reply. Well spotted.

        Yes, cell references in columns R and C are evaluated like row and column numbers – interesting – bug or feature?

        Reply
Don - January 12, 2017

Nice shortcuts but they don’t work on a Mac. At least all of them I tried don’t with Excel 2016.

Reply
    Jon Acampora - January 13, 2017

    Thanks Don,
    I updated the article to include the Mac equivalent shortcuts in each section. Almost all of them should work on the Mac, with the exception of the Alt shortcuts for the ribbon or QAT. The Mac versions do not have those features. Also, the shortcuts will be for the 2016 version for Mac. I hope that helps.

    Reply
      jim - May 25, 2017

      Man I wish Mac has the Alt shortcut ! But why dont they??

      #13 – Create Keyboard Shortcuts for any Command with the Quick Access Toolbar

      Is there any alt shorcuts?

      Reply
Chris Hassall - January 12, 2017

Hi Jon,
I’m located in Australia, so often it’s a bit difficult timezone wise to participate in your webinars. I do like scanning through your tips very helpful.

What I like to do is print a copy so that I can read through while on the train going home. Your articles come through to my work email address because it’s available for collection during our daylight hours. Thanks Frans for raising the pdf request. This will be very helpful.

Could I suggest just one thing though. When offering as a pdf, would you place the link at the beginning of the article please. The reason why? I started to scan the topics and thought great! I printed the article. Upon collecting from the printer, the pages happened to fall open on Frans’ request and then Jon’s answer. I then saw the link at the bottom after all the comments. It would have been easier, quicker and better for mother earth to have clicked on the link at the beginning and saving to USB for later viewing on tablet or phone. Just a thought!

Reply
    Jon Acampora - January 12, 2017

    Hey Chris,
    Another great suggestion! Sorry about that. I just added the download link to the top of the article as well. Thank you for your support. And thanks for thinking about our beautiful planet as well. 🙂

    Reply
Phil - January 12, 2017

Really neat stuff, Jon. I love #1 and #15. Both are really helpers for me!

Reply
Nate O - January 12, 2017

#8 is pretty slick. I’ll have to remember that one.

Reply
Kristine - January 12, 2017

Hi Jon,
Thank you for the tipps. I like #10 a lot. Looking forward to use it soon 🙂

One of my favorite shortcuts is Ctrl+y
It repeats your last action (like coloring, deleting, …).

Thanks

Reply
Tony - January 12, 2017

Jon,

Not an Excel questions, but can I ask what tool or application you used to create the gifs with the red progress bars? Thank you.

Reply
    Jon Acampora - January 13, 2017

    Hi Tony,
    I use Camtasia for all my screencasts and videos, including the GIFs. The red progress bars in the GIFs are a bit of a hack with a transition. I’ve been meaning to write a post on that. I’ll put that on the list and let you know when it’s available. To me, this makes GIFs so much easier to watch when you know what stage their in and how much time you have to wait until they restart…

    Reply
Paul - January 12, 2017

Number 4 is awesome. I have learned so much from your VBA course as well.

My most favorite tip is finding Excel Campus!!!!!!!!!!
Happy New Year to you also
Paul

Reply
Róbert - January 12, 2017

Hi Jon,

tip #11 – Lock the Format Painter is very useful for me, thanks

Reply
Tonya - January 12, 2017

I work with very large excel files and create a lot of pivots. Sometimes I make pivots from pivots. The issue with a pivots is doesn’t carry the names/numbers all the way down. I copy the pivot to a new sheet, highlight the columns with blanks and then ^G altS K = up arrow ^enter. It fills in all the blank fields. When you have hundreds to thousands of lines this is a big time saver. I can then create another pivot from the data.

Reply
    Keith - January 12, 2017

    Tonya,
    If I understand your issue correctly, I might have a much easier answer for you.

    You mention that “The issue with a pivots is doesn’t carry the names/numbers all the way down.”

    Try right clicking a value in the column that is not carrying down the names, then selecting “Field Settings” from the drop down list. It will bring up a popup box with two tabs, select the 2nd tab (labeled “Layout & Print”). On this tab select the check box “Repeat item labels”, then select OK. This will repeat each item downward into the blank cells until each respective value in that column.

    Let me know if this is a solution to your issue.

    Reply
      Jon Acampora - January 13, 2017

      Thanks Keith! That is a great suggestion! These options can also be set from the Report Layout drop-down in the Design Tab of the ribbon (select a cell inside the pivot table first). Here is a screenshot.

      I hope that helps.

      Reply
Musa - January 12, 2017

This post is loaded with lot of insight. You always raise bar.

Add a Search Box to Slicers catches my glimpse.

Thanks Mr Jon

Reply
Carrie - January 12, 2017

Happy New Year Jon! Thanks for the tips! My eyes lit up like a Christmas tree on #1 alone lol. I forgot about #7, and I very much like #9-2. Cheers! =)

Reply
Wen - January 12, 2017

Hi there,
My favorite excel shortcut is opening another instance of excel. So if you already have an instance of Excel open and it’s listed as a tab in the main bar (or side bar depending on how you have your settings), you hold down the SHIFT key and then just normal click on the program and it opens up another instance. This works for any program as well.

Reply
Hamed Parada - January 12, 2017

I loved tips # 1, and # 15 was awesome to learnt it. Jon Thank very much for your tips, I don’t think you now how much you help with your tips,

Reply
Jorge Sanguino - January 12, 2017

Hi Jon….

Excellent tips for Excel fans….It is incredible how this application is useful in our lives…Excel should be taught starting in primary school.! There is so much to learn about it, that the earlier you start the better.
I congratulate you for your dedication to spread the word about how to make excel easier.
Thanks,
Jorge

Reply
    Jon Acampora - January 13, 2017

    Thanks Jorge! I completely agree. I’m happy to see it’s being taught in more colleges, but there is still a lack in education.

    Reply
Jasper - January 12, 2017

One of my favorites is the Alt key, which allows you to access different tabs on the top. For example, if I want to paste value only, a simple Alt + H + V + V would do; Or if I want to clear all, I just need Alt + H + E + A to complete the task, which has saved me a lot of time.

Reply
    Jon Acampora - January 13, 2017

    Thanks Jasper! Great tip, and one of my favorites too. The Alt+ accelerator keys are awesome.

    This made me think of another tip, which is that you can hover over most buttons in the ribbon to see their dedicated keyboard shortcut. Here is a screenshot.

    Reply
Mohamed Chakroun - January 12, 2017

Thank you Jhon
One of my favourite excel Tips, is the différence in using subtotal with 9 or 109 etc…
When we use for example subtotal(9;range) it will calculate the sum of filtred rows including those we have hidden manuelly by selecting rows and hiding them, and when we use subtotal(109;range) it will calculate the filtred and even the hidden rows that whe have hide manuelly

Reply
Michel - January 12, 2017

Dear Jon,

Let me first say happy new year!!!

To be honest with you, your work specifically this artical and many other video and courses are very very important for us than you think. I am excel user on daily basis, before I could spent many days organising data for analysis but after receiving your courses, it becomes more and more easier and gained more time. Thank you a lot and know that you doing a fantastic job for many people from all the world. I wish good health so that you can continue to help the people.

Thank you very murch

Reply
    Jon Acampora - January 13, 2017

    Hi Michel,
    Thank you so much for the nice comment. I feel blessed to have such a wonderful group of members here that are as excited as I am about learning Excel. You definitely keep me inspired. Thanks again! 🙂

    Reply
Angel H. - January 12, 2017

Hi!
Will these work with Excel 2010?

Thanks!

Reply
    Jon Acampora - January 12, 2017

    Yes, great question Angel. All the shortcuts listed above will work in Excel 2007 or later for Windows. Thanks!

    Reply
Delville Stickland - January 12, 2017

Wow! Thank you so much.
Happy 2017 for you, your wife and soon to be your new family addition.
Love your work.
Cheers

Reply
Dev - January 12, 2017

I really liked tip no. 10 Lock drawing mode… Really rocking Jon…

Reply
Frans Visser (Beter in Excel) - January 12, 2017

Hi Jon,
Nice tip these 17 one’s 😉
My tip: make also a pdf of this so people can download it as well.
I would have liked it and maybe others as well?!
Thanks anyway!

Reply
Muhammet Mustafa ASLAN - January 12, 2017

Hi Jon,

I just could not skip your post, it’s really well prepared, thanks very much.

I guess I do use all of the tips frequently that you’ve mentioned except the first one.

Thanks for sharing again.

Regards,
Mustafa ASLAN

Reply
Peter Buyze - January 11, 2017

Great stuff Jon. I posted about it on G+ here https://plus.google.com/+PeterBuyze/posts/evm5k7xMJYa.

Reply
Jon Acampora - January 11, 2017

Please leave a comment with one of your favorite Excel tips. It can be something simple that saves you time. Thank you! 🙂

Reply
    Jude - January 14, 2017

    Thanks for sharing all your tips.

    Tip No 2 : For copying the formula right down to the last cell, I would place the arrow in the right lower corner of the cell with the first formula and double click. This would be faster than the method you have shown in tip no.2.

    I am sure you must be aware of this, but I do hope it helps someone.

    Jude

    Reply
      Jon Acampora - January 20, 2017

      Thanks Jude! Yes, as I mention in the article, this tip works best when you already have the cells selected. This is usually after a copy/paste and you need to modify all the formulas. You can also use Ctrl+D to copy down. So many ways to perform the same task. 🙂 Thanks again for your suggestion!

      Reply

Leave a Reply: