Vertex42 Excel Tips

A few years ago, I started publishing occasional Excel tips via Twitter. I've compiled some of these Excel tips below so that you can soak in a bunch all at once.

Most of these tips apply to nearly any version of Excel. However, because of the changes to the user interface in newer versions of Excel, explanations that say how to get to something via a menu (such as how to get to print and page setup options) may be different for different versions of Excel.

For 140+ detailed tips and tutorials that provide the equivalent of taking an intermediate level Excel training course, see the Spreadsheet Tips Workbook.

Data Entry / Editing

  1. [Essential] Learn to use the combination of CTRL, ALT, and SHIFT along with the Arrow Keys to navigate and select text.
  2. [Essential] Press F2 to edit the currently selected cell (will place the cursor at the end of the text/formula)
  3. [Essential] Insert a Line Break inside a cell by pressing Alt+Enter. On a Mac: Ctrl+Option+Return
  4. [Essential] Press ESC while you are editing the text or formula in a cell to exit the cell and cancel any changes that you may have made.
  5. [Essential] Learn and use other important keyboard shortcuts. See this article for a list of my favorites and to download a 1-page reference sheet.
  6. [Very Handy] Use Ctrl+Enter to quickly fill a range of cells with a value or formula after entering a value in one of the cells.
  7. [Very Handy] Use Ctrl+d to copy the cell/row immediately above the selected cell/row. Copies formatting also. Use Ctrl+r to copy the cell immediately to the left.
  8. [Very Handy] To quickly fill a range with 4,5,6,7... enter the 4 in the first cell and then hold down CTRL as you drag the fill handle. Watch How to Use AutoFill in Excel
  9. [Handy] To quickly fill a range with a pattern such as 10,20,30,40... enter the 10 and the 20 then select both and drag the fill handle.
  10. [Handy] Shortcut to Copy the Current Row and Insert the Copied Row above it: SHIFT+SPACE then CTRL+c then CTRL+SHIFT++.
  11. [Handy] Use the keyboard shortcut combination F2 then CTRL+SHIFT+HOME to quickly select an entire formula within a cell.
  12. [Handy] Press Ctrl+; to quickly enter today's date in a cell.
  13. [Handy] Press Ctrl+Colon (Ctrl+Shift+;) to quickly enter the current time in a cell.
  14. [Handy] Fast Fill/Copy: Double-click the fill handle (the little "square") of a selected cell to fill (copy) down the same number of rows as the adjacent column.
  15. [Handy] To split data in a cell into multiple columns, go to Data > Text to Columns. Handy for lists of names, comma-delimited data, etc.
  16. [Handy] Right-Click and Drag on the border of a selected cell or range to open a hidden menu that lets you paste values. (Learned that on ExcelCampus.com)
  17. [Handy] Press F2 prior to using arrow keys when editing a formula in a dialog box such as a conditional formatting rule (to avoid inserting references).
  18. [Tricky] A menu of fill options will show up after using the Right mouse button to drag a Fill Handle (instead of the Left mouse button)
  19. [Tricky] After pressing Enter in Excel, you will typically move down one cell. Go to File > Options > Advanced to move in a direction other than down. Or, select a horizontal range of cells first and see what happens when you press Enter.
  20. [Tricky] To enter a value as a fraction like 3/4 without having it autoformatted as a date, include a leading zero and a space (0 3/4)
  21. [Info] If you see "######" in a cell, it means the column is too narrow to display the value.
  22. [Info] Inserting a new row will copy the formatting from the previous row (as well as sparklines and maybe other stuff) unless you first copy a blank row from elsewhere in your spreadsheet and insert the copied row instead. Undo automatic formatting after inserting a row by selecting "Clear Formatting" from the paintbrush icon that appears next to the row.

