Looking for more? Try chatting with the Exceljet Chatbot.
When a filter is applied to a Pivot Table, you may see rows or columns disappear. This is because pivot tables, by default, display only items that contain data. In the example shown, a filter has been applied to exclude the East region. Normally the Blue column would disappear, because there...Read more
Pivot tables provide a built-in ranking feature, and can rank smallest to largest or largest to smallest. In the example shown, a pivot table is used to group and rank sales by item. The pivot table is also sorted by rank so that the top sales items appear first.
...Read more
Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to count colors per month for data that covers a 6-month period. The count displayed represents the number of records per month for each color.
...Read more
Pivot tables can group dates by year, month, and quarter. This comes in handy if you want to count birthdays that occur in each month, while ignoring the year. In the example shown, there are 100 names and birthdays in the source data. A pivot table is used to count the birthdays that occur in...Read more
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:
To apply conditional formatting to a pivot table, create a new conditional formatting rule and pay particular attention to the "apply rule to" settings as described below. In the example shown, there are two rules applied. The green shows the top 5 values using a rule like this:
...Read more
Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to count colors per year. This is the number of records that occur for each color in a given year.
The source data contains three fields: Date,...Read more
Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to summarize sales by year and quarter. Once the date field is grouped into years and quarters, the grouping fields can be dragged into separate areas, as seen in the...Read more
Pivot tables are an easy way to quickly count unique values in a data set, and can easily be adapted to perform a two-way count. In the example shown above, a pivot table is used to count unique combinations of color and size, based on data in the range B5:D16, defined as an...Read more
Pivot tables are an easy way to quickly sum unique values in a data set, and can easily be adapted to perform a two-way sum. In the example shown above, a pivot table is used to sum The Qty field for unique combinations of City and Size, based on data in the range B5:D17, defined as an...Read more
Pivot tables have a built-in feature to allow manual grouping. In the example shown, a pivot table is used to group colors into two groups: Original and New. Notice these groups do not appear anywhere in the source data.
The source data contains three fields:...Read more
Pivot tables make it easy to quickly sum values in various ways. In the example shown, a pivot table is used to sum amounts by color.
The pivot table shown is based on two fields: Color and Amount. The Color field is...Read more
Pivot tables make it easy to count values in a data set. One way this feature can be used is to display duplicates. In the example shown, a pivot table is used to show duplicate cities in an Excel Table that contains more than 250 rows.
The data contains 263...Read more
Pivot tables are an easy way to quickly average unique values in a data set, and can easily be adapted to perform a two-way average. In the example shown above, a pivot table is used to average Ratings for unique combinations of Age and Gender, based on data in the range B5:D16, defined as an...Read more
Standard Pivot Tables have a simple feature for creating calculated fields. You can think of a calculated field as a virtual column in the source data. A calculated field will appear in the field list window, but will not take up space in the source data. In the example shown, a calculated field...Read more
Standard Pivot Tables have a simple feature for creating calculated items. You can think of a calculated item as "virtual rows" in the source data. A calculated item will not appear in the field list window. Instead, it will appear as an item in the field for which it is...Read more
This shortcut will apply the Currency format with two decimal places. Excel offers many types of number formatting.Read more
The delete key on a Mac deletes to the left.Read more
While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again:
A1 --> $A$1 --> A$1-- > $A1-- > A1
This is much faster and easier than typing the $ character manually.
To convert an existing...Read more
Move to the next workbook window. This shortcut "cycles" through open Excel workbooks in a "forward" direction.
On Windows, you can also use Ctrl + F6 to move to the next workbook and Shift + Ctrl + F6 to move to the previous open workbook.
On a Mac, the...Read more
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
Use this shortcut to display the Paste Special dialog box. Paste Special is the gateway into many powerful operations, including paste Values, which you probably use every day. Note that this shortcut only works when data has been copied to the clipboard.
On Windows, once you have the...Read more
If the active cell is empty, Excel will extend the selection to the first non-empty cell to the right. If the active cell is non-empty, Excel will extend the selection on the last non-empty cell to the right. On a Mac, the command key (⌘) can be used instead of the control key.Read more
The first time you use this shortcut, Excel will select the current region around the active cell, if one can be detected, otherwise the whole sheet is selected. The second time you use the shortcut (consecutively), Excel will select the entire worksheet.Read more
Pressing escape while editing an a cell will abort the operation without changing anything.Read more
This shortcut toggles bolding on and off for the current selection.
On Windows, Ctrl 2 also toggles bolding.Read more
If you select just the cells you'd like to sum, SUM formula(s) are placed below the selection. If you select empty cells to the right and Excel will place SUM to the right and SUM horizontally. If you select empty cells below and to the right, Excel will add SUM formulas to cells below and...Read more
This shortcut will delete the contents of selected cells, leaving formatting intact.
On the Mac, Control-B also deletes cell contents of one or more cells.Read more
