Looking for more? Try chatting with the Exceljet Chatbot.

Keyboard shortcut

Toggle pivot table field checkbox

This shortcut will toggle check and uncheck the checkbox for fields listed in the pivot table field list. Use to add or remove a field from a pivot table. On Windows, you can use the spacebar to toggle items that appear in row and column label filters as well.Read more

Keyboard shortcut

Group pivot table items

This shortcut will group selected pivot table items. If you want to group a field automatically by date or number, you only need to select one item in the field before using this shortcut.Read more

Keyboard shortcut

Ungroup pivot table items

This shortcut will ungroup selected pivot table items. If you want to ungroup a field, you only need to select one item in the field before using this shortcut.Read more

Keyboard shortcut

Hide pivot table item

This shortcut is the same as filtering a pivot table field to exclude an item in the field. Check the filter afterwards to confirm how the shortcut works.Read more

Video

How Excel Table ranges work

One of the most useful features of Excel Tables is that they create a dynamic range. In this video, we'll take a closer look at how table ranges work. ...Read more
Video

How to build a simple summary table

Pivot Tables are fantastic tools for summarizing data, but you can also use formulas to build your own summaries using functions like COUNTIF and SUMIF. See how in this 3 minute video.Read more
Formula

Average salary by department

In this example, the goal is to create a formula that calculates an average salary by department, where data is an Excel Table in the range B5:D16. The solution shown requires three general steps:

  1. Create an Excel
    2. ...Read more
Formula

Dynamic two-way average

In this example, the goal is to create a formula that performs a dynamic two-way average of all age and gender combinations in the range B5:D16 . The solution shown requires four general steps:

  1. Create an Excel Table called data
    2. ...Read more
Formula

Sum by month in columns

In this example, the goal is to construct a formula that will subtotal the amounts in column D by client and month as seen in the range G5:I8. A big part of the problem is to set up the proper references so that the formula can be entered once, and copied throughout G5:I8. The solution...Read more

Formula

Average by group

In this example, the goal is to create a formula that calculates an average by group, using the group names in column C. The solution shown requires three general steps:

  1. Create an Excel Table called data
  2. List
    3. ...Read more
Formula

Dynamic two-way count

In this example, the goal is to create a formula that performs a dynamic two-way count of all color and size combinations in the range B5:D16. The solution shown requires four general steps:

  1. Create an Excel Table called
    2. ...Read more
Formula

Dynamic two-way sum

In this example, the goal is to create a formula that performs a dynamic two-way sum of all City and Size combinations in the range B5:D17 . The solution shown requires four basic steps:

  1. Create an Excel Table called data
    2. ...Read more
Keyboard shortcut

Select table

This shortcut selects the data area of a table, when the cursor is in an Excel table. Behavior changes as the shortcut is used more than once:

  1. First time: selects table data
  2. Second time: selects table data + table header
  3. Third time: selects entire worksheet
    4. ...Read more
Formula

Summary count with COUNTIF

In this example, the goal is to return a count for each color that appears in column C, using the color values already in column E as criteria. When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by...Read more

Formula

Count if two criteria match

In this example, the goal is to count orders where the color is "blue" and the quantity is greater than 15. All data is in the range B5:B15. There are two primary ways to solve this problem, one with the COUNTIFS function, the other with the SUMPRODUCT function....Read more

Formula

Count numbers by range

In this example, the goal is to count ages in column C according to the brackets defined in columns E and F. All data is in an Excel Table named data defined in the range B5:C16. A simple way to solve this problem is with the COUNTIFS...Read more

Formula

Summary count by month with COUNTIFS

In this example, we have a list of 100 issues in Columns B to D. Each issue has a date and priority. We are also using the named range dates for C5:C104 and priorities for D5:D105. Starting in column F, we have a summary table...Read more

Keyboard shortcut

Go to next workbook

Move to next / previous workbook window.  This shortcut "cycles" through open workbooks each time it is used. To reverse direction, add the shift key.

On a Mac, check options at System Preferences > Keyboard > Shortcuts > Keyboard to confirm this shortcut is enabled.Read more

Function

SUMIFS Function

The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria. To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.Read more

Formula

Sum by quarter

In this example, the goal is to sum the amounts in column C by quarter in column G. Column D is a helper column, and the formula to calculate quarters from the dates in column B is explained below. All data is in an Excel...Read more

Formula

Sum by week

In this example, the goal is to sum the amounts in column C by week, using the dates in the range E5:E10 which are all Mondays. All data is in an Excel Table named data in the range B5:C16. This problem can be solved in a straightforward way with the...Read more

Pages