Printing Tips

  1. [Essential] Choose a specific range of cells to print by setting the Print Area ... more info + video demo
  2. [Essential] Fit a worksheet to one-page wide by setting the Print Scaling and leaving the number of pages tall blank ... more info + video demo
  3. [Handy] Print a chart object (such as a timeline) across multiple pages by selecting the cells surrounding the chart rather than the chart object itself.
  4. [Handy] Switch to Page Break Preview via the View menu to change the position of the page breaks in your worksheet.
  5. [Handy] Have a data table spanning multiple pages? Print the header rows on all pages by going to Page Layout > Page Setup > Sheet tab > Rows to Repeat at Top
  6. [Handy] Print a square grid in Excel: Ctrl+a, Arial 10pt font, Column width = 1.71, Row Height = 12.75 ...download grid templates
  7. [Handy] Excel can do headers and footers just like Word, including auto page numbering and dates. Go to Page Layout > Page Setup to set them up.
  8. [Handy] If a portion of worksheet is not printing, you may need to redefine the print area via Page Layout > Print Area.
  9. [Tricky] Update the print settings on multiple worksheets at the same time by selecting multiple tabs (hold down Ctrl as you click on the tabs) and go to Page Layout > Page Setup.
  10. [Advanced] A Print Area is actually a Named Range, so you can create a Dynamic Print Area using the OFFSET function (see the article "Dynamic Named Ranges in Excel").

Options and Customization

  1. [Essential] Get rid of the grid! In newer versions of Excel, go to the View tab and uncheck Gridlines. In Excel 2003, go to Tools > Options > View tab and uncheck Gridlines.
  2. [Handy] To change the default number of sheets in a new workbook, go to File > Options > General and change the "Include this many sheets" setting
  3. [Cool] To change the background color of a worksheet tab, right-click on the tab and select Tab Color.
  4. [Tricky] Tired of seeing the green triangles in Excel? You can turn off specific rules/warnings via File > Options > Formulas (or Error Checking in older versions).
  5. [Tricky] Before keying data into a table, highlight the range of cells to edit. Then use the enter key to quickly move through just the highlighted region.

Navigation and Selection

  1. [Very Handy!] Use Ctrl+F to search a worksheet for a value. If you have a range selected, it will search only that range. Handy for replacing values as well.
  2. [Very Handy!] Use Ctrl+Arrow to jump between edges of occupied ranges. Add the Shift button to highlight cells as you go.
  3. [Very Handy!] Use Ctrl+G > Special to do things like Select all cells with comments, Select all cells containing formulas, etc.
  4. [Very Handy] Use SHIFT+Space to select the current Row and CTRL+Space to select the current Column. Remember which is which by noting that both "Ctrl" and "Column" start with the letter "C".
  5. [Handy] Navigate to and Select a named range by pressing Ctrl+G to list the named ranges. Click on the name you want to select and press OK. Useful for selecting the current Print Area.
  6. [Handy] Move between tabs (worksheets) in Excel using Ctrl+PgUp and Ctrl+PgDn. In Google Sheets, use Ctrl+Shift+PgUp and Ctrl+Shift+PgDn.
  7. [Handy] Move to a specific worksheet tab by right-clicking on the arrows to the left of the tabs and selecting from the list.
  8. [Handy] Ctrl+Home takes you to cell A1. On a MacBook it's Fn+Cmd+LeftArrow
  9. [Advanced] Create a bookmark by naming a cell. Select a cell to mark as a bookmark location and enter a name such as "bm_1" in the Name Box (or by going to Formulas > Define Name). Create a hyperlink in a cell or select a shape object to turn it into a button by pressing Ctrl+k. Select "Place in This Document" and then find and select the bookmark under Defined Names.
  10. [Info] In a protected worksheet, press Tab to navigate between input cells.

General

  1. [Essential] Right-click on everything! Contextual menus pop up with options that are unique to the thing you clicked on. When there is no right mouse button ... Phone: Tap-and-hold (long press); Trackpad: Two-finger click; Mac: Ctrl+click.
  2. [Essential] Use Split screen and Freeze Panes (via the View menu) so that you can keep a portion of your worksheet visible as you scroll and edit another part of your worksheet.
  3. [Very Handy] For a quick sum or count of selected cells, look at the bottom of the Excel window in the status bar (see an example).
  4. [Handy] Make a copy of a worksheet by holding Ctrl as you click and drag the worksheet tab you want to copy.
  5. Turn on/off automatic recalculation via Formulas > Calculation Options. (In Excel 2003: Tools > Options > Calculation). To manually recalculate press F9, Shift+F9, or Ctrl+Alt+F9.
  6. Press Ctrl+Alt+F9 to manually force a full recalculation of your workbook. This may be needed when using functions like XIRR().
  7. Pressing F9 will cause volatile functions like RAND() to recalculate. This can create interesting chart effects (see an example)
  8. A great way to learn new techniques in Excel is by dissecting templates. Remember to check for named ranges and conditional formatting rules.
  9. [Advanced] Use the built-in Macro Recorder to record simple sequences of actions and then look at the resulting VBA code (this is a great way to learn VBA).
  10. [Advanced] If you work with large tables of data and you don't know what Pivot tables are, find out! They are powerful.
  11. [Info] Dates in Excel are stored as serial numbers starting from 1 = 1/1/1900. The value for Jan 1, 2009 is 39814.
  12. [Info] Times in Excel are stored as decimal values representing a fraction of a day. For example 0.5 is 12:00 PM (noon) and 0.25 is 6:00 AM.

