Looking for more? Try chatting with the Exceljet Chatbot.

Video

How to use pivot table layouts

One of the more confusing aspects of pivot tables is layout options. When you create a pivot table, you have your choice of three layouts. In this video, we'll take a look at each layout. ...Read more
Video

How to group a pivot table by day of week

If you've used pivot tables at all, you understand the addictive power they give you for grouping dated information. You can group by year, by month, by quarter, and even by day and hour. But if you want to group by something like day of week, you'll need to do a little prep work first...Read more
Formula

Filter and exclude columns

In this example, the goal is to use a single formula to extract high-value projects and list them in a simple table. We also want to remove unnecessary columns to create a clean, uncluttered view. The solutions explained below are based on a combination of several functions in Excel, including...Read more

Video

What is a pivot chart

In this video, we'll introduce Pivot Charts. Unlike normal charts, Pivot charts can be used to plot data with hundreds or thousands of rows...Read more
Pivot Table

Pivot table running total

Pivot tables have a built-in feature to calculate running totals. In the example shown, a pivot table is used group data by month and show both the monthly total and running total over a 6-month period.

Fields

The source data contains three fields: Date,...Read more

Function

SUBTOTAL Function

The Excel SUBTOTAL function is designed to run a given calculation on a range of cells while ignoring cells that should not be included. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see complete list below), and SUBTOTAL function can either include or exclude values in hidden...Read more

Pivot Table

Pivot table basic count

Pivot tables are an easy way to quickly count values in a data set. In the example shown, a pivot table is used to count the names associated with each color.

Fields

The pivot table shown is based on two fields: Name and Color. The Color field is configured as a row field, and...Read more

Pivot Table

Pivot table list unique values

To extract a list of unique values from a data set, you can use a pivot table. In the example shown, the color field has been added as a row field. The resulting pivot table (in column D) is a one-column list of unique color values.

Data

The data in this pivot tables comes from...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

Article

Alternatives to Dynamic Array Functions

Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.Read more

Keyboard shortcut

Select entire column

This shortcut will select the entire column of the current selection. If the current selection spans multiple columns, all columns that intersect the current selection will be selected.Read more

Pivot Table

Pivot table group by age

Pivot tables have a built-in feature to group numbers into buckets at a given interval. In the example shown, a pivot table is used to group a list of 300 names into age brackets separated by 10 years. This numeric grouping is fully automatic.

Fields

The source data contains...Read more

Pivot Table

Pivot table issue count by priority

Pivot tables have a feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to summarize support issues by month and by priority. Each row in the pivot table lists the count of issues recorded in a given month by priority (A, B, C). The Total columns shows...Read more

Pages