Looking for more? Try chatting with the Exceljet Chatbot.
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
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
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
To create a pivot table that shows the last 7 days of data (or, more generally, the last n days) you can add a helper column to the source data with a formula to flag records in the last 7 days, then use the helper column to filter the...Read more
A pivot table is an easy way to count blank values in a data set. In the example shown, the source data is a list of 50 employees, and some employees are not assigned to a department. The Pivot Table is configured to group out data by department, and automatically creates a category called "(...Read more
In this example, a pivot table is used to show the year-over-year change in sales across 4 categories. Change can be displayed as the numeric difference (this example) or as a percentage.
The pivot table uses all three fields in the source data: Date, Sales,...Read more
In this example, a pivot table shows the year-over-year variance in sales for each month of the year. Change can be displayed as the numeric difference or as a percentage (this example).
The pivot table uses all two of the three fields in the source data: Date, and...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 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
In the example shown, a pivot table is used to show the month-over-month variance in sales for each month of a given year. The variance is displayed both as an absolute value and also as a percentage. The year is selected by using a global filter.
The source...Read more
To display data in categories with a count and percentage breakdown, you can use a pivot table. In the example shown, the field "Last" has been added as a value field twice – once to show count, once to show percentage. The pivot table shows the count of employees in each department along with a...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 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
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
Excel Tables offer an easy way to create dynamic ranges that adjust when data changes. This makes tables perfect for pivot tables, charts, and dashboards that need to show the latest data. This course covers the key benefits of tables, including a detailed review of structured references, the...Read more
In this example, the goal is to build a simple summary count table with a formula. Once created, the summary table should automatically update to show new values and counts when data changes. The article below walks through several options, from simple to very advanced. The more advanced options...Read more
The Excel GETPIVOTDATA function can retrieve specific data from a pivot table by name based on the structure, instead of cell references.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