Formulas

  1. [Essential] Use F4 to toggle a reference between A1, $A$1, A$1 and $A1 while editing a formula (When NOT editing a formula, F4 is an alternative to Ctrl+Y which is "repeat" or "redo")
  2. [Important] Excel performs Negation before Exponentiation, so watch out for errors in formulas like =–x^2 or =5/(–x^2). Use =-(x^2) if you want to perform the exponentiation first.
  3. [Handy] Do you use Names for cells and regions? Press F3 when entering formulas to pull up a list of defined names and drop one in the formula.
  4. [Handy]You can quickly name cells and ranges by typing a name in the reference box directly to the left of the formula bar. You can't name something n1, n2, or n3 because those are cell references. Use n_1, n_2, n_3 instead.
  5. [Handy] Join text from multiple cells using the "&" operator: =A1&" "&B1. This works well for combining first and last names.
  6. [Handy] Tired of toggling between sheets and workbooks to see how inputs affect outputs? Use excel's Watch Window to keep an eye on specific cells.
  7. [Handy] Having a hard time with a complex calculation or finding a bug? Try using the Audit Formula feature to quickly highlight interdependencies.
  8. [Handy] Use COUNTIF() to count the number of data points between two values ...see article
  9. [Cool] Use Named Ranges if you want your formulas to use natural language or variable names rather than cell references (e.g. =m*x+b)
  10. [Tricky] Use OFFSET(ref,-1,0) to refer to the cell immediately above. Useful for a running balance that lets you more easily insert and delete rows.
  11. [Tricky] Enter a Line Break within a cell using a formula: ="abc"&CHAR(10)&"def" (then set the Wrap Text property)
  12. [Tricky] Start entering a formula and after typing a function name like =INDEX press CTRL+SHIFT+a and see what happens (Excel will add argument placeholders to your function).
  13. [Advanced] There is no SUMPRODUCTIF function, but you can conditionally sum products using =SUMPRODUCT(--(cond_range="x"),range1,range2)).
  14. [Advanced] Need to round to a specific number of significant figures? Try this formula ...see article
  15. To copy a formula without changing references, you can first convert it to text by adding an apostrophe before the equal sign: '=
  16. [Advanced] Use a Dynamic Named Range to reference a list that expands or contracts based on the amount of data in it ...see how
  17. [Advanced] Arrays: When using an Array Formula, you press Ctrl+Shift+Enter instead of just Enter after entering or editing the formula. Identify an array formula by checking the formula bar - you'll see curly braces around the formula like this: {=theformula}
  18. [Advanced] Arrays: Array constants (arrays that are "hard-coded" into formulas) are enclosed in braces and use commas to separate columns and semi-colons to separate rows like this 2(row)x3(column) array: {1,1,1;2,2,2}
  19. [Advanced] Arrays: Check out sample array formulas for doing things like multiple linear regression and creating sequential number arrays ...see article
  20. [Example] Calculate the Day of the Year (1-366) in #Excel for a given date using =theDate-DATE(YEAR(theDate),1,0). Return the Date value for a given Year and Day of the Year (1-366) using =DATE(theYear,1,dayOfYear)

