Excel Tips & Tricks

Calculate the Days, Months or Years between two Dates in Excel

Use the DatedIf function to calculate the interval between dates in Excel. Here's how:

Enter the function into a cell

=DATEDIF ( start_date , end_date, unit )

For example, to calculate the number of months between two dates, if the start date is in cell D2 and the end date is in cell E2 you could enter this formula into cell F2:

Video demo :

Using IF Functions in Excel 2007 and 2010

Download theIf Function Worksheet( and learn about the more complex uses of If functions.

Quick Navigation in Excel

To quickly move to the left, right, top, or bottom cell in a range of data, position the cursor at the beginning of the range and press:

Ctrl + a cursor arrow

To select a range of data, position the cursor at the beginning of the range and press:

Shift + Ctrl + a cursor arrow

Using VLOOKUP in Excel

VLOOKUP is a powerful Excel function which allows you to look up a value in an Excel list or table. For example, you could use this function to look up grades based on a percentage value:

To learn more about this function, check out the free online training from MicrosoftVLOOKUP: What it is, and when to use it (

Using Excel 2010 Sparklines (New Feature)

A new feature in Excel 2010 called Sparklines can help you spot trends in your data. Sparklines are tiny charts that sit on top of a cell. Here’s how to create them:

  1. Select an empty cell or group of empty cells where you want to insert Sparklines

  1. Click the Insert tab on the Ribbon

  1. In the Sparklines group, click the type of Sparkline you want to create

  1. In the Data box, enter the range of cells that contain the data to base the Sparklines on


Insert a Worksheet Based on a Template in Excel 2007

  1. Right click a worksheet tab

  1. Select Insert

  1. Choose the template

  1. Click OK

You can choose a custom template which you have created, one of the templates available when you click the Spreadsheet Solutions tab, or click the Templates on Office Online button and choose from hundreds of templates available from Microsoft.

COUNTIFS in Excel 2007

We've had the COUNTIF function in Excel for a while. This function allows you to count records in a list that match a single criteria. What's completely new with Excel 2007 is COUNTIFS which allows you to count records based onmultiplecriteria. Sure, there were other ways of doing this before, but none as easy as the COUNTIFS function. Here's the syntax:

=COUNTIFS(range1,criterion1,range2,criterion2,…,range_n,criterion_n)

Makes it really simple to count records in a range that match the criteria you enter in the formula. If you need a little more help, check out this article on the Microsoft web site:Use the COUNTIFS function in Excel 2007 to analyze data (

SUMIFS in Excel 2007

This is just an extension of the CountIfs function above. Here's the syntax:

=SUMIFS(SumRange,range1,criterion1,range2,criterion2,…,range_n,criterion_n)

AutoFill with Week Days

Hopefully you are already familiar with the Excel AutoFill feature for filling ranges with the months or days of the week. But what if you need to fill an Excel range with just weekdays? Here’s how:

  1. Enter the starting day into a cell

  1. Place the pointer over the lower right corner of the cell until you see the copy/fill handle (a thin black plus)

  1. Right click the handle and drag to select the range you want to fill with weekdays

  1. When you let up on the mouse button a menu will appear

  1. Select Fill Weekdays

This tip works in Excel 2007/2010 as well as earlier versions.

Long Text Entries in Excel 2007

To break a long text entry into separate lines, position the insertion point in the cell entry or on the Formula bar where you want the new line to start and press Alt + Enter. Excel expands the row containing the cell when it starts a new line. Excel automatically wraps the text in the cell when you press Enter to complete the entry, according to the cell's column width and the position of the line break.

Here is another method for dealing with long text entries in Excel 2007:

  1. Select the cell containing the long text entry

  1. Click the Home tab on the Ribbon

  1. In the Alignment group, click the Wrap Text button

Or you can right click the text entry and:

  1. Select Format Cells from the shortcut menu

  1. Click the Alignment tab

  1. Click to select the Wrap text option

Cleaning your Data in Excel

If you have copied or imported data into your Excel workbook, you may need to clean it up. An article from Microsoft entitledTop ten ways to clean your data ( covers features that will help you accomplish this.

The basics of cleaning your data include:

  • Removing duplicate rows

  • Finding and replacing text

  • Changing the case of text

  • Removing spaces and nonprinting characters from text

  • Fixing numbers and number signs

  • Fixing dates and times

  • Merging and splitting columns

  • Transforming and rearranging columns and rows

  • Reconciling table data by joining or matching

Adding Paste Values to the Quick Access Toolbar in Excel 2007

Using the Paste Values option in Excel lets you strip formulas from your data and paste only the resulting values. In Excel 2007 the Quick Access Toolbar is a great way to simplify using this feature. Here's how:

  1. Click the Office button in the upper left corner of the Excel 2007 Screen

  1. Click the Excel Options button at the bottom of the Office window to display the Excel Options dialog box

  1. Click Customize in the left pane

  1. Under Choose commands select All Commands

  1. Select Paste Values

  1. Click Add and then OK

Using the Excel 2007 Camera Tool

The Excel 2007 Camera tool lets you take a picture of a range ofcellson a worksheet. Before you can use this tool you must first add it to theQuickAccessToolbaron the Ribbon.

To add the Camera tool to the Quick Access Toolbar:

  1. Click the Officebuttonin the upper-left corner oftheRibbon

  1. Click the Excel Options button

  1. Click Customize

  1. In the Choose Commands From drop-down list, select Commands Not in the Ribbon

  1. Select Camera and double-click to add it to the Quick Access Toolbar

  1. Click OK to close the Excel Optionsdialog box

To use this tool:

  1. Select a range on your worksheet

  1. Click the Camera tool on the Ribbon

  1. Click where you want the picture to appear (In this workbook or even in another workbook)

A graphic is created of the range you selected. If you change the original data the picture also changes. You can even copy or move this picture to the clipboard and paste it into Word or PowerPoint if you need to. However, if you copy it into another program it will no longer update when the original is changed.

Tracking Revisions in Excel 2007

Excel 2007 tracks worksheet revisions so you can see what has changed on each sheet. To use this feature, do the following:

  1. Click the sheet to make it active
  2. Click the Review tab on the Ribbon
  3. Click Track Changes in the Changes group
  4. Choose Highlight Changes
  5. Click in the "Track changes while editing" box and then set the types of changes you want to highlight.

You can choose to have the changes highlighted on screen or listed on a new sheet.

Data Validation in Excel 2007

DataValidation lets you restrict what goes into a cell. For example, if dates being entered must be between a certain range, do this:

  1. Select the range of cells that you want to apply data validation to

  1. Click the Data tab on the Ribbon

  1. In the Data Tools group click the Data Validation button

  1. Under Validation criteria choose Date from the Allow drop-down menu

  1. Enter the acceptable date range

  1. Click OK

If someone tries to enter a date outside this range a warning is displayed that says, "The value you entered is not valid.”

Import a Custom Auto Fill List in Excel 2007

  1. Select the range of cells on the worksheet which contains the list

  1. Click the Microsoft Office button

  1. Click the Excel Options button to display the Excel Options dialog box

  1. Click the Popular tab in the left pane

  1. Click the Edit Custom List button in the right pane to open the Custom List dialog box

  1. The range of selected cells will be displayed in the Import list from cells box at the bottom

  1. Click the Import button

  1. Your list now appears in the Custom Lists window

  1. Click OK

Change the Case of Text in Excel

To change the case of text in Excel, use one of the following formulas:

=UPPER(A1) Changes the text in cell A1 to upper case
=LOWER(A1) Changes the text in cell A1 to lower case
=PROPER(A1) Changes the text in cell A1 to proper case

For example:


Display Formulas in Excel 2007 Worksheet

When creating Microsoft Excel worksheets formula results are shown in the worksheet NOT the formula. There may be times when it would be helpful if you could SEE the formula in the worksheet.
To display formulas in Excel 2007:
  1. Click the Office button
  2. Click the Excel Options button
  3. In the Excel Options dialog box, click Advanced
  4. Scroll down to the Display options for this worksheet section
  5. Click to select the option Show formulas in cells instead of their calculated values
To resume seeing the results of the formula, just repeat the above steps. Or you can use the shortcut keys for toggling formula viewing on and off: Ctrl+` (accent key below the tilde ~). This shortcut works in earlier versions of Excel as well.

Worksheet Selection in Excel

If you have several worksheets in an Excel workbook, it can be time consuming to select the one you want to view. This shortcut can help you speed up the process.

Right click on thesheet tab navigation arrows
A menu of worksheet tabs pops up
Left click the worksheet you want to view

This tip works in Excel 2007 as well as earlier versions.

Shade Alternate Rows in Excel 2007

One way to quickly add shading to alternate rows in Excel 2007 is by applying a predefined table style. By default, shading is applied to alternate rows in an Excel 2007 table to make the data easier to read. The alternate row shading will remain accurate even if you add or delete rows.

  1. Select the range of cells you want to format

  1. On the Home tab, in the Styles group, click Format as Table

  1. Under Light, Medium, or Dark, click the table style you want to use

  1. In the Format as Table dialog box, click OK. Notice that the Banded Rows check box is selected by default in the Table Style Options group. To apply shading to alternate columns instead of alternate rows, clear this check box and select Banded Columns

  1. If you want to convert the Excel table back to a regular range of cells, click anywhere in the table to display the tools necessary for converting the table back to a range of data

  1. On the Design tab, in the Tools group, click Convert to Range

Using Paste Link in Excel 2007

Most people know how to Cut, Copy and Paste in Excel 2007 but many don’t use Paste Link, which creates a link between the data or formula in the originalcelland the destination cell where it’s pasted. Here’s how:

  1. Click on a cell to make it the active cell

  1. Click on the Copy button on the Home tab (or press Ctrl + c)

  1. Click on the destination cell where you want the results to be displayed

  1. Click the small arrow at the bottom of the Paste button to open the drop down list

  1. Click Paste Link

The contents of the original cell now also appear in the destination cell.

Workbook Capacities in Excel 2007/2010

If you've upgraded to Excel 2007, or if you are thinking about it, you might be interested in the increased workbook capacities. With Excel 2007 you are no longer limited to 3 sort levels and a worksheet can contain more than a million rows.

Workbook Capacities / Excel 2003 / Excel 2007
Number of Worksheets / 255 / Limited to memory
Columns / 256 / 16,384
Rows / 65,536 / 1,048,576
Colors / 56 / 16 million
Sort levels / 3 / 64
Characters in formula / 1,024 / 8,192
Nested levels in formulas / 7 / 64
Arguments in a function / 30 / 255
Conditional formats / 3 / Limited to memory

Sorting and Filtering in Excel 2007

A big improvement in Excel 2007 is the ability to sort on up to 64 levels instead of the 3 we had available in prior versions. You can also filter data by color or by dates and Excel will display more than 1000 items in the AutoFilter drop-down list. A quick way to sort or filter data in Excel 2007 is to use the shortcut menu:

  1. Right click a column in your table

  1. Select Filter or Sort from the shortcut menu

  1. To sort by the column selected, click Sort A to Z or Sort Z to A

  1. To sort on multiple criteria, select Custom Sort to display the Sort dialog box

Add Color to Worksheet Tabs in Excel 2007

It can be useful to color code the tabs of Excel worksheets:

Select the tab of the sheet you want to re-color (to select more than one tab hold down the CTRL key and click each tab)
Right click and select Tab Color from the shortcut menu
Select color and click OK

This tip also works in previous versions of Excel.

Protect Cells in Excel 2007

You can prevent users from overwriting formulas or inadvertently changing labels or formats by protecting cells in a worksheet. When you protect a worksheet all cells are protected, or locked, and cannot be changed. To allow users to change certain cells in the worksheet you first identify which cells should NOT be locked. Then you protect, or lock, the worksheet. In Excel 2007, follow these steps:

Unlock cells that can be changed

  1. Select the cells that users should be able to change
  2. Click the Home tab on the Ribbon
  3. In the Font group, click the small arrow in the lower right corner to open the Format Cells dialog box
  4. Click the Protection tab
  5. Deselect the Locked checkbox and click OK
  6. Repeat these steps for each range of cells that can be changed, then protect the worksheet

Protect the worksheet

  1. Click the Review tab on the Ribbon
  2. In the Changes group, click Protect Sheet and click OK
  3. In the Protect Sheet dialog box enter a password, or leave blank if you don’t want a password, and click OK

Now users can only change the cells that are unlocked. If users try to change any other cells they receive a message saying they must unprotect the cells to modify them.

Total Data in an Excel 2007 Table

New in Excel 2007, you can now total the data in a Microsoft Office Excel table by displaying a totals row at the end of the table. Here's how:

  1. Click anywhere in the table to display the Table Tools Design tab

  1. Click the Design tab

  1. In the Table Style Options group, select the Total Row check box

  1. In the total row, click the cell in the column you want to calculate a total for

  1. Click the drop-down list arrow that appears

  1. In the drop-down list, select the function you want to use to calculate the total

Working with Large Excel 2007 Worksheets

Worksheets that are too large to be displayed on a single screen can cause problems when entering data as well as printing. There are 2 different features that will help you deal successfully with these issues.

To enter data into a worksheet that is wider or longer than the available screen area:

  1. Position the pointer beside and below the rows and/or columns to keep on the screen

  1. Click the View tab on the Ribbon

  1. In the Window group click Freeze Panes

To print a worksheet that is wider or longer than the available page size without losing the rows and/or columns required to give meaning to your data:

  1. Click the Page Layout tab on the Ribbon

  1. In the Page Setup group click Print Titles to display the Page Setup Dialog box

  1. Click the Sheet tab

  1. Select the columns and/or rows that need to be repeated on each printed page

  1. Click OK

Calculated Columns in Excel 2007 Tables

  1. Click a cell in a blank table column that you want to turn into a calculated column

  1. Type the formula

  1. The formula is automatically filled into all cells of the column

Freeze a Formula into its Current Value

To freeze a formula into its current value:

  1. Select the formula

  1. Press F2 (Edit)

  1. Press F9 (Calc)

  1. Press Enter

Now you can copy or move the value anywhere you need it. This trick works in all versions of Excel, even 2007!

Keyboard Shortcuts in Excel 2007

If you are a fan of keyboard shortcuts you will be happy to know that most of the shortcuts we've used for years work exactly the same in Excel 2007. Here's a list of some of my favorites:

Action / Keys
Start a new line in the same cell / Alt + Enter
Selects the entire worksheet / Ctrl + A
Undoes the last action / Ctrl + Z
Redoes the last action / Ctrl + Y
Calculates all worksheets in all open workbooks / F9
Copy selected cells / Ctrl + C
Paste / Ctrl + V
Select data range / Ctrl + Shift + *

For a complete listing of all Excel 2007 keyboard shortcuts, go toExcel shortcut and function keys ( on you can find lots of other great resources for learning Microsoft Office.