Vertex42 Excel Tips

A while back, I began publishing Excel Tips via twitter. If you'd like to get these occasional Excel Tip tweets, Follow Vertex42 on 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 Excel 2007/2010/2013, explanations that say how to get to something via the 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 my new 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. ...more info
  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.
  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, CTRL+c, CTRL+SHIFT+"+".
  11. [Handy] Use the keyboard shortcut combination F2,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+: (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. [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)
  17. [Tricky] After pressing Enter in Excel, you will typically move down one cell. Check out the Edit options to move in a direction other than down.
  18. [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)
  19. [Info] If you see "######" in a cell, it means the column is too narrow to display the value.
  20. [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] Switch to Page Break Preview via the View menu to change the position of the page breaks in your worksheet.
  4. [Handy] Have a data table spanning multiple pages? Print the header rows on all pages by going to Page Setup > Sheet tab > Rows to Repeat at Top
  5. [Handy] Print a square grid in Excel: Ctrl+a, Arial 10pt font, Column width = 1.71, Row Height = 12.75 ... full article + downloads
  6. [Handy] Excel can do headers and footers just like Word, including auto page numbering and dates. Check out the page format options to set them up.
  7. [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 File > Page Setup.
  8. [Advanced] A Print Area is actually a Named Range, so you can create a Dynamic Print Area using the OFFSET function ... see full article

Options and Customization

  1. [Essential] Get rid of the grid - To remove the gridlines in an Excel 2003 worksheet, go to Tools > Options > View tab and uncheck Gridlines.
  2. [Handy] To change the default number of sheets in a new workbook, go to Tools > Options > General tab and change "Sheets in new workbook"
  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 the Tools > Options > Error Checking tab.
  5. [Tricky] Before keying data into a table, highlight the area. 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.
  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.
  7. [Handy] Ctrl+Home takes you to cell A1. On a MacBook it's Fn+Cmd+LeftArrow
  8. [Info] In a protected worksheet, press Tab to navigate between input cells.


  1. [Essential] Use Split screen and Freeze Panes (via the View menu) so that you can still see column and row labels when you are editing data tables.
  2. Pressing F9 will cause functions like RAND() to recalculate. This can create interesting chart effects ...see an example
  3. Turn off automatic recalculation via Tools > Options > Calculation tab. To manually recalculate press F9, Shift+F9, or Ctrl+Alt+F9.
  4. Press Ctrl+Alt+F9 to manually force a full recalculation of your workbook. This may be needed when using functions like XIRR().
  5. A great way to learn new techniques in Excel is by dissecting templates. Remember to check for named ranges and conditional formatting.
  6. 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)
  7. [Advanced] If you work with large tables of data and you don't know what Pivot tables are, find out! They are powerful.
  8. [Info] Dates in Excel are stored as serial numbers starting from 1 = 1/1/1900. The value for Jan 1, 2009 is 39814.
  9. [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.


  1. [Essential] Use F4 to toggle a reference between A1, $A$1, A$1 and $A1 while editing a formula ...more info (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....see article
  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 the OFFSET function to create a running balance that lets you more easily insert and delete rows ...see article
  11. [Tricky] Enter a Line Break within a cell using a formula: ="abc"&CHAR(10)&"def" (then set the Wrap Text property) ...see article
  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)


  1. [Essential] Press Ctrl+1 (that's a "one" not an "el") 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+"~"
  3. [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.
  4. [Handy] Double-click on the column or row sizing handle to auto-size the column or row to the longest entry (some exceptions with wrapped cells).
  5. [Handy] Undo automatic formatting after inserting a row by selecting "Clear Formatting" from the paintbrush icon that appears next to the row
  6. [Info] If you see weird or magical stuff going on with formatting in #Excel, 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).
  7. [Handy] Trying to get a title centered across a table? Use the Center Across Selection format rather than one large merged cell.
  8. [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)
  9. [Handy] Use the "Shrink to Fit" cell format option to avoid showing ##### in a cell containing a date.
  10. [Cool] Conditional Formatting: Highlight odd numbered rows with this formula: =MOD(ROW(),2)=1 ...see example
  11. [Advanced] Create a Custom Number Format to display values using special formats
  12. Custom Number Formats - The custom date format [h]:mm can be used to display times that are greater than 24 hours, like 42:36.
  13. 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)
  14. Custom Number Formats - Display feet and inches as 8 3/12 using the format code # ??/12 (rounded to the nearest inch)
  15. Custom Number Formats - Display temperature with the degrees symbol using the format code: #.##"°"
  16. 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"
  17. Custom Number Formats - Display a number with leading zeros using a format code like 00000 to display 345 as 00345
  18. Custom Number Formats - Add a carriage return within a custom number format by pressing Ctrl+j
  19. Custom Number Formats - Display 23576 as 23.6K using the format code 0.0,"K"
  20. 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. [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) ...see how
  8. [Tricky] Insert special symbols like °, ², ∂ and µ quickly by adding custom Autocorrect entries. Example: replace (^2) with ² ...see article for list
  9. [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
  10. [Tricky] Remove Duplicates from a list by selecting the cells then go to Data > Filter > Advanced Filter, and check Unique Records Only.
  11. [Advanced] Show the Developer tab in Excel 2010: Go to File > Customize Ribbon and select the "Developer" tab option
  12. [Advanced] Solver Add-In: In Excel 2010, enable the Solver Add-In by going to Developer > Add-Ins, the 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
  13. [Advanced] Customizable Drop-Down Lists: Use data validation lists that reference named ranges to create lists that you can customize easily ...see article
  14. [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.
  15. [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: Creating a transparent chart series (bar or column chart)
  11. John Walkenbach: Create a Thermomemter Style Chart in Excel

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 this article by Chip Pearson


More Excel Tips and Articles by Vertex42

The articles and tips below are more lengthy than the little quips that I publish via twitter.

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:

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


Follow Us On ...

Master Excel - Spreadsheet Tips Workbook

Create Dashboard Reports in Excel!