Advertisement

Microsoft Excel – Sum values based on multiple conditions

You want to sum values with more than one condition, such as the sum of sales of a certain product in a certain region. This is when you’d use the SUMIFS function in a formula.


Here’s an example where we have two conditions: we want the sum of Meat sales (from column C) in the South region (from column A).Data in an Excel worksheet

Here’s the formula you’d use:

=SUMIFS(D2:D11,A2:A11,”South”,C2:C11,”Meat”)The result is 14719, and here’s how the formula works.

=SUMIFS is an arithmetic formula. It calculates numbers, which in this case are in column D. So start by telling the formula where the numbers are:

  • =SUMIFS(D2:D11,

In other words, you want the formula to sum numbers in that column if they meet the conditions. That cell range is your first argument, or piece of data the function needs to run.

Next, you want to find data that meets two conditions, so you enter your first condition by telling the function where the data resides (A2:A11) and what the condition is, which is “South”. Notice the commas between the separate arguments.

  • =SUMIFS(D2:D11,A2:A11,”South”,

Quotation marks around “South” tell Excel it’s using text data.

Finally, you enter the arguments for your second condition – the range of cells (C2:C11) that contains the word “meat,” plus the word itself (surrounded by quotes) so that Excel can match it. End the formula with a closing parenthesis ) and then press Enter to get the result of 14719.

  • =SUMIFS(D2:D11,A2:A11,”South”,C2:C11,”Meat”)

As you type the SUMIFS function in Excel, if you don’t remember the arguments, help is nearby. After you type =SUMIFS( Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order.

Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_rangeis D2:D11, the column of numbers you want to sum; criteria_range1is A2.A11, the column of data where criteria1 “South” resides.

Using Formula AutoComplete to enter the SUMIFS function

As you type, the rest of the arguments will appear in Formula AutoComplete (not shown here); criteria_range2 is C2:C11, the column of data where criteria2 “Meat” resides.

If you click SUMIFS in Formula AutoComplete, an article opens to give you more help.

Give it a try

If you want to play around with the SUMIFS function, here’s some sample data and a formula that uses the function.

You can work with sample data and formulas right here, in this Excel Online workbook. Change values and formulas, or add your own values and formulas and watch the results change, live.

Copy all the cells in the table below, and paste into cell A1 in a new worksheet in Excel. You may want to adjust column widths to see the formulas better

Region Salesperson Type Sales
South Ito Beverages 3571
West Lannin Dairy 3338
East Makovec Beverages 5122
North Makovec Dairy 6239
South Jordan Produce 8677
South Lannin Meat 450
South Lannin Meat 7673
East Makovec Produce 664
North Lannin Produce 1500
South Jordan Meat 6596
Formula   Description Result
‘=SUMIFS(D2:D11,A2:A11,
“South”, C2:C11,”Meat”)
Sums the Meat Sales in
Column C in the South
region in Column A (result is 14719).
=SUMIFS(D2:D11,A2:A11,
“South”, C2:C11,”Meat”)

NOTE: If you want to create a total value for just one range based on a value in another range, use the SUMIF function.

 
 
For more information, please visit the Excel help center.

Microsoft Word – Use an Excel Spreadsheet for Mail Merge Contacts

Personalize names, addresses, and much more in bulk email or documents. Any data stored in an Excel workbook can be used to personalize communications.

Discover more Word training at LinkedIn Learning

Note: These steps only cover what’s shown in this video. For detailed steps on how to do a mail merge, please see Mail merge using an Excel spreadsheet.

Use an Excel spreadsheet for mail merge contacts

  1. Open the Word document that you want to mail merge.
  2. Select Mailings > Select Recipients.
  3. Select Use an Existing List.
  4. Select the Excel spreadheet you want to use, and select Open.
  5. Select the worksheet that you want to use, and select OK.Each worksheet name ends with $.
  6. Select Edit Recipient List to view the data source, select the checkboxes for the people you want to send to, and select OK.
  7. Save your Word file, to save the connections you’ve made.

Microsoft Excel 2016 – Using Excel Help

This guide will show you how to use the Excel Help feature to make it easier to understand functions of Excel and find solutions.

Excel Help does basic things like show shortcuts for a function just by hovering the cursor over the button. It can also explain the purpose of the tool/function. Some functions even have visuals included.


Excel Help Window

  • On many of the tools and functions there will be a link which says “tell me more”. This brings up the Excel Help window.
  • Another way to finding things in the Excel Help Window is by using the search bar by the Design tab.
    • Type the requested function or featureand the search results will display what it thinks is the desired outcome.
  • There should also be a result which says “Get Help on [X]”. On Macs, this can also be done by selecting Help from the Menu Bar and using the search function there.

Insights Panel

  1. From the same search area where “Get Help on [X]” is found, there is a search result which says “Smart Lookup on [X]”. 
  2. Select this option to open the Insights panel on the right-hand side of the excel window.
For more information, please visit the Excel help center.

Microsoft Excel – Basic tasks in Excel – General Overview

Excel is an incredibly powerful tool for getting meaning out of vast amounts of data. But it also works really well for simple calculations and tracking almost any kind of information. The key for unlocking all that potential is the grid of cells. Cells can contain numbers, text, or formulas. You put data in your cells and group them in rows and columns. That allows you to add up your data, sort and filter it, put it in tables, and build great-looking charts.


Create a new workbook

Excel documents are called workbooks. Each workbook has sheets, typically called spreadsheets. You can add as many sheets as you want to a workbook, or you can create new workbooks to keep your data separate.

  1. Click File, and then click New.
  2. Under New, click the Blank workbook.New blank workbook

Enter your data

  1. Click an empty cell.For example, cell A1 on a new sheet. Cells are referenced by their location in the row and column on the sheet, so cell A1 is in the first row of column A.
  2. Type text or a number in the cell.
  3. Press Enter or Tab to move to the next cell.

Use AutoSum to add your data

When you’ve entered numbers in your sheet, you might want to add them up. A fast way to do that is by using AutoSum.

  1. Select the cell to the right or below the numbers you want to add.
  2. Click the Home tab, and then click AutoSum in the Editing group.AutoSum on the Home tab

    AutoSum adds up the numbers and shows the result in the cell you selected.

Create a simple formula

Adding numbers is just one of the things you can do, but Excel can do other math as well. Try some simple formulas to add, subtract, multiply, or divide your numbers.

  1. Pick a cell, and then type an equal sign (=).That tells Excel that this cell will contain a formula.
  2. Type a combination of numbers and calculation operators, like the plus sign (+) for addition, the minus sign (-) for subtraction, the asterisk (*) for multiplication, or the forward slash (/) for division.For example, enter =2+4=4-2=2*4, or =4/2.
  3. Press Enter.This runs the calculation.

    You can also press Ctrl+Enter if you want the cursor to stay on the active cell.

Apply a number format

To distinguish between different types of numbers, add a format, like currency, percentages, or dates.

  1. Select the cells that have numbers you want to format.
  2. Click the Home tab, and then click the arrow in the General box.Format Number box on the Home tab
  3. Pick a number format.Number format gallery

    If you don’t see the number format you’re looking for, click More Number Formats.

Put your data in a table

A simple way to access Excel’s power is to put your data in a table. That lets you quickly filter or sort your data.

  1. Select your data by clicking the first cell and dragging to the last cell in your data.To use the keyboard, hold down Shift while you press the arrow keys to select your data.
  2. Click the Quick Analysis button Quick Analysis button in the bottom-right corner of the selection.Selected data with Quick Analysis Lens button visible
  3. Click Tables, move your cursor to the Table button to preview your data, and then click the Table button.Quick Analysis Tables gallery
  4. Click the arrow Filter drop-down arrow in the table header of a column.
  5. To filter the data, clear the Select All check box, and then select the data you want to show in your table.Select All box in the Sort and Filter gallery
  6. To sort the data, click Sort A to Z or Sort Z to A.Sorting commands in the Sort and Filter gallery
  7. Click OK.

Show totals for your numbers

Quick Analysis tools let you total your numbers quickly. Whether it’s a sum, average, or count you want, Excel shows the calculation results right below or next to your numbers.

  1. Select the cells that contain numbers you want to add or count.
  2. Click the Quick Analysis button Quick Analysis button in the bottom-right corner of the selection.
  3. Click Totals, move your cursor across the buttons to see the calculation results for your data, and then click the button to apply the totals.Quick Analysis Totals gallery

Add meaning to your data

Conditional formatting or sparklines can highlight your most important data or show data trends. Use the Quick Analysis tool for a Live Preview to try it out.

  1. Select the data you want to examine more closely.
  2. Click the Quick Analysis button button image in the bottom-right corner of the selection.
  3. Explore the options on the Formatting and Sparklines tabs to see how they affect your data.Quick Analysis Formatting gallery

    For example, pick a color scale in the Formatting gallery to differentiate high, medium, and low temperatures.

    Data with a color scale conditional format

  4. When you like what you see, click that option.

Show your data in a chart

The Quick Analysis tool recommends the right chart for your data and gives you a visual presentation in just a few clicks.

  1. Select the cells that contain the data you want to show in a chart.
  2. Click the Quick Analysis button button image in the bottom-right corner of the selection.
  3. Click the Charts tab, move across the recommended charts to see which one looks best for your data, and then click the one that you want.Quick Analysis Charts gallery

    NOTE:  Excel shows different charts in this gallery, depending on what’s recommended for your data.

Save your work

  1. Click the Save button on the Quick Access Toolbar, or press Ctrl+S.Save button on the Quick Access Toolbar

    If you’ve saved your work before, you’re done.

  2. If this is the first time you’ve save this file:
    1. Under Save As, pick where to save your workbook, and then browse to a folder.
    2. In the File name box, enter a name for your workbook.
    3. Click Save.

Print your work

  1. Click File, and then click Print, or press Ctrl+P.
  2. Preview the pages by clicking the Next Page and Previous Page arrows.Next and Previous buttons in the Print Preview pane

    The preview window displays the pages in black and white or in color, depending on your printer settings.

    If you don’t like how your pages will be printed, you can change page margins or add page breaks.

  3. Click Print.

For more information, please visit the Excel help center.

Microsoft Excel – View two or more worksheets at the same time

You can quickly compare two worksheets in the same workbook or in different workbooks by viewing them side by side. You can also arrange multiple worksheets to view them all at the same time.


NOTE: The images in this article were created in Excel 2016. If you’re using an earlier version your display might be slightly different.

View two worksheets in the same workbook side by side

  1. On the View tab, in the Window group, click New Window.Window group on the View tab
  2. On the View tab, in the Window group, click View Side by Side Button image .
  3. In each workbook window, click the sheet that you want to compare.
  4. To scroll both worksheets at the same time, click Synchronous Scrolling Synchronous Scrolling in the Window group on the View tab.

    NOTE:
     This option is available only when View Side by Side is turned on.

Tips

  • If you resize the workbook windows for optimal viewing, you can click Reset Window Position Button image to return to the original settings.
  • To restore a workbook window to full size, click Maximize Button image at the upper-right corner of the workbook window.

View two worksheets of different workbooks side by side

  1. Open both of the workbooks that contain the worksheets that you want to compare.
  2. On the View tab, in the Window group, click View Side by Side Button image .Window group on the View tab

    If you have more than two workbooks open, Excel displays the Compare Side by Side dialog box. In this dialog box, under Compare Side by Side with, click the workbook that contains the worksheet that you want to compare with your active worksheet, and then click OK.

  3. In each workbook window, click the sheet that you want to compare.
  4. To scroll both worksheets at the same time, click Synchronous Scrolling Synchronous Scrolling in the Window group on the View tab.NOTE: This option is available only when View Side by Side is turned on.

Tips

  • In Microsoft Excel 2013 and Excel 2016, workbooks that you open in Excel are no longer displayed as multiple workbook windows inside a single Excel window. Instead, they are shown as separate Excel windows. Because workbooks don’t share the same Excel window, they are displayed with their own ribbon, and you can view the open workbooks on different monitors.
  • If you resize the workbook windows for optimal viewing, you can click Reset Window Position Button image to return to the original settings.
  • To restore a workbook window to full size, click Maximize Button image at the upper-right corner of the workbook window.

View multiple worksheets at the same time

  1. Open one or more workbooks that contain the worksheets that you want to view at the same time.
  2. Do one of the following:
    • If the worksheets that you want to view are in the same workbook, do the following:
      1. Click a worksheet that you want to view.
      2. On the View tab, in the Window group, click New Window.Window group on the View tab
      3. Repeat steps 1 and 2 for each sheet that you want to view.
    • If the worksheets that you want to view are in different workbooks, continue with step 3.
  3. On the View tab, in the Window group, click Arrange All.
  4. Under Arrange, click the option that you want.
  5. If the sheets that you want to view are all located in the active workbook, select the Windows of active workbook check box.
TIP: To restore a workbook window to full size, click Maximize Button image at the upper-right corner of the workbook window.
 
For more information, please visit the Excel help center.

Microsoft Excel – Enter data manually in worksheet cells

You have several options when you want to enter data manually in Excel. You can enter data in one cell, in several cells at the same time, or on more than one worksheet at the same time. The data that you enter can be numbers, text dates, or times. You can format the data in a variety of ways. And, there are several settings that you can adjust to make data entry easier for you.


Note: If you can’t enter or edit data in a worksheet, it might have been protected by you or someone else to prevent data from being changed accidentally. On a protected worksheet, you can select cells to view the data, but you won’t’ be able to type information in cells that are locked. In most cases, you should not remove the protection from a worksheet unless you have permission to do so from the person who created it. To unprotect a worksheet, clickUnprotect Sheet in the Changes group on the Review tab. If a password was set when the worksheet protection was applied, you must first type that password to unprotect the worksheet. 

 
 
Enter text or a number in a cell
  1. On the worksheet, click a cell 
  2. type the numbers or text that you want to enter, and then press Enter or Tab.To enter data on a new line within a cell, enter a line break by pressing Alt+Enter.
 
Enter a number that has a fixed decimal point

  1. On the File tab, click Options. 
  2. Click Advanced, and then under Editing options, select the Automatically insert a decimal point check box. 
  3. In the Places box, enter a positive number four digits to the right of the decimal point or a negative number for digits to the left of the decimal point.For example, if you enter 3 in the Places box and then type 2834 in a cell, the value will appear as 2.834. If you enter a -3 in the Places box and then type 283, the value will be 283000.

     

  4. On the worksheet, click a cell, and then enter the number that you want.Data that you typed in cells before selecting the Fixed decimal option is not affected.

    To temporarily override the Fixed decimal option, type a decimal point when you enter the number.

 
Enter a date or time in a cell
  1. On the worksheet, click a cell. 
  2. type a date or time as follows: 
    • To enter a date, use a slash mark or a hyphen to separate the parts of a date; for example, type 9/5/2021 or 5-Sep-2021. 
    • To enter a time that is based on the 12-hour clock, enter the time followed by a space, and then type a or p after the time, for example, 9:00 p. Otherwise, Excel enters the time as AM.To enter the current date and time, press Ctrl+Shift+; (semicolon).

       

    • To enter a date or time that stays current when you reopen a worksheet, you can use the TODAY and NOW functions. 
    • When you enter a date or a time in a cell, it appears either in the default date or time format for your computer or in the format that was applied to the cell before you entered the date or time. The default date or time format is based on the date and time settings in the Regional and Language Options dialog box (Control Panel, Clock, Language, and Region). If these settings on your computer have been changed, the dates and times in your workbooks that have not been formatted by using the Format Cells command are displayed according to those settings. 
    • To apply the default date or time format, click the cell that contains the date or time value, and then press Ctrl+Shift+# or Ctrl+Shift+@
 
Enter the same data into several cells at the same time
  1. Select the cells into which you want to enter the same data. The cells do not have to be adjacent.How to select cells

     

    To select Do this
    A single cell Click the cell, or press the arrow keys to move to the cell.
    A range of cells Click the first cell in the range, and then drag to the last cell, or hold down Shift while you press the arrow keys to extend the selection.

    You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.

    A large range of cells Click the first cell in the range, and then hold down Shift while you click the last cell in the range. You can scroll to make the last cell visible.
    All cells on a worksheet Click the Select All button.

    Select All button

    To select the entire worksheet, you can also press Ctrl+A.

    If the worksheet contains data, Ctrl+A selects the current region. Pressing Ctrl+A a second time selects the entire worksheet.

    Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down Ctrl while you select the other cells or ranges.

    You can also select the first cell or range of cells, and then press Shift+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press Shift+F8 again.

    NOTE: You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.

    An entire row or column Click the row or column heading.

    Worksheet headings

    1. Row heading

    2. Column heading

    You can also select cells in a row or column by selecting the first cell and then pressing Ctrl+Shift+Arrow key (Right Arrow or Left Arrow for rows, Up Arrow or Down Arrow for columns).

    If the row or column contains data, Ctrl+Shift+Arrow key selects the row or column to the last used cell. Pressing Ctrl+Shift+Arrow key a second time selects the entire row or column.

    Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down Shift while you select the last row or column.
    Nonadjacent rows or columns Click the column or row heading of the first row or column in your selection; then hold down Ctrl while you click the column or row headings of other rows or columns that you want to add to the selection.
    The first or last cell in a row or column Select a cell in the row or column, and then press Ctrl+Arrow key (Right Arrow or Left Arrow for rows, Up Arrow or Down Arrow for columns).
    The first or last cell on a worksheet or in a Microsoft Office Excel table Press Ctrl+Home to select the first cell on the worksheet or in an Excel list.

    Press Ctrl+End to select the last cell on the worksheet or in an Excel list that contains data or formatting.

    Cells to the last used cell on the worksheet (lower-right corner) Select the first cell, and then press Ctrl+Shift+End to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
    Cells to the beginning of the worksheet Select the first cell, and then press Ctrl+Shift+Home to extend the selection of cells to the beginning of the worksheet.
    More or fewer cells than the active selection Hold down Shift while you click the last cell that you want to include in the new selection. The rectangular range between the active cell and the cell that you click becomes the new selection.

    To cancel a selection of cells, click any cell on the worksheet.

  2. In the active cell, type the data, and then press Ctrl+Enter.
    You can also enter the same data into several cells by using the fill handle Fill handle  to automatically fill data in worksheet cells. 
 
Enter the same data on several worksheets at the same time
  1. Click the tab of the first worksheet that contains the data that you want to edit. Then hold down Ctrl while you click the tabs of other worksheets in which you want to synchronize the data.
    Tab scrolling buttons
    Note: If you don’t see the tab of the worksheet that you want, click the tab scrolling buttons to find the worksheet, and then click its tab. If you still can’t find the worksheet tabs that you want, you might have to maximize the document window.
     
  2. On the active worksheet, select the cell or range in which you want ot edit existing or enter new data. 

    How to select cells

    To select Do this
    A single cell Click the cell, or press the arrow keys to move to the cell.
    A range of cells Click the first cell in the range, and then drag to the last cell, or hold down Shift while you press the arrow keys to extend the selection.

    You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow keys. To stop extending the selection, press F8 again.

    A large range of cells Click the first cell in the range, and then hold down Shift while you click the last cell in the range. You can scroll to make the last cell visible.
    All cells on a worksheet Click the Select All button.

    Select All button

    To select the entire worksheet, you can also press Ctrl+A.

    If the worksheet contains data, Ctrl+A selects the current region. Pressing Ctrl+A a second time selects the entire worksheet.

    Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down Ctrl while you select the other cells or ranges.

    You can also select the first cell or range of cells, and then press Shift+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press Shift+F8 again.

    NOTE: You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection.

    An entire row or column Click the row or column heading.

    Worksheet headings

    1. Row heading

    2. Column heading

    You can also select cells in a row or column by selecting the first cell and then pressing Ctrl+Shift+Arrow key (Right Arrow or Left Arrow for rows, Up Arrow or Down Arrow for columns).

    If the row or column contains data, Ctrl+Shift+Arrow key selects the row or column to the last used cell. Pressing Ctrl+Shift+Arrow key a second time selects the entire row or column.

    Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down Shift while you select the last row or column.
    Nonadjacent rows or columns Click the column or row heading of the first row or column in your selection; then hold down Ctrl while you click the column or row headings of other rows or columns that you want to add to the selection.
    The first or last cell in a row or column Select a cell in the row or column, and then press Ctrl+Arrow key (Right Arrow or Left Arrow for rows, Up Arrow or Down Arrow for columns).
    The first or last cell on a worksheet or in a Microsoft Office Excel table Press Ctrl+Home to select the first cell on the worksheet or in an Excel list.

    Press Ctrl+End to select the last cell on the worksheet or in an Excel list that contains data or formatting.

    Cells to the last used cell on the worksheet (lower-right corner) Select the first cell, and then press Ctrl+Shift+End to extend the selection of cells to the last used cell on the worksheet (lower-right corner).
    Cells to the beginning of the worksheet Select the first cell, and then press Ctrl+Shift+Home to extend the selection of cells to the beginning of the worksheet.
    More or fewer cells than the active selection Hold down Shift while you click the last cell that you want to include in the new selection. The rectangular range between the active cell and the cell that you click becomes the new selection.

    To cancel a selection of cells, click any cell on the worksheet.

  3. In the active cell, type new data or edit the existing data, and then press Enter or Tab to move the selection to the next cell.
    The changes are applied to all the worksheets that you selected. 
  4. Repeat the previous step until you have completed entering or editing data. 
    • To cancel a selection of multiple worksheets, click any unselected worksheet. If an unselected worksheet is not visible, you can right-click the tab of a selected worksheet, and then click Ungroup Sheets./ 
    • When you enter or edit data, the changes affect all the selected worksheets and can inadvertently replace data that you didn’t mean to change. To help avoid this, you can view all the worksheets at the same time to identify potential data conflicts. 
      1. On the View tab, in the Window group, click New Window.
      2. Switch to the new window, and then click a worksheet that you want to view. 
      3. Repeat steps 1 and 2 for each worksheet that you want to view. 
      4. On the View tab, in the Window group, click Arrange All, and then click the option that you want. 
      5. To view worksheets int he active workbonly ony, in the Arrange Windows dialog box, select the Windows of active workbook check box. 
Adjust worksheet settings and cell formats
 
There are several settings in Excel that you can change to help make manual data entry easier. Some changes affect all workbooks, some affect the whole worksheet, and some affect only the cells that you specify. 
 
Change the direction for the Enter key
 
When you press Tab to enter data in several cells in a row and then press Enter at the end of that row, by default, the selection moves to the start of the next row. 
 
Pressing Enter moves the selection down one cell, and pressing Tab moves the selection one cell to the right. You cannot change the direction of the move for the Tab key, but you can specify a different direction for the Enter key. Changing this setting affects the whole worksheet, any other open worksheets, any other open workbooks, and all new workbooks. 
  1. On the File tab, click Options. 
  2. In the Advanced category, under Editing options, select the After pressing Enter, move selection check box, and then click the direction that you want in the Direction box. 
 
Change the width of a column
 
At time, a cell might display ####. This can occur when the cell contains a number or a date and the width of its column cannot display all the characters that its format requires. For example, suppose a cell with the Date format “mm/dd/yyyy” contains 12/31/2051. However, the column is only wide enough to display six characters. The cell will display ####. To see the entire contents of the cell with its current format, you must increase the width of the column. 
  1. Click the cell from which you want to change the column width. 
  2. On the Home tab, in the Cells group, click Format. Cells group on the Home tab

     

  3. Under Cell Size, do one of the following: 
    • To fit all text in the cell, click AutoFit Column Width. 
    • To specify a larger column width, click Column Width, and then type the width that you want in the Column width box.Note: As an alternative to increasing the width of a column, you can change the format of that column or even an individual cell. For example, you could change the date format so that a date is displayed as only the month and day (“mm/dd” format), such as 12/31, or represent a number in a Scientific (exponential) format, such as 4E+08.
 
Wrap text in a cell
 
You can display multiple lines of text inside a cell by wrapping the text. Wrapping text in a cell does not affect other cells. 
  1. Click the cell in which you want to wrap the text. 
  2. On the Home tab, int eh Alignment group, click Wrap Text.Alignment group on the Home tab

    Note: If the text is a long word, the characters won’t wrap (the word won’t be split); instead, you can widen the column or decrease the font size to see all the text. If all the text is not visible after you wrap the text, you might have to adjust the height of the row. On the Home tab, in the Cells group, click Format, and then under Cell Size click AutoFit Row.

 
Change the format of a number
 
In Excel, the format of a cell is separate from the data that is stored int eh cell. This display difference can have a significant effect when the data is numeric. For example, when a number that you enter is rounded, usually only the displayed number is rounded. Calculations use the actual number that is store dint he cell, not the formatted number that is displayed. Hence, calculations might appear inaccurate because of rounding in one or more cells. 
 
After you type numbers in a cell, you can change the format in which they are displayed. 
  1. Click the cell that contains the numbers that you want to format. 
  2. On the Home tab, int he Numbers group, click the arrow next to the Number Format box and then click the format that you want.Format Number box on the Home tab

    To select a number format from the list of available formats, click More Number Formats, and then click the format that you want to use int he Category list.

 
Format a number as a text
 
for numbers that should not be calculated in Excel, such as phone numbers, you can format them as text by applying the Text format to empty cells before typing the numbers. 
  1. Select an empty cell. 
  2. On the Home tab, in the Number group, click the arrow next to the Number Format box, and then click Text.Format Number box on the Home tab

     

  3. Type the numbers that you want in the formatted cell.Numbers that you entered before you applied the Text format to the cells must be entered again in the formatted cells. To quickly reenter numbers as text, select each cell, press F2, and then press Enter.

Microsoft Excel – Define and use names in formulas

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.


Learn more about using names

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula, or table, each of which may be difficult to comprehend at first glance. The following information shows common examples of names and how they can improve clarity and understanding.

Example Type Example with no name Example with a name
Reference =SUM(C20:C30) =SUM(FirstQuarterSales)
Constant =PRODUCT(A5,8.3) =PRODUCT(Price,WASalesTax)
Formula =SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) =SUM(Inventory_Level,-Order_Amt)
Table C4:G36 =TopSales06

Types of names

There are several types of names that you can create and use.

Defined name    A name that represents a cell, range of cells, formula, or constant value. You can create your own defined name, and Microsoft Office Excel sometimes creates a defined name for you, such as when you set a print area.

Table name    A name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of Table1, Table2, and so on, each time that you insert an Excel table, but you can change a table’s name to make it more meaningful. 

The scope of a name

All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognized without qualification. For example:

  • If you have defined a name, such as Budget_FY08, and its scope is Sheet1, that name, if not qualified, is recognized only in Sheet1, but not in other sheets without qualification.To use a local worksheet name in another worksheet, you can qualify it by preceding it with the worksheet name, as the following example shows:

    Sheet1!Budget_FY08

  • If you have defined a name, such as Sales_Dept_Goals, and its scope is the workbook, that name is recognized for all worksheets in that workbook, but not for any other workbook.

A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However you can use the same name in different scopes. For example, you can define a name, such as GrossProfit that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name, GrossProfit, is always referencing the same cells at the local worksheet level.

You can even define the same name, GrossProfit, for the global workbook level, but again the scope is unique. In this case, however, there can be a name conflict. To resolve this conflict, by default Excel uses the name that is defined for the worksheet because the local worksheet level takes precedence over the global workbook level. If you want to override the precedence and you want to use the workbook name, you can disambiguate the name by prefixing the workbook name as the following example shows:

WorkbookFile!GrossProfit

You can override the local worksheet level for all worksheets in the workbook, with the exception of the first worksheet, which always uses the local name if there is a name conflict and cannot be overridden.

Defining and entering names

You define a name by using the:

  • Name box on the formula bar     This is best used for creating a workbook level name for a selected range.
  • Create a name from selection     You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.
  • New Name dialog box    This is best used for when you want more flexibility in creating names, such as specifying a local worksheet level scope or creating a name comment.

Note: By default, names use absolute cell references.

You can enter a name by:

  • Typing:     Typing the name, for example, as an argument to a formula.
  • Using Formula AutoComplete:    Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you.
  • Selecting from the Use in Formula command:    Select a defined name from a list available from the Use in Formula command in the Defined Names group on the Formulas tab.

Auditing names

You can also create a list of defined names in a workbook. Locate an area with two empty columns on the worksheet (the list will contain two columns, one for the name and one for a description of the name). Select a cell that will be the upper-left corner of the list. On the Formulas tab, in the Defined Names group, click Use in Formula, click Paste and then, in the Paste Names dialog box, click Paste List.

Learn about syntax rules for names

The following is a list of syntax rules that you need to be aware of when you create and edit names.

  • Valid characters    The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.Tip: You cannot use the uppercase and lowercase characters “C”, “c”, “R”, or “r” as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
  • Cell references disallowed    Names cannot be the same as a cell reference, such as Z$100 or R1C1.
  • Spaces are not valid     Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
  • Name length    A name can contain up to 255 characters.
  • Case sensitivity    Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

Define a name for a cell or cell range on a worksheet

  1. Select the cell, range of cells, or nonadjacent selections that you want to name.
  2. Click the Name box at the left end of the formula bar.Name box

    Name box

  3. Type the name that you want to use to refer to your selection. Names can be up to 255 characters in length.
  4. Press ENTER.

Note: You cannot name a cell while you are changing the contents of the cell.

Define a name by using a selection of cells in the worksheet

You can convert existing row and column labels to names.

  1. Select the range that you want to name, including the row or column labels.
  2. On the Formulas tab, in the Defined Names group, click Create from Selection.The Defined Names group on the Formulas tab
  3. In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top rowLeft columnBottom row, or Right column check box. A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels.

Define a name by using the New Name dialog box

  1. On the Formulas tab, in the Defined Names group, click Define Name.The Defined Names group on the Formulas tab
  2. In the New Name dialog box, in the Name box, type the name that you want to use for your reference.Note: Names can be up to 255 characters in length.
  3. To specify the scope of the name, in the Scope drop-down list box, select Workbook or the name of a worksheet in the workbook.
  4. Optionally, in the Comment box, enter a descriptive comment up to 255 characters.Note: If you save the workbook to Microsoft Office SharePoint Server 2007  Excel Services, and you specify one or more parameters, the comment is used as a ScreenTip in the Parameters Task Pane.
  5. In the Refers to box, do one of the following:
    • To enter a cell reference, type the cell reference.Tip: The current selection is entered by default. To enter other cell references as an argument, click Collapse Dialog Button image (which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog Button image .
    • To enter a constant, type = (equal sign) and then type the constant value.
    • To enter a formula, type = and then type the formula.
  6. To finish and return to the worksheet, click OK.

Tip: To make the New Name dialog box wider or longer, click and drag the grip handle at the bottom.

Manage names by using the Name Manager dialog box

Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.

To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

The Defined Names group on the Formulas tab

View names

The Name Manager dialog box displays the following information about each name in a list box:

This Column: Displays:
Icon and Name One of the following:

  • A defined name, which is indicated by a defined name icon. Defined name icon
  • A table name, which is indicated by a table name icon. Table name icon
Value The current value of the name, such as the results of a formula, a string constant, a cell range, an error, an array of values, or a placeholder if the formula cannot be evaluated. The following are representative examples:

  • “this is my string constant”
  • 3.1459
  • {2003;12,2002;23,;2001,18}
  • #REF!
  • {…}
Refers To The current reference for the name. The following are representative examples:

  • =Sheet1!$A$3
  • =8.3
  • =HR!$A$1:$Z$345
  • =SUM(Sheet1!A1,Sheet2!B2)
Scope
  • A worksheet name, if the scope is the local worksheet level.
  • “Workbook”, if the scope is the global worksheet level.
Comment Additional information about the name up to 255 characters. The following are representative examples:

  • This value will expire on May 2, 2007.
  • Don’t delete! Critical name!
  • Based on the ISO certification exam numbers.

Note: If you save the workbook to Microsoft Office SharePoint Server 2007  Excel Services, and you specify one or more parameters, the comment is used as a ScreenTip in the Parameters Task Pane.

  • You cannot use the Name Manager dialog box while you are changing the contents of the cell.
  • The Name Manager dialog box does not display names defined in Visual Basic for Applications (VBA), or hidden names (the Visible property of the name is set to “False”).

Resize columns

  • To automatically size the column to fit the largest value in that column, double-click the right side of the column header.

Sort names

  • To sort the list of names in ascending or descending order, alternately click the column header.

Filter names

Use the commands in the Filter drop-down list to quickly display a subset of names. Selecting each command toggles the filter operation on or off, which makes it easy to combine or remove different filter operations to get the results that you want.

To filter the list of names, do one or more of the following:

Select: To:
Names Scoped To Worksheet Display only those names that are local to a worksheet.
Names Scoped To Workbook Display only those names that are global to a workbook.
Names With Errors Display only those names with values that contain errors (such as #REF, #VALUE, or #NAME).
Names Without Errors Display only those names with values that do not contain errors.
Defined Names Display only names defined by you or by Excel, such as a print area.
Table Names Display only table names.

Change a name

If you change a defined name or table name, all uses of that name in the workbook are also changed.

  1. On the Formulas tab, in the Defined Names group, click Name Manager.The Defined Names group on the Formulas tab
  2. In the Name Manager dialog box, click the name that you want to change, and then click Edit.Tip: You can also double-click the name.
  3. In the Edit Name dialog box, in the Name box, type the new name for the reference.
  4. In the Refers to box, change the reference , and then click OK.
  5. In the Name Manager dialog box, in the Refers to box, change the cell, formula, or constant represented by the name.
    • To cancel unwanted or accidental changes, click Cancel Cancel button , or press ESC.
    • To save changes, click Commit Enter button , or press ENTER.

The Close button only closes the Name Manager dialog box. It is not required to commit changes that have already been made.

Delete one or more names

  1. On the Formulas tab, in the Defined Names group, click Name Manager.The Defined Names group on the Formulas tab
  2. In the Name Manager dialog box, click the name that you want to change.
  3. Select one or more names by doing one of the following:
    • To select a name, click it.
    • To select more than one name in a contiguous group, click and drag the names, or press SHIFT and click the mouse button for each name in the group.
    • To select more than one name in a noncontiguous group, press CTRL and click the mouse button for each name in the group.
  4. Click Delete. You can also press DELETE.
  5. Click OK to confirm the deletion.

The Close button only closes the Name Manager dialog box. It is not required to commit changes that have already been made.

Microsoft Excel – Keyboard shortcuts for Mac Excel

This topic itemizes all keyboard shortcuts for Excel 2016 for Mac. Keyboard shortcuts allow you to quickly complete commands and functions without using a mouse. Many of the shortcuts that use the Ctrl key on a Windows keyboard also work with the Control key in Excel 2016 for Mac. However, not all do. The shortcuts in this article refer to the US keyboard layout. Keys for other layouts might not correspond exactly to the keys on a US keyboard.


  • If a shortcut requires pressing two or more keys at the same time, this topic separates the keys with a plus sign (+). If you have to press one key immediately after another, the keys are separated by a comma (,).
  • The settings in some versions of the Mac operating system (OS) and some utility applications might conflict with keyboard shortcuts and function key operations in Office 2016 for Mac. For information about changing the key assignment of a keyboard shortcut, see Mac Help for your version of the Mac OS or see your utility application. Also see Shortcut conflicts later in this topic.

Frequently used shortcuts

This table itemizes the most frequently used shortcuts in Excel 2016 for Mac.

To do this Press
Paste COMMAND + V
or
CONTROL + V
Copy COMMAND + C
or
CONTROL + C
Clear DELETE
Save COMMAND + S
or
CONTROL + S
Undo COMMAND + Z
or
CONTROL + Z
Redo COMMAND + Y
or
CONTROL + Y
or
COMMAND + SHIFT+ Z
Cut COMMAND + X
or
CONTROL + X
Bold COMMAND + B
or
CONTROL + B
Print COMMAND + P
or
CONTROL + P
Open Visual Basic OPTION + F11
Fill Down COMMAND + D
or
CONTROL + D
Fill Right COMMAND + R
or
CONTROL + R
Insert cells CONTROL + SHIFT + =
Delete cells COMMAND + HYPHEN
or
CONTROL + HYPHEN
Calculate all open workbooks COMMAND + =
or
F9
Close window COMMAND + W
or
CONTROL + W
Quit Excel COMMAND + Q
Display the Go To dialog box CONTROL + G
or
F5
Display the Format Cells dialog box COMMAND + 1
or
CONTROL + 1
Display the Replace dialog box CONTROL + H
or
COMMAND + SHIFT + H
Paste Special COMMAND + CONTROL + V
or
CONTROL + OPTION + V
or
COMMAND + OPTION + V
Underline COMMAND + U
Italic COMMAND + I
or
CONTROL + I
New blank workbook COMMAND + N
or
CONTROL + N
New workbook from template COMMAND + SHIFT + P
Display the Save As dialog box COMMAND + SHIFT + S
or
F12
Display the Help window F1
or
COMMAND + /
Select All COMMAND + A
or
COMMAND + SHIFT + SPACEBAR
Add or remove a filter COMMAND + SHIFT + F
or
CONTROL + SHIFT + L
Minimize or maximize the ribbon tabs COMMAND + OPTION + R
Display the Open dialog box COMMAND + O
or
CONTROL + O
Check spelling F7
Open the thesaurus SHIFT + F7
Display the Formula Builder SHIFT + F3
Open the Define Name dialog box COMMAND + F3
Open the Create names dialog box COMMAND + SHIFT + F3
Insert a new sheet * SHIFT + F11
Print COMMAND + P
or
CONTROL + P
Print preview COMMAND + P
or
CONTROL + P

Get started

Many keyboards assign special functions to function keys, by default. To use the function key for other purposes, you have to press Fn+the function key. 

Shortcut conflicts

Some Windows keyboard shortcuts conflict with the corresponding default Mac OS keyboard shortcuts. This topic flags such shortcuts with an asterisk ( * ). To use these shortcuts, you may have to change your Mac keyboard settings to change the Show Desktop shortcut for the key.

Change system preferences for keyboard shortcuts with the mouse

  1. On the Apple menu, press System Preferences.
  2. Press Keyboard.
  3. In the tabs, press Shortcuts.
  4. Click Mission Control.
  5. Clear the check box for the keyboard shortcut that you want to use.

Work in windows and dialogs

To do this Press
Expand or minimize the ribbon COMMAND + OPTION + R
Switch to full screen view COMMAND + CONTROL + F
Switch to the next application COMMAND + TAB
Switch to the previous application COMMAND + SHIFT + TAB
Close the active workbook window COMMAND + W
Copy the image of the screen and save it to
a Screen Shot file on your desktop.
COMMAND + SHIFT + 3
Minimize the active window CONTROL + F9
Maximize or restore the active window CONTROL + F10
or
COMMAND + F10
Hide Excel. COMMAND + H
Move to the next box, option, control, or command TAB
Move to the previous box, option, control, or command SHIFT + TAB
Exit a dialog or cancel an action ESC
Perform the action assigned to the default command button (the button with the bold outline, often the OK button) RETURN
Cancel the command and close ESC

Move and scroll in a sheet or workbook

To do this Press
Move one cell up, down, left, or right ARROW KEYS
Move to the edge of the current data region COMMAND + ARROW KEY
Move to the beginning of the row HOME
On a MacBook, press FN + LEFT ARROW
Move to the beginning of the sheet CONTROL + HOME
On a MacBook, press CONTROL + FN + LEFT ARROW
Move to the last cell in use on the sheet CONTROL + END
On a MacBook, press CONTROL + FN + RIGHT ARROW
Move down one screen PAGE DOWN
On a MacBook, press FN + DOWN ARROW
Move up one screen PAGE UP
On a MacBook, press FN + UP ARROW
Move one screen to the right OPTION + PAGE DOWN
On a MacBook, press FN + OPTION + DOWN ARROW
Move one screen to the left OPTION + PAGE UP
On a MacBook, press FN + OPTION + UP ARROW
Move to the next sheet in the workbook CONTROL + PAGE DOWN
or
OPTION + RIGHT ARROW
Move to the previous sheet in the workbook CONTROL + PAGE DOWN
or
OPTION + LEFT ARROW
Scroll to display the active cell CONTROL + DELETE
Display the Go To dialog box CONTROL + G
Display the Find dialog box CONTROL + F
or
SHIFT + F5
Access search (when in a cell or when a cell is selected) COMMAND + F
Move between unlocked cells on a protected sheet TAB

Enter data on a sheet

To do this Press
Edit the selected cell F2
Complete a cell entry and move forward in the selection RETURN
Start a new line in the same cell CONTROL + OPTION + RETURN
Fill the selected cell range with the text that you type COMMAND  + RETURN
or
CONTROL + RETURN
Complete a cell entry and move up in the selection SHIFT + RETURN
Complete a cell entry and move to the right in the selection TAB
Complete a cell entry and move to the left in the selection SHIFT + TAB
Cancel a cell entry ESC
Delete the character to the left of the insertion point, or delete the selection DELETE
Delete the character to the right of the insertion point, or delete the selection
Note: Some smaller keyboards do not have this key
Delete
On a MacBook, press FN + DELETE
Delete text to the end of the line
Note: Some smaller keyboards do not have this key
CONTROL + Delete
On a MacBook, press CONTROL + FN + DELETE
Move one character up, down, left, or right ARROW KEYS
Move to the beginning of the line HOME
On a MacBook, press FN + LEFT ARROW
Insert a comment SHIFT + F2
Open and edit a cell comment SHIFT + F2
Fill down CONTROL + D
or
COMMAND  + D
Fill to the right CONTROL + R
or
COMMAND  + R
Define a name CONTROL + L

Work in cells or the Formula bar

To do this Press
Edit the selected cell F2
Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents DELETE
Complete a cell entry RETURN
Enter a formula as an array formula COMMAND + SHIFT + RETURN
or
CONTROL + SHIFT + RETURN
Cancel an entry in the cell or formula bar ESC
Display the Formula Builder after you type a valid function name in a formula CONTROL + A
Insert a hyperlink COMMAND + K
or
CONTROL + K
Edit the active cell and position the insertion point at the end of the line CONTROL + U
Open the Formula Builder SHIFT + F3
Calculate the active sheet SHIFT + F9
Display a contextual menu SHIFT + F10
Start a formula =
Toggle the formula reference style between absolute, relative, and mixed COMMAND + T
or
F4
Insert the AutoSum formula COMMAND + SHIFT + T
Enter the date CONTROL + SEMICOLON (;)
Enter the time COMMAND + SEMICOLON (;)
Copy the value from the cell above the active cell into the cell or the formula bar CONTROL + SHIFT + INCH MARK (“)
Alternate between displaying cell values and displaying cell formulas CONTROL + GRAVE ACCENT (`)
Copy a formula from the cell above the active cell into the cell or the formula bar CONTROL + APOSTROPHE (‘)
Display the AutoComplete list CONTROL + OPTION + DOWN ARROW
Define a name CONTROL + L
Open the Smart Lookup pane CONTROL + OPTION + COMMAND+ L

Format and edit data

To do this Press
Edit the selected cell F2
Create a table COMMAND + T
or
CONTROL + T
Insert a line break in a cell COMMAND + OPTION + RETURN
or
CONTROL + OPTION + RETURN
Insert special characters like symbols, including emoji CONTROL + COMMAND + SPACEBAR
Increase font size COMMAND + SHIFT + >
Decrease font size COMMAND + SHIFT + <
Align center COMMAND + E
Align left COMMAND + L
Display the Modify Cell Style dialog box COMMAND + SHIFT + L
Display the Format Cells dialog box COMMAND + 1
Apply the general number format CONTROL + SHIFT + ~
Apply the currency format with two decimal places (negative numbers appear in red with parentheses) CONTROL + SHIFT + $
Apply the percentage format with no decimal places CONTROL + SHIFT + %
Apply the exponential number format with two decimal places CONTROL + SHIFT + ^
Apply the date format with the day, month, and year CONTROL + SHIFT + #
Apply the time format with the hour and minute, and indicate AM or PM CONTROL + SHIFT + @
Apply the number format with two decimal places, thousands separator, and minus sign (-) for negative values CONTROL + SHIFT + !
Apply the outline border around the selected cells COMMAND + OPTION + ZERO
Add an outline border to the right of the selection COMMAND + OPTION + RIGHT ARROW
Add an outline border to the left of the selection COMMAND + OPTION + LEFT ARROW
Add an outline border to the top of the selection COMMAND + OPTION + UP ARROW
Add an outline border to the bottom of the selection COMMAND + OPTION + DOWN ARROW
Remove outline borders COMMAND + OPTION + HYPHEN
Apply or remove bold formatting COMMAND + B
Apply or remove italic formatting COMMAND + I
Apply or remove underscoring COMMAND + U
Apply or remove strikethrough formatting COMMAND + SHIFT + X
Hide a column COMMAND + )
or
CONTROL + )
Unhide a column COMMAND + SHIFT + )
or
CONTROL + SHIFT + )
Hide a row COMMAND + (
or
CONTROL + (
Unhide a row COMMAND + SHIFT + (
or
CONTROL + SHIFT + (
Edit the active cell CONTROL + U
Cancel an entry in the cell or the formula bar ESC
Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents DELETE
Paste text into the active cell COMMAND + V
Complete a cell entry RETURN
Give selected cells the current cell’s entry COMMAND + RETURN
or
CONTROL + RETURN
Enter a formula as an array formula COMMAND + SHIFT + RETURN
or
CONTROL + SHIFT + RETURN
Display the Formula Builder after you type a valid function name in a formula CONTROL + A

Select cells, columns, or rows

To do this Press
Extend the selection by one cell SHIFT + ARROW KEY
Extend the selection to the last nonblank cell
in the same column or row as the active cell
COMMAND  + SHIFT + ARROW KEY
Extend the selection to the beginning of the row SHIFT + HOME
On a MacBook, press SHIFT + FN + LEFT ARROW
Extend the selection to the beginning of the sheet CONTROL + SHIFT + HOME
On a MacBook, press CONTROL + SHIFT + FN + LEFT ARROW
Extend the selection to the last cell used
on the sheet (lower-right corner)
CONTROL + SHIFT + END
On a MacBook, press CONTROL + SHIFT + FN + RIGHT ARROW
Select the entire column CONTROL + SPACEBAR
Select the entire row SHIFT + SPACEBAR
Select the entire sheet COMMAND + A
Select only visible cells COMMAND + SHIFT + * (asterisk)
Select only the active cell when multiple cells are selected SHIFT + DELETE
Extend the selection down one screen SHIFT + PAGE DOWN
On a MacBook, SHIFT + FN + DOWN ARROW
Extend the selection up one screen SHIFT + PAGE UP
On a MacBook, SHIFT + FN + UP ARROW
Alternate between hiding objects, displaying objects,
and displaying placeholders for objects
CONTROL + 6
Turn on the capability to extend a selection
by using the arrow keys
F8
Add another range of cells to the selection SHIFT + F8
Select the current array, which is the array that the
active cell belongs to
CONTROL + /
Select cells in a row that don’t match the value
in the active cell in that row.
You must select the row starting with the active cell
CONTROL + \
Select only cells that are directly referred to by formulas in the selection CONTROL + SHIFT + [
Select all cells that are directly or indirectly referred to by formulas in the selection CONTROL + SHIFT + {
Select only cells with formulas that refer directly to the active cell CONTROL + ]
Select all cells with formulas that refer directly or indirectly to the active cell CONTROL + SHIFT + }

Work with a selection

To do this Press
Copy COMMAND + C
or
CONTROL + V
Paste COMMAND + V
or
CONTROL + V
Cut COMMAND + X
or
CONTROL + X
Clear DELETE
Delete the selection CONTROL + HYPHEN
Undo the last action COMMAND + Z
Hide a column COMMAND + )
or
CONTROL + )
Unhide a column COMMAND + SHIFT + )
or
CONTROL + SHIFT + )
Hide a row COMMAND + (
or
CONTROL + (
Unhide a row COMMAND + SHIFT + (
or
CONTROL + SHIFT + (
Move from top to bottom within the selection (down) * RETURN
Move from bottom to top within the selection (up) * SHIFT + RETURN
Move from left to right within the selection,
or move down one cell if only one column is selected
TAB
Move from right to left within the selection,
or move up one cell if only one column is selected
SHIFT + TAB
Move clockwise to the next corner of the selection CONTROL + PERIOD
Group selected cells COMMAND + SHIFT + K
Ungroup selected cells COMMAND + SHIFT + J

* These shortcuts may move in another direction other than down or up. If you’d like to change the direction of these shortcuts using the mouse, on the Excel menu, click Preferences, click Edit, and then, under. After pressing Return, move selection, select the direction you want to move in.

Use charts

To do this Press
Insert a new chart sheet. * F11
Cycle through chart object selection ARROW KEYS

Sort, filter, and use PivotTable reports

To do this Press
Open the Sort dialog box COMMAND + SHIFT + R
Add or remove a filter COMMAND + SHIFT + F
or
CONTROL + SHIFT + L
Display the Filter list or PivotTable page
field pop-up menu for the selected cell
OPTION + DOWN ARROW

Outline data

To do this Press
Display or hide outline symbols CONTROL + 8
Hide selected rows CONTROL + 9
Unhide selected rows CONTROL + SHIFT + Opening parenthesis ( ( )
Hide selected columns CONTROL + ZERO
Unhide selected columns CONTROL + SHIFT + Closing parenthesis ( ) )

Use function key shortcuts

Excel 2016 for Mac uses the function keys for common commands, including Copy and Paste. For quick access to these shortcuts, you can change your Apple system preferences so you don’t have to press the FN key every time you use a function key shortcut

NOTE: Changing system function key preferences affects how the function keys work for your Mac, not just Excel. After changing this setting, you can still perform the special features printed on a function key. Just press the FN key. For example, to use the F12 key to change your volume, you would press FN+F12.

If a function key doesn’t work as you expect it to, press the FN key in addition to the function key. If you don’t want to press the FN key each time, you can change your Apple system preferences:

 

Change function key preferences with the mouse

  1. On the Apple menu, press System Preferences.
  2. Select Keyboard.
  3. On the Keyboard tab, select the check box for Use all F1, F2, etc. keys as standard function keys.


The following table provides the function key shortcuts for Excel 2016 for Mac

To do this Press
Display the Help window F1
Edit the selected cell F2
Insert or edit a cell comment SHIFT + F2
Open the Save dialog OPTION + F2
Open the Formula Builder SHIFT +F3
Open the Define Name dialog COMMAND +F3
Close COMMAND +F4
Display the Go To dialog F5
Display the Find dialog SHIFT + F5
Move to the Search Sheet dialog CONTROL + F5
Check spelling F7
Open the thesaurus SHIFT + F7
or
CONTROL + OPTION + COMMAND + R
Extend the selection F8
Add to the selection SHIFT + F8
Display the Macro dialog OPTION +F8
Calculate all open workbooks F9
Calculate the active sheet SHIFT + F9
Minimize the active window CONTROL + F9
Display a contextual menu, or “right click” menu SHIFT + F10
Maximize or restore the active window CONTROL + F10
or
COMMAND + F10
Insert a new chart sheet* F11
Insert a new sheet* SHIFT + F11
Insert an Excel 4.0 macro sheet COMMAND + F11
Open Visual Basic OPTION + F11
Display the Save As dialog F12
Display the Open dialog COMMAND + F12

For more information, please visit the Excel help center.

Microsoft Excel – Windows Excel keyboard shortcuts and function keys

This article describes keyboard shortcuts, function keys, and some other common shortcut keys for Microsoft Excel for Windows. This includes the access keys that you can use for Ribbon commands.

Note:

  • These shortcuts refer to the US keyboard layout. Keys for other layouts might not correspond exactly to the keys on a US keyboard.
  • If a shortcut requires pressing two or more keys at the same time, this topic separates the keys with a plus sign (+). If you have to press one key immediately after another, the keys are separated by a comma (,).

This article describes keyboard shortcuts, function keys, and some other common shortcut keys for Excel 2016. This includes the access keys that you can use for ribbon commands. 

TIPS: 

Frequently used shortcuts

If you’re new to the Ribbon, the information in this section can help you understand the Ribbon’s keyboard shortcut model. The Ribbon comes with new shortcuts, called Key Tips, which you can make appear when you press the Alt key. The Ribbon groups related commands on tabs. For example, on the Home tab, the Number group includes the Number Format command.

This table lists the most frequently used shortcuts in Excel 2016.

To do this Press
Close a spreadsheet Ctrl+W
Open a spreadsheet Ctrl+O
Go to the Home tab Alt+H
Save a spreadsheet Ctrl+S
Copy Ctrl+C
Paste Ctrl+V
Undo Ctrl+Z
Remove cell contents Delete key
Choose a fill color Alt+H, H
Cut Ctrl+X
Go to Insert tab Alt+N
Bold Ctrl+B
Center align cell contents Alt+H, A, then C
Go to Page Layout tab Alt+P
Go to Data tab Alt+A
Go to View tab Alt+W
Open context menu Shift+F10, or

Context key

Add borders Alt+H, B
Delete column Alt+H,D, then C
Go to Formula tab Alt+M
Hide the selected rows Ctrl+9
Hide the selected columns Ctrl+0

Ribbon keyboard shortcuts

If you’re new to the ribbon, the information in this section can help you understand the ribbon’s keyboard shortcut model.

When you press the Alt key, letters appear in small images, called KeyTips, next to tabs and commands on the ribbon, as shown in the following image.

Key Tip badges appearing on ribbon

You can combine these letters with Alt to make shortcuts called Access Keys for ribbon commands. For example, Alt+H opens the Home tab, and Alt+Q goes to the Tell me box.

Press Alt again to see KeyTips for the commands on any tab.

Access keys for ribbon tabs

To go directly to a tab on the ribbon, press one of the following access keys:

To do this Press
Open the Tell me box on the Ribbon and type a search term for assistance or Help content. Alt+Q, and then enter the search term.
Open the File page and use Backstage view. Alt+F
Open the Home tab and format text and numbers and use the Find tool. Alt+H
Open the Insert tab and insert PivotTables, charts, add-ins, Sparklines, pictures, shapes, headers, or text boxes. Alt+N
Open the Page Layout tab and work with themes, page setup, scale, and alignment. Alt+P
Open the Formulas tab and insert, trace, and customize functions and calculations. Alt+M
Open the Data tab and connect to, sort, filter, analyze, and work with data. Alt+A
Open the Review tab and check spelling, add comments, and protect sheets and workbooks. Alt+R
Open the View tab and preview page breaks and layouts, show and hide gridlines and headings, set zoom magnification, manage windows and panes, and view macros. Alt+W

Work in the ribbon with the keyboard

To do this Press
Select the active tab of the ribbon, and activate the access keys. Alt or F10. To move to a different tab, use access keys or the arrow keys.
Move the focus to commands on the ribbon. Tab or Shift+Tab
Move down, up, left, or right, respectively, among the items on the Ribbon. The Down Arrow, Up Arrow, Left Arrow, or Right Arrow key
Activate a selected button. Spacebar or Enter
Open the list for a selected command. The Down Arrow key
Open the menu for a selected button. Alt+Down Arrow
When a menu or submenu is open, move to the next command. Down Arrow key
Expand or collapse the ribbon. Ctrl+F1
Open a context menu. Shift+F10

Or, on a Windows keyboard

Context key (between the right Alt and right Ctrl keys)

Move to the submenu when a main menu is open or selected. Left Arrow key

Excel keyboard shortcut reference

Navigate in cells: keyboard shortcuts

To do this Press
Move to the previous cell in a worksheet or the previous option in a dialog box. Shift+Tab
Move one cell up in a worksheet. Up Arrow key
Move one cell down in a worksheet. Down Arrow key
Move one cell left in a worksheet. Left Arrow key
Move one cell right in a worksheet. Right Arrow key
Move to the edge of the current data region in a worksheet. Ctrl+arrow key
Enter End mode, move to the next nonblank cell in the same column or row as the active cell, and turn off End mode. If the cells are blank, move to the last cell in the row or column. End, arrow key
Move to the last cell on a worksheet, to the lowest used row of the rightmost used column. Ctrl+End
Extend the selection of cells to the last used cell on the worksheet (lower-right corner). Ctrl+Shift+End
Move to the cell in the upper-left corner of the window when Scroll Lock is turned on. Home+Scroll Lock
Move to the beginning of a worksheet. Ctrl+Home
Move one screen down in a worksheet. Page Down
Move to the next sheet in a workbook. Ctrl+Page Down
Move one screen to the right in a worksheet. Alt+Page Down
Move one screen up in a worksheet. Page Up
Move one screen to the left in a worksheet. Alt+Page Up
Move to the previous sheet in a workbook. Ctrl+Page Up
Move one cell to the right in a worksheet. Or, in a protected worksheet, move between unlocked cells. Tab

Format in cells: keyboard shortcuts

To do this Press
Open the Format Cells dialog box. Ctrl+1
Format fonts in the Format Cells dialog box. Ctrl+Shift+F or Ctrl+Shift+P
Edit the active cell and put the insertion point at the end of its contents. Or, if editing is turned off for the cell, move the insertion point into the formula bar. If editing a formula, toggle Point mode off or on so you can use arrow keys to create a reference. F2
Add or edit a cell comment. Shift+F2
Open the Insert dialog to insert blank cells. Ctrl+Shift+Plus (+)
Open the Delete dialog box to delete selected cells. Ctrl+Minus (-)
Enter the current time. Ctrl+Shift+colon (:)
Enter the current date. Ctrl+semi-colon (;)
Switch between displaying cell values or formulas in the worksheet. Ctrl+grave accent (`)
Copy a formula from the cell above the active cell into the cell or the Formula Bar. Ctrl+apostrophe (‘)
Move the selected cells. Ctrl+X
Copy the selected cells. Ctrl+C
Paste content at the insertion point, replacing any selection. Ctrl+V
Open the Paste Special dialog box. Ctrl+Alt+V
Italicize text or remove italic formatting. Ctrl+I or Ctrl+3
Bold text or remove bold formatting. Ctrl+B or Ctrl+2
Underline text or remove the underline. Ctrl+U or Ctrl+4
Apply or remove strikethrough formatting. Ctrl+5
Switch between hiding objects, displaying objects, and displaying placeholders for objects. Ctrl+6
Apply an outline border to the selected cells. Ctrl+Shift+ampersand (&)
Remove the outline border from the selected cells. Ctrl+Shift+underline (_)
Display or hide the outline symbols. Ctrl+8
Use the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below. Ctrl+D
Apply the General number format. Ctrl+Shift+tilde (~)
Apply the Currency format with two decimal places (negative numbers in parentheses). Ctrl+Shift+dollar sign ($)
Apply the Percentage format with no decimal places. Ctrl+Shift+percent (%)
Apply the Scientific number format with two decimal places. Ctrl+Shift+caret (^)
Apply the Date format with the day, month, and year. Ctrl+Shift+number sign (#)
Apply the Time format with the hour and minute, and AM or PM. Ctrl+Shift+at sign (@)
Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative values. Ctrl+Shift+exclamation point (!)
Open the Insert hyperlink dialog. Ctrl+K
Check spelling in the active worksheet or selected range. F7
Display the Quick Analysis options for selected cells that contain data. Ctrl+Q
Display the Create Table dialog box. Ctrl+L or Ctrl+T

Make selections and perform actions: keyboard shortcuts

To do this Press
Select the entire worksheet. Ctrl+A or Ctrl+Shift+Spacebar
Select the current and next sheet in a workbook. Ctrl+Shift+Page Down
Select the current and previous sheet in a workbook. Ctrl+Shift+Page Up
Extend the selection of cells by one cell. Shift+arrow key
Extend the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, to the next nonblank cell. Ctrl+Shift+arrow key
Turn extend mode on and use the arrow keys to extend a selection. Press again to turn off. Turn extend mode on and use the arrow keys to extend a selection. Press again to turn off. F8
Add a non-adjacent cell or range to a selection of cells by using the arrow keys. Shift+F8
Start a new line in the same cell. Alt+Enter
Fill the selected cell range with the current entry. Ctrl+Enter
Complete a cell entry and select the cell above. Shift+Enter
Select an entire column in a worksheet. Ctrl+Spacebar
Select an entire row in a worksheet. Shift+Spacebar
Select all objects on a worksheet when an object is selected. Ctrl+Shift+Spacebar
Extend the selection of cells to the beginning of the worksheet. Ctrl+Shift+Home
Select the current region if the worksheet contains data. Press a second time to select the current region and its summary rows. Press a third time to select the entire worksheet. Ctrl+A or Ctrl+Shift+Spacebar
Select the current region around the active cell or select an entire PivotTable report. Ctrl+Shift+asterisk (*)
Select the first command on the menu when a menu or submenu is visible. Home
Repeat the last command or action, if possible. Ctrl+Y
Undo the last action. Ctrl+Z

Work with data, functions, and the formula bar: keyboard shortcuts

To do this Press
Select an entire PivotTable report. Ctrl+Shift+asterisk (*)
Edit the active cell and put the insertion point at the end of its contents. Or, if editing is turned off for the cell, move the insertion point into the formula bar. If editing a formula, toggle Point mode off or on so you can use arrow keys to create a reference. F2
Expand or collapse the formula bar. Ctrl+Shift+U
Cancel an entry in the cell or Formula Bar. Esc
Complete an entry in the formula bar and select the cell below. Enter
Move the cursor to the end of the text when in the formula bar. Ctrl+End
Select all text in the formula bar from the cursor position to the end. Ctrl+Shift+End
Calculate all worksheets in all open workbooks. F9
Calculate the active worksheet. Shift+F9
Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. Ctrl+Alt+F9
Check dependent formulas, and then calculate all cells in all open workbooks, including cells not marked as needing to be calculated. Ctrl+Alt+Shift+F9
Display the menu or message for an Error Checking button. Alt+Shift+F10
Display the Function Arguments dialog box when the insertion point is to the right of a function name in a formula. Ctrl+A
Insert argument names and parentheses when the insertion point is to the right of a function name in a formula. Ctrl+Shift+A
Invoke Flash Fill to automatically recognize patterns in adjacent columns and fill the current column Ctrl+E
Cycle through all combinations of absolute and relative references in a formula if a cell reference or range is selected. F4
Insert a function. Shift+F3
Copy the value from the cell above the active cell into the cell or the formula bar. Ctrl+Shift+straight quotation mark (“)
Create an embedded chart of the data in the current range. Alt+F1
Create a chart of the data in the current range in a separate Chart sheet. F11
Define a name to use in references. Alt+M, M, D
Paste a name from the Paste Name dialog box (if names have been defined in the workbook. F3
Move to the first field in the next record of a data form. Enter
Create, run, edit, or delete a macro. Alt+F8
Open the Microsoft Visual Basic For Applications Editor. Alt+F11

Function keys

Key Description
F1
  • F1 alone: displays the Excel Help task pane.
  • Ctrl+F1: displays or hides the ribbon.
  • Alt+F1: creates an embedded chart of the data in the current range.
  • Alt+Shift+F1: inserts a new worksheet.
F2
  • F2 alone: edit the active cell and put the insertion point at the end of its contents. Or, if editing is turned off for the cell, move the insertion point into the formula bar. If editing a formula, toggle Point mode off or on so you can use arrow keys to create a reference.
  • Shift+F2: adds or edits a cell comment.
  • Ctrl+F2: displays the print preview area on the Print tab in the Backstage view.
F3
  • F3 alone: displays the Paste Name dialog box. Available only if names have been defined in the workbook.
  • Shift+F3: displays the Insert Function dialog box.
F4
  • F4 alone: repeats the last command or action, if possible.When a cell reference or range is selected in a formula, F4 cycles through all the various combinations of absolute and relative references.
  • Ctrl+F4: closes the selected workbook window.
  • Alt+F4: closes Excel.
F5
  • F5 alone: displays the Go To dialog box.
  • Ctrl+F5: restores the window size of the selected workbook window.
F6
  • F6 alone: switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split , F6 includes the split panes when switching between panes and the ribbon area.
  • Shift+F6: switches between the worksheet, Zoom controls, task pane, and ribbon.
  • Ctrl+F6: switches to the next workbook window when more than one workbook window is open.
F7
  • F7 alone: Opens the Spelling dialog box to check spelling in the active worksheet or selected range.
  • Ctrl+F7: performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press Enter, or Esc to cancel.
  • F8 alone: turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection.
F8
  • Shift+F8: enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys.
  • Ctrl+F8: performs the Size command when a workbook is not maximized.
  • Alt+F8: displays the Macro dialog box to create, run, edit, or delete a macro.
F9
  • F9 alone: calculates all worksheets in all open workbooks.
  • Shift+F9: calculates the active worksheet.
  • Ctrl+Alt+F9: calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
  • Ctrl+Alt+Shift+F9: rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
  • Ctrl+F9: minimizes a workbook window to an icon.
F10
  • F10 alone: Turns key tips on or off. (Pressing Alt does the same thing.)
  • Shift+F10: displays the shortcut menu for a selected item.
  • Alt+Shift+F10: displays the menu or message for an Error Checking button.
  • Ctrl+F10: maximizes or restores the selected workbook window.
F11
  • F11 alone: Creates a chart of the data in the current range in a separate Chart sheet.
  • Shift+F11: inserts a new worksheet.
  • Alt+F11: opens the Microsoft Visual Basic For Applications Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
F12
  • F12 alone: displays the Save As dialog box.

Other useful shortcut keys

Key Description
Alt
  • Displays the Key Tips (new shortcuts) on the ribbon.

For example,

  • Alt, W, P switches the worksheet to Page Layout view.
  • Alt, W, L switches the worksheet to Normal view.
  • Alt, W, I switches the worksheet to Page Break Preview view.
Arrow Keys
  • Move one cell up, down, left, or right in a worksheet.
  • Ctrl+Arrow Key moves to the edge of the current data region in a worksheet.
  • Shift+Arrow Key extends the selection of cells by one cell.
  • Ctrl+Shift+Arrow Key extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.
  • Left Arrow or Right Arrow selects the tab to the left or right when the ribbon is selected. When a submenu is open or selected, these arrow keys switch between the main menu and the submenu. When a ribbon tab is selected, these keys navigate the tab buttons.
  • Down Arrow or Up Arrow selects the next or previous command when a menu or submenu is open. When a ribbon tab is selected, these keys navigate up or down the tab group.
  • In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
  • Down Arrow or Alt+Down Arrow opens a selected drop-down list.
Backspace
  • Deletes one character to the left in the Formula Bar.
  • Also clears the content of the active cell.
  • In cell editing mode, it deletes the character to the left of the insertion point.
Delete
  • Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
  • In cell editing mode, it deletes the character to the right of the insertion point.
End
  • End turns End mode on or off. In End mode, you can press an arrow key to move to the next nonblank cell in the same column or row as the active cell. End mode turns off automatically after pressing the arrow key. Make sure to press End again before pressing the next arrow key. End mode is shown in the status bar when it is on.
  • If the cells are blank, pressing End followed by an arrow key moves to the last cell in the row or column.
  • End also selects the last command on the menu when a menu or submenu is visible.
  • Ctrl+End moves to the last cell on a worksheet, to the lowest used row of the rightmost used column. If the cursor is in the formula bar, Ctrl+End moves the cursor to the end of the text.
  • Ctrl+Shift+End extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, Ctrl+Shift+End selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar.
Enter
  • Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
  • In a data form, it moves to the first field in the next record.
  • Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
  • In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).
  • Alt+Enter starts a new line in the same cell.
  • Ctrl+Enter fills the selected cell range with the current entry.
  • Shift+Enter completes a cell entry and selects the cell above.
Esc
  • Cancels an entry in the cell or Formula Bar.
  • Closes an open menu or submenu, dialog box, or message window.
  • It also closes full screen mode when this mode has been applied, and returns to normal screen mode to display the ribbon and status bar again.
Home
  • Moves to the beginning of a row in a worksheet.
  • Moves to the cell in the upper-left corner of the window when Scroll Lock is turned on.
  • Selects the first command on the menu when a menu or submenu is visible.
  • Ctrl+Home moves to the beginning of a worksheet.
  • Ctrl+Shift+Home extends the selection of cells to the beginning of the worksheet.
Page Down
  • Moves one screen down in a worksheet.
  • Alt+Page Down moves one screen to the right in a worksheet.
  • Ctrl+Page Down moves to the next sheet in a workbook.
  • Ctrl+Shift+Page Down selects the current and next sheet in a workbook.
Page Up
  • Moves one screen up in a worksheet.
  • Alt+Page Up moves one screen to the left in a worksheet.
  • Ctrl+Page Up moves to the previous sheet in a workbook.
  • Ctrl+Shift+Page Up selects the current and previous sheet in a workbook.
Spacebar
  • In a dialog box, performs the action for the selected button, or selects or clears a check box.
  • Ctrl+Spacebar selects an entire column in a worksheet.
  • Shift+Spacebar selects an entire row in a worksheet.
  • Ctrl+Shift+Spacebar selects the entire worksheet.
  • If the worksheet contains data, Ctrl+Shift+Spacebar selects the current region. Pressing Ctrl+Shift+Spacebar a second time selects the current region and its summary rows. Pressing Ctrl+Shift+Spacebar a third time selects the entire worksheet.
  • When an object is selected, Ctrl+Shift+Spacebar selects all objects on a worksheet.
  • Alt+Spacebar displays the Control menu for the Excel window.
Tab
  • Moves one cell to the right in a worksheet.
  • Moves between unlocked cells in a protected worksheet.
  • Moves to the next option or option group in a dialog box.
  • Shift+Tab moves to the previous cell in a worksheet or the previous option in a dialog box.
  • Ctrl+Tab switches to the next tab in dialog box.
  • Ctrl+Shift+Tab switches to the previous tab in a dialog box.

CTRL combination shortcut keys

Key Description
CTRL+PgUp Switches between worksheet tabs, from left-to-right.
CTRL+PgDn Switches between worksheet tabs, from right-to-left.
CTRL+SHIFT+( Unhides any hidden rows within the selection.
CTRL+SHIFT+) Unhides any hidden columns within the selection.
CTRL+SHIFT+& Applies the outline border to the selected cells.
CTRL+SHIFT_ Removes the outline border from the selected cells.
CTRL+SHIFT+~ Applies the General number format.
CTRL+SHIFT+$ Applies the Currency format with two decimal places (negative numbers in parentheses).
CTRL+SHIFT+% Applies the Percentage format with no decimal places.
CTRL+SHIFT+^ Applies the Exponential number format with two decimal places.
CTRL+SHIFT+# Applies the Date format with the day, month, and year.
CTRL+SHIFT+@ Applies the Time format with the hour and minute, and AM or PM.
CTRL+SHIFT+! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+SHIFT+* Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).

In a PivotTable, it selects the entire PivotTable report.

CTRL+SHIFT+: Enters the current time.
CTRL+SHIFT+” Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL+SHIFT+Plus (+) Displays the Insert dialog box to insert blank cells.
CTRL+Minus (-) Displays the Delete dialog box to delete the selected cells.
CTRL+; Enters the current date.
CTRL+` Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+’ Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+1 Displays the Format Cells dialog box.
CTRL+2 Applies or removes bold formatting.
CTRL+3 Applies or removes italic formatting.
CTRL+4 Applies or removes underlining.
CTRL+5 Applies or removes strikethrough.
CTRL+6 Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
CTRL+8 Displays or hides the outline symbols.
CTRL+9 Hides the selected rows.
CTRL+0 Hides the selected columns.
CTRL+A Selects the entire worksheet.

If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the current region and its summary rows. Pressing CTRL+A a third time selects the entire worksheet.

When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.

CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.

CTRL+B Applies or removes bold formatting.
CTRL+C Copies the selected cells.

CTRL+C followed by another CTRL+C displays the Clipboard.

CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F Displays the Find and Replace dialog box, with the Find tab selected.

SHIFT+F5 also displays this tab, while SHIFT+F4 repeats the last Find action.

CTRL+SHIFT+F opens the Format Cells dialog box with the Font tab selected.

CTRL+G Displays the Go To dialog box.

F5 also displays this dialog box.

CTRL+H Displays the Find and Replace dialog box, with the Replace tab selected.
CTRL+I Applies or removes italic formatting.
CTRL+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlinkdialog box for selected existing hyperlinks.
CTRL+N Creates a new, blank workbook.
CTRL+O Displays the Open dialog box to open or find a file.

CTRL+SHIFT+O selects all cells that contain comments.

CTRL+P Displays the Print dialog box.

CTRL+SHIFT+P opens the Format Cells dialog box with the Font tab selected.

CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S Saves the active file with its current file name, location, and file format.
CTRL+T Displays the Create Table dialog box.
CTRL+U Applies or removes underlining.

CTRL+SHIFT+U switches between expanding and collapsing of the formula bar.

CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents.

CTRL+ALT+V displays the Paste Special dialog box. Available only after you have cut or copied an object, text, or cell contents on a worksheet or in another program.

CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL+Y Repeats the last command or action, if possible.
CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.

Function keys

Key Description
F1 Displays the Microsoft Office Excel Help task pane.

CTRL+F1 displays or hides the Ribbon, a component of the Microsoft Office Fluent user interface.

ALT+F1 creates a chart of the data in the current range.

ALT+SHIFT+F1 inserts a new worksheet.

F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.

SHIFT+F2 adds or edits a cell comment.

CTRL+F2 displays the Print Preview window.

F3 Displays the Paste Name dialog box.

SHIFT+F3 displays the Insert Function dialog box.

F4 Repeats the last command or action, if possible.

When a cell reference or range is selected in a formula, F4 cycles through the various combinations of absolute and relative references.

CTRL+F4 closes the selected workbook window.

F5 Displays the Go To dialog box.

CTRL+F5 restores the window size of the selected workbook window.

F6 Switches between the worksheet, Ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This WindowFreeze PanesSplit Window command), F6 includes the split panes when switching between panes and the Ribbon area.

SHIFT+F6 switches between the worksheet, Zoom controls, task pane, and Ribbon.

CTRL+F6 switches to the next workbook window when more than one workbook window is open.

F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.

CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ENTER, or ESC to cancel.

F8 Turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection.

SHIFT+F8 enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys.

CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.

ALT+F8 displays the Macro dialog box to create, run, edit, or delete a macro.

F9 Calculates all worksheets in all open workbooks.

SHIFT+F9 calculates the active worksheet.

CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

CTRL+F9 minimizes a workbook window to an icon.

F10 Turns key tips on or off.

SHIFT+F10 displays the shortcut menu for a selected item.

ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.

CTRL+F10 maximizes or restores the selected workbook window.

F11 Creates a chart of the data in the current range.

SHIFT+F11 inserts a new worksheet.

ALT+F11 opens the Microsoft Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).

F12 Displays the Save As dialog box.

Other useful shortcuts

Key Description
ARROW KEYS Move one cell up, down, left, or right in a worksheet.

CTRL+ARROW KEY moves to the edge of the current data region in a worksheet.

SHIFT+ARROW KEY extends the selection of cells by one cell.

CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.

LEFT ARROW or RIGHT ARROW selects the tab to the left or right when the Ribbon is selected. When a submenu is open or selected, these arrow keys switch between the main menu and the submenu. When a Ribbon tab is selected, these keys navigate the tab buttons.

DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open. When a Ribbon tab is selected, these keys navigate up or down the tab group.

In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.

DOWN ARROW or ALT+DOWN ARROW opens a selected drop-down list.

BACKSPACE Deletes one character to the left in the Formula Bar.

Also clears the content of the active cell.

In cell editing mode, it deletes the character to the left of the insertion point.

DELETE Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.

In cell editing mode, it deletes the character to the right of the insertion point.

END Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.

Also selects the last command on the menu when a menu or submenu is visible.

CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column. If the cursor is in the formula bar, CTRL+END moves the cursor to the end of the text.

CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar, CTRL+SHIFT+END selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula bar.

ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).

In a data form, it moves to the first field in the next record.

Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.

In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).

ALT+ENTER starts a new line in the same cell.

CTRL+ENTER fills the selected cell range with the current entry.

SHIFT+ENTER completes a cell entry and selects the cell above.

ESC Cancels an entry in the cell or Formula Bar.

Closes an open menu or submenu, dialog box, or message window.

It also closes full screen mode when this mode has been applied, and returns to normal screen mode to display the Ribbon and status bar again.

HOME Moves to the beginning of a row in a worksheet.

Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.

Selects the first command on the menu when a menu or submenu is visible.

CTRL+HOME moves to the beginning of a worksheet.

CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.

PAGE DOWN Moves one screen down in a worksheet.

ALT+PAGE DOWN moves one screen to the right in a worksheet.

CTRL+PAGE DOWN moves to the next sheet in a workbook.

CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.

PAGE UP Moves one screen up in a worksheet.

ALT+PAGE UP moves one screen to the left in a worksheet.

CTRL+PAGE UP moves to the previous sheet in a workbook.

CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.

SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.

CTRL+SPACEBAR selects an entire column in a worksheet.

SHIFT+SPACEBAR selects an entire row in a worksheet.

CTRL+SHIFT+SPACEBAR selects the entire worksheet.

  • If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the current region and its summary rows. Pressing CTRL+SHIFT+SPACEBAR a third time selects the entire worksheet.
  • When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.

ALT+SPACEBAR displays the Control menu for the Microsoft Office Excel window.

TAB Moves one cell to the right in a worksheet.

Moves between unlocked cells in a protected worksheet.

Moves to the next option or option group in a dialog box.

SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.

CTRL+TAB switches to the next tab in dialog box.

CTRL+SHIFT+TAB switches to the previous tab in a dialog box.

For more information, please visit the Excel help center.