Formatting

  1. [Essential] Press Ctrl+1 (that's a "one" not an "L") to open the Format Cells dialog window for easy access to all the cell formatting options.
  2. [Very Handy] When Excel automatically formats a number as a date, you can change it back to a number using the shortcut CTRL+SHIFT+~. The tilde (~) is usually in the upper left of your keyboard.
  3. [Handy] Press Ctrl+E to use Flash Fill for quickly reformatting lists. Watch How to Use Flash Fill on our YouTube channel.
  4. [Handy] Make multiple columns the same width by selecting each of the columns and then changing the width of just one of them. This applies to rows as well.
  5. [Handy] Double-click on the column or row sizing handle (the line between the column letters or row numbers) to auto-size the column or row to the longest entry (some exceptions with wrapped cells).
  6. [Handy] Undo automatic formatting after inserting a row by selecting "Clear Formatting" from the paintbrush icon that appears next to the row
  7. [Info] If you see weird or magical stuff going on with formatting, it may be due to conditional formatting. Or, it could be that Excel is applying formatting automatically (which it does sometimes because it thinks it's so smart).
  8. [Handy] Trying to get a title centered across a table? Use the Center Across Selection format rather than one large merged cell.
  9. [Handy] Apply text formats to part of a cell by highlighting that portion in the formula bar before applying the formatting. (This is how you can do stuff like displaying H2O, where the 2 is a subscript)
  10. [Handy] Use the "Shrink to Fit" cell format option to avoid showing ##### in a cell containing a date.
  11. [Cool] Conditional Formatting: Highlight odd numbered rows with this formula: =MOD(ROW(),2)=1
  12. [Advanced] Create a Custom Number Format to display values using special formats
  13. Custom Number Formats - The custom date format [h]:mm can be used to display times that are greater than 24 hours, like 42:36.
  14. Custom Number Formats - Display numbers as fractions using the format code # ??/100 to display 5.2 as 5 20/100 and ?/2 to display 5.2 as 10/2 (note the automatic rounding)
  15. Custom Number Formats - Display feet and inches as 8 3/12 using the format code # ??/12 (rounded to the nearest inch)
  16. Custom Number Formats - Display temperature with the degrees symbol using the format code: #.##"°"
  17. Custom Number Formats - Display "kg" units (or other labels) within a cell without causing the value to convert to text using a format code like #.## "kg"
  18. Custom Number Formats - Display a number with leading zeros using a format code like 00000 to display 345 as 00345
  19. Custom Number Formats - Add a carriage return within a custom number format by pressing Ctrl+j
  20. Custom Number Formats - Display 23576 as 23.6K using the format code 0.0,"K"
  21. Custom Number Formats - Display 23,576,000 as 23.6M using the format code 0.0,,"M"

Special Features

  1. [Cool] Create a Drop-Down List within a Cell using Data Validation. The list can be on a different worksheet ...see how
  2. [Handy] Named Ranges as Bookmarks: You can create a bookmark within a large spreadsheet by naming a cell. Then use CTRL+G to quickly navigate to that cell. You can create a hyperlink to navigate to a bookmark (press CTRL+K and click on Bookmark).
  3. [Handy] Autoshapes as Navigation Buttons: You can use an autoshape to create a button and then apply a hyperlink to that button to link to a bookmark, another worksheet, or even an external web page.
  4. [Handy] Named Constants: You can create a Name for a constant or formula without actually referencing a range. Go to Insert > Name > Define (Excel 2003) or Formulas > Name Manager (Excel 2010).
  5. [Handy] What-If Scenarios: If you have a mathematical model, consider using Scenarios to store and analyze different what-if scenarios. (go to Tools > Scenarios in Excel 2003 or Data > What-If Analysis > Scenario Manager in Excel 2010)
  6. [Handy] Grouping and Outlining: You can use the Data > Group and Outline feature in Excel to expand and contract groups of rows and columns ...see an example.
  7. [Handy] Insert a ✔ by pressing ⊞+. to open the Windows 10 Emoji Panel or by going to Insert > Symbol and changing the font to "Segoe UI Symbol". Watch How to Insert a Check Mark in Excel on youtube.
  8. [Tricky] Add an AutoCorrect Exception to prevent "MPa" from changing to "Mpa" (Tools > AutoCorrect Options in Excel 2003 or File > Options > Proofing in Excel 2010)
  9. [Tricky] Insert special symbols like °, ², ∂ and µ quickly by adding custom Autocorrect entries. Example: replace (^2) with ² ...see article ... Unicode Characters
  10. [Handy] Goal Seek: Set an output (calculated) cell to a specific value by changing an input cell automatically using Excel's Goal Seek feature. ...see an example
  11. [Tricky] Remove Duplicates from a list by selecting the cells then go to Data > Filter > Advanced Filter, and check Unique Records Only.
  12. [Advanced] Show the Developer tab in Excel 2016: Go to File > Options > Customize Ribbon and select the "Developer" tab option
  13. [Advanced] Solver Add-In: In Excel 2010+, enable the Solver Add-In by going to Developer > Excel Add-Ins. Solver will show up in the Data ribbon. In Excel 2003 go to Tools > Add-Ins, the Solver will show up under Tools. ...See Excel Solver examples
  14. [Advanced] Customizable Drop-Down Lists: Use data validation lists that reference named ranges to create lists that you can customize easily ...see article
  15. [Tricky] Named Ranges: If you set the Zoom to 39% or less, Named Ranges consisting of 2 or more adjacent cells will be shown outlined.
  16. [Tricky] Pictures in Comments: You can display a picture as the background in a comment (Format Comment > Colors and Lines > Fill - Color > Fill Effects > Picture)

Charts and Graphics

  1. [Handy] - Your ability to compare areas isn't as good as your ability to compare lengths, so use bar graphs instead of pie graphs if possible.
  2. [Handy] - To align objects to the corners of the grid in Excel, hold down the ALT key as you draw, move, or resize objects.
  3. [Handy] - If you have a hard time selecting a series or object in an Excel chart, try using the drop-down box in the Chart toolbar.
  4. [Handy] - Link text in chart titles, axis labels, data labels, and text boxes to a cell by entering =A1 in the formula bar after selecting the object.
  5. [Cool] - Create a timeline using an X-Y chart, data labels, and vertical error bars.
  6. [Cool] - Create a Dot Plot or Dot Chart in Excel using the REPT() formula ...see how
  7. Jon Peltier: Jon's awesome collection of Excel Charts and Tutorials
  8. Charley Kyd: How to Create Bullet Graphs in Excel to Replace Gauges
  9. John Walkenbach: How to handle missing data in a line chart using =NA()
  10. John Walkenbach: Chart Templates

International Stuff

  1. [Essential] Change the Currency symbol from $ to £ or something else by opening the Format Cells window (Ctrl+1) and going to the Number tab. ...see video demo
  2. [Handy] In Excel 2010 you can use WORKDAY.INTL and NETWORKDAYS.INTL for date calculations that exclude specific days of the week (instead of Saturday and Sunday).
  3. [Important] Whether you should enter a day as m/d/yy or d/m/yy depends on your computer's system settings.
  4. [Handy] Use the built-in "*3/14/2001" date format or the "*1:30:55 PM" time format to display dates based on your computer's system setting. The "*" is what identifies the number formats that use your system's settings.

Spreadsheet Compatibility Tips

  1. [Info] Avoid Vertical Oriented Text ... Vertical text is not compatible with Google Docs or Excel Web App
  2. [Info] OpenOffice Compatibility: When using the INDEX function in Excel, use the correct syntax INDEX(array,row,column). Don't use the shortcut INDEX(array,column).
  3. [Info] OpenOffice Compatibility: In Excel arrays, make sure that you are using ";" for row separators and "," for column separators
  4. [Info] OpenOffice Compatibility: In Excel use cell references rather than named ranges for the linked cells in form field controls

Hidden Features

  1. The EVALUATE function: Trick for evaluating text as a formula ...see article
  2. Use the DATEDIF function to find the number of years, months, or days between two dates. See "Calculate Age in Excel" for examples.

 

More Excel Tips and Articles by Vertex42

The articles and tips below are more lengthy than the short tips listed above.

More Excel Tips

Link to Vertex42's Excel Tips

Like our Excel tips? Help us help others by recommending our Excel tips via your blog, Facebook, twitter, or email. The URL is simple:

https://www.vertex42.com/ExcelTips/


Disclaimer: This article is meant for educational purposes only. Use these tips at your own risk. :-)

Follow Us On ...

FB  PIN  TWEET  LI  IG  YT 
Excel and Financial Modeling Conference