These texts, likely from a course on advanced pivot tables, provide a comprehensive guide to working with pivot tables in Excel. The content covers essential foundational skills, starting with creating a basic pivot table from raw data, ensuring the data is properly formatted and cleaned, and placing it into a table for easier management. It then moves into more advanced techniques, including importing data from external sources like text files and Access databases, demonstrating how to consolidate data from multiple sources, and showing various ways to group data within a pivot table by text, numbers, and dates. Finally, the texts explore formatting options for pivot tables, including applying built-in and custom styles, and handling error values and empty cells to present data clearly.

Fundamentals of Excel Pivot Tables

Based on the provided sources, here is a discussion of pivot table basics:

Pivot tables are described as the single most useful tool available in Excel for analyzing data. They are helpful for analyzing data in different ways, such as seeing the total sales accumulated by managers, sales in each category, sales by store, or sales between certain dates. The sources mention a recap of basic pivot table skills early in the course for those who haven’t used them recently or are not overly familiar with creating them from scratch.

Before creating a pivot table, it’s recommended to start with clean data. Cleaning data involves ensuring consistency and the absence of anomalies, such as blank rows, blank cells, inconsistent case, duplicates, and ensuring everything is formatted correctly. The sources also emphasize the importance of putting your data into a regular Excel table before creating a pivot table. This can be done by selecting the data and using Control + T, or by going to the Home ribbon, Styles group, and selecting “Format as Table”. When data is in a table, the “Table Design” contextual ribbon appears when clicked within the data. Another indicator is the presence of filter buttons at the top of each column. It’s also recommended to name your table for easier reading and understanding. Naming a table involves going to the Table Design ribbon, Properties group, and entering a name (without spaces, using underscores if needed), remembering to hit Enter. Putting data into a table also makes it easier to update pivot tables later when new data is added, as the table automatically expands to accommodate new rows.

To create a pivot table from scratch, make sure you are clicked within your data. You can use the “Summarize with PivotTables” option on the Table Design ribbon or go to the Insert ribbon and select the “PivotTable” button in the Tables group. Clicking either option opens a dialog box.

In this dialog box, you need to:

1. Choose the data you want to analyze. Excel often intuitively picks up the table name or range you are clicked within. You can also choose to use an external data source.
2. Choose where to place the pivot table report. It is generally suggested to keep your raw data separate from your pivot tables, so placing it on a new worksheet is recommended. You can rename the new sheet to something meaningful like “Pivot Table”.
3. Click OK.

Once the pivot table is created, you will see an empty pivot table report area on the left and the PivotTable Fields pane on the right. If the pane is not visible, ensure you are clicked within the pivot table report area, or go to the PivotTable Analyze ribbon, Show group, and click “Field List”.

The PivotTable Fields pane lists all the column headings from your source data. Below the list of fields are four areas: Filters, Columns, Rows, and Values.

The core basic operation of building a pivot table is dragging any of these fields into any of these four areas.

• Values: Fields dragged here are typically numeric and are used for calculations like sum, count, average, etc..
• Rows: Fields dragged here display their unique values as rows in the pivot table.
• Columns: Fields dragged here display their unique values as columns in the pivot table.
• Filters: Fields dragged here create a filter above the pivot table, allowing you to filter the entire report by selecting specific items from that field.

Building a basic pivot table often involves some trial and error depending on the information you want to extract. For example:

• To see total sales broken down by manager, drag “Sales” to Values and “Manager” to Rows.
• To see total sales by category, drag “Sales” to Values and “Category” to Rows.
• Dragging a field like “Manager” or “Product” between Rows and Columns changes the layout and how the data is presented.
• Dragging “Category” to Filters allows you to filter the sales data shown in the report by selected categories.
• Combining fields in Rows and Columns (e.g., Towns in Rows, Categories in Columns, Sales in Values) creates a cross-tabulated report.

The sources also mention the Recommended Pivot Tables option on the Insert ribbon, which analyzes your data and suggests potential pivot table layouts based on what might be useful. This can be a quick way to get a starting point, pre-populating the pivot table fields in the appropriate areas. However, this option cannot be used when combining data from multiple tables; in that case, you must use the standard “PivotTable” option and select the “Add this data to the Data Model” checkbox.

You can have more than one field in each area. When multiple fields are in the Rows or Columns areas, their order determines how the data is organized (e.g., organized by country first, then product, or product first, then country).

In summary, the basics involve preparing your data by cleaning it and putting it into a named Excel table, creating the pivot table using the Insert or Table Design ribbon, choosing the data source and location, and then dragging fields from the PivotTable Fields pane into the Rows, Columns, Values, and Filters areas to analyze and summarize your data.

Importing External Data for Pivot Tables

Importing data is a fundamental step when the information you need to analyze with a pivot table is not already in your current Excel workbook. The sources discuss various methods and considerations for bringing external data into Excel so it can be used effectively in pivot tables.

The primary location within Excel for accessing data import tools is the Data ribbon, specifically within the Get & Transform Data group. While the options available might differ slightly depending on your version of Excel, this is where you’ll find utilities for importing data from numerous sources.

The sources detail importing data from two main types of external sources:

1. Text Files (like .txt or .csv):

• One method is using the Get & Transform Data tool from the Data ribbon and selecting “From Text/CSV”. This opens a preview window where Excel attempts to detect the delimiter (the character separating columns, such as a tab, comma, or semicolon) and data types. You can change the delimiter if needed. From here, you can either “Load” the data directly or “Transform Data” using the Power Query Editor.
• The Transform Data option is highlighted as a way to clean up data as part of the import process. In the Power Query Editor, you can check and correct data types (e.g., ensuring numbers are formatted as currency or dates are recognized as dates) and remove columns that are not needed for your analysis. Once satisfied, you can use “Close & Load” to import the data into an Excel table or “Close & Load To” to load it directly into a pivot table report.
• Another way to import a text file is by opening it directly through the File menu. This often triggers the Text Import Wizard, which guides you through steps like defining the delimiter and setting column data formats. If you use the wizard or simply open a file, cleaning steps like correcting case, splitting columns, removing duplicates, and applying correct number formatting need to be performed after the data is in the worksheet using standard Excel tools. After cleaning, it’s recommended to put this data into a regular Excel table before creating a pivot table.

1. Databases (like Microsoft Access):

• To import from a database, you again use the Get & Transform Data group on the Data ribbon. Click the “Get Data” drop-down, select “From Database,” and then choose the relevant database type, such as “From Microsoft Access Database”.
• You browse and select the database file, and Excel will connect and display the tables contained within it. You then select the specific table you want to import.
• Similar to text files, you have the option to “Load” or “Transform Data”. Using “Transform Data” opens the Power Query Editor, allowing you to refine the data before importing, such as removing columns that are not relevant to your pivot table.
• After transforming, the “Close & Load To” option can be used to directly import the cleaned data into a PivotTable Report on a new worksheet.

Regardless of how the data is imported, the sources strongly emphasize the importance of starting with or creating clean data. This means ensuring consistency, formatting data correctly, and removing anomalies like blank rows, blank cells, inconsistent casing, or duplicate entries. Cleaning can be done during the import process using Power Query or afterward using various Excel functions and tools.

Furthermore, after importing data into a worksheet (if not loaded directly into a pivot table), putting the data into a regular Excel table and naming it is recommended. This makes the data easier to reference, understand, and is particularly beneficial because a table automatically expands when new rows are added, making it much easier to update pivot tables built upon that data later on using the refresh function.

A more advanced scenario discussed is consolidating data from multiple tables into a single pivot table. This is necessary when your data is spread across different sets of information that need to be linked for combined analysis.

• Each set of data must first be placed into a regular Excel table and named.
• The tables must share a common field (referred to as a “key” or “primary key”) that logically links the data between them, like an “Order ID” shared across customer, order, and payment information.
• To create a pivot table from multiple tables, you must use the standard “PivotTable” option on the Insert ribbon and select “Add this data to the Data Model” in the creation dialog box. The “Recommended Pivot Tables” option cannot be used for this.
• Once the pivot table is created, you will see fields from the initial table in the PivotTable Fields pane but can click “All” to view fields from all imported tables.
• The crucial next step is to create relationships between these tables based on their common key field. This is done via the PivotTable Analyze ribbon, using the “Relationships” button. By defining these links (e.g., linking the Order ID field in one table to the Order ID field in another), you enable the pivot table to draw data from different sources correctly.
• After relationships are established, you can freely drag fields from any of the linked tables into the different areas of the pivot table to perform your analysis.

In essence, importing data involves using the tools on the Data ribbon to bring external information into Excel, potentially cleaning and transforming it using Power Query, ensuring it is in a clean Excel table format, and for analyzing multiple sources, creating relationships between the tables via the Data Model.

Essential Data Cleaning for Pivot Tables

Data cleaning and preparation are highlighted as absolutely crucial steps before analyzing data, particularly with pivot tables. The primary reason for this is that if your data is not clean, you might end up with inaccurate or misleading results.

Clean data is described as data that is consistent and free from anomalies. This includes ensuring there are:

• No blank rows or blank cells.
• No inconsistent casing (e.g., some text is all uppercase, some proper case).
• No duplicate entries.
• All data is formatted correctly, such as numbers, currencies, and dates.

Cleaning can be performed at different stages. If you are importing data using the “Get & Transform Data” tools, you can utilize the Power Query Editor to clean and transform data as part of the import process. Alternatively, if you open a file directly or data is already in Excel, you can clean it afterwards using standard Excel tools.

Here are some specific techniques and tools for cleaning data mentioned in the sources:

• Checking and Correcting Data Types: When importing with Get & Transform Data, Excel attempts to detect data types, but you should verify and correct them in the Power Query Editor (e.g., changing numbers to currency or dates). If opening a file directly using the Text Import Wizard, you can set some formats, but often you need to correct them after import using the Home ribbon’s Number group. For values in a pivot table, number formatting is best done via Value Field Settings > Number Format to ensure consistency across the entire pivot table. Custom number formatting can be used to control how positive, negative, and zero values appear, including adding currency symbols, colors (like red or blue for negatives), or text (like “no data” for zeros).
• Handling Blank Rows and Cells: Blank rows can be efficiently removed by selecting all columns, going to Find & Select > Go To Special > Blanks, and then using the Delete Sheet Rows option. For blank cells, you can select them using the same “Go To Special > Blanks” method and then enter a value (like 0) followed by Control + Enter to fill all selected blank cells at once. Pivot table options also allow you to specify what to show for empty cells (e.g., 0 or custom text).
• Ensuring Consistent Case: You can use the PROPER function in a helper column to convert text to proper case. After using the function, it’s recommended to copy the helper column and paste values over the original data to replace the formulas with the cleaned text.
• Removing Duplicates: Excel has a dedicated Remove Duplicates tool on the Data ribbon in the Data Tools group. You can select the columns Excel should check for duplicate information before removing entire rows that match across the selected columns.
• Correcting Text Inconsistencies: The Find and Replace feature (Home ribbon > Find & Select, or Control + H) is useful for replacing inconsistent abbreviations or spellings with a standard version (e.g., replacing “mktg” with “marketing”).
• Handling Non-Printable Characters, Line Breaks, and Erroneous Spaces: Text functions like CLEAN (removes non-printable characters and manual line breaks) and TRIM (removes excess spaces) can be used. These functions can even be combined with other functions like PROPER within a single formula in a helper column to address multiple issues at once. Again, pasting values over the original data is recommended after using formulas.
• Splitting Data in Columns: The Flash Fill tool (Data ribbon > Data Tools group, or Control + E) is a quick way to split combined text, like separating a full name into first and last names, by recognizing a pattern from the first few manually entered examples.
• Handling Error Values: Pivot table options allow you to specify what to display for error values (e.g., custom text like “no data” or a value like 0) instead of showing the raw error (like #N/A).

After the data has been cleaned, the final and critically important step before creating a pivot table is to put the data into a regular Excel table. This can be done by selecting the data and using Control + T or by using the “Format as Table” option on the Home ribbon. Putting data into a table provides several benefits:

• It automatically adds filter buttons to column headers, making sorting and filtering easier.
• It creates a Table Design contextual ribbon with tools specific to tables.
• It’s recommended to name your table from the Table Design ribbon > Properties group. Table names (like sales_data) are easier to read and understand than cell ranges when creating pivot tables.
• Crucially for pivot tables, when you add new data (rows) to the bottom of a table, the table automatically expands to include the new data. This makes updating pivot tables built on that table much simpler, as you only need to use the Refresh function on the PivotTable Analyze ribbon to incorporate the new data. If the data wasn’t in a table, you would have to manually change the pivot table’s data source to include the new rows, which takes much longer.

In summary, thorough data cleaning and preparation are essential for accurate pivot table analysis, involving various techniques to address inconsistencies, errors, and formatting issues, and culminating in placing the cleaned data into a named Excel table for ease of use and future updates.

Creating Excel Pivot Tables from Single or Multiple Tables

Creating pivot tables is the primary goal after you have prepared and imported your data, as discussed previously. Pivot tables are considered the single most useful tool in Excel for analyzing data. This course is designed to guide you through utilizing the pivot table options to create meaningful analysis.

Before you begin creating a pivot table, it is crucial that your data is clean and, importantly, placed within a regular Excel table. As we’ve discussed, clean data is consistent and free from anomalies like blank rows, blank cells, inconsistent casing, or duplicates, and everything is formatted correctly. Putting your data into a regular table (Control + T or Home ribbon > Format as Table) is a vital final step. Naming your table (Table Design ribbon > Properties group) is also highly recommended for clarity, making the data easier to read and understand. A key benefit of using a table for pivot tables is that it automatically expands to include new data added to the bottom, making it simple to refresh your pivot table to incorporate the new information later.

There are a few different ways to initiate the process of creating a pivot table from your prepared data:

1. Using the Table Design Ribbon: If your data is in an Excel table and you are clicked inside it, you can use the “Summarize with PivotTable” option found on the Table Design contextual ribbon.
2. Using the Insert Ribbon: A more standard method is to go to the Insert ribbon and click the “PivotTable” button, located in the Tables group. This is the first option in that group.
3. Using Recommended PivotTables: Excel offers a “Recommended PivotTables” option on the Insert ribbon, right next to the standard “PivotTable” button. This feature analyzes your data and suggests potential pivot table layouts that might be useful, such as summing profit by country or month. Choosing one of these suggested options can create a pre-populated pivot table very quickly. However, this method cannot be used if you need to analyze data from multiple tables simultaneously.

Regardless of whether you use the Table Design or Insert ribbon’s standard “PivotTable” option, clicking it will open the “Create PivotTable” dialog box. Here, you need to make two main choices:

• Choose the data that you want to analyze: If you were clicked inside a named Excel table when you opened the dialog, Excel will intuitively select that table name as the data source. You can also choose to use an external data source.
• Choose where you want the PivotTable Report to be placed: The recommendation is always to place the pivot table on a new worksheet to keep your raw data separate. You can also choose an existing worksheet and specify the location.

Clicking “OK” (after specifying data and location) will create a new worksheet (or navigate you to the chosen location) containing a blank pivot table report on the left side. On the right side, you will see the PivotTable Fields pane. If this pane is not visible, ensure you are clicked within the blank pivot table report area. If it still doesn’t appear, it might have been accidentally closed; you can get it back by going to the PivotTable Analyze ribbon, clicking “Field List” in the Show group.

The PivotTable Fields pane is essential for building your pivot table. It lists all the column headings from your data source as available fields. Below the field list, there are four distinct areas:

• Filters: Fields placed here allow you to filter the entire pivot table report.
• Columns: Fields dragged here become the column headings in your pivot table.
• Rows: Fields dragged here become the row headings in your pivot table.
• Values: Fields placed here are the numbers or values you want to summarize (e.g., sum of sales, count of units). By default, Excel often sums numeric fields, but you can change the calculation type in the Value Field Settings.

Building the Pivot Table: The core process of creating a pivot table involves simply dragging fields from the list at the top of the pane into the four areas below. There’s often a bit of trial and error involved depending on the analysis you need. For example, to see the total sales by manager, you would drag the “Sales” field into the Values area and the “Manager” field into the Rows area. The pivot table report will update as you drag and drop fields. You can easily move fields between areas to change the layout and analysis. Placing multiple fields in the Rows or Columns areas will create nested levels of detail. The order of fields within an area matters for the hierarchy of the report (e.g., Country then Product, or Product then Country).

Excel provides helpful automatic grouping for date fields when you drag them into Rows or Columns, often breaking them down into Years, Quarters, and the Date itself, allowing you to easily analyze data by different time periods. You can expand or collapse these groups or customize which levels (Years, Quarters, Months, Days) are displayed via the Group Field option on the PivotTable Analyze ribbon.

A more advanced scenario is creating a pivot table from multiple tables. This is necessary when the data you need for analysis is spread across different sets of information, each in its own table. To do this:

1. Ensure each set of data is in a regular Excel table and named meaningfully.
2. The tables must share a common field (like an “Order ID”) that acts as a “key” to link the data logically between them.
3. When creating the pivot table, you must use the standard “PivotTable” option from the Insert ribbon. In the “Create PivotTable” dialog box, after selecting your first table and location, you must select the option “Add this data to the Data Model”.
4. After the pivot table is created, the PivotTable Fields pane will initially show fields from the table you were in, but clicking “All” will display fields from all imported tables that were added to the Data Model.
5. The critical next step is to create relationships between these tables based on their common field. This is done from the PivotTable Analyze ribbon using the “Relationships” button. In the “Manage Relationships” dialog, you click “New” and define the links, specifying which table and column relate to which other table and column (e.g., linking the “Order ID” in the ‘Order Info’ table to the “Order ID” in the ‘Payment Info’ table).
6. Once relationships are established, you can freely drag fields from any of the linked tables into the Filters, Columns, Rows, and Values areas to build your consolidated pivot table.

Finally, it’s a good practice to name your pivot table itself (PivotTable Analyze ribbon > Properties group) to keep everything organized and easy to reference, similar to naming tables. You can also drill down into any number in your pivot table by double-clicking it, which will open a new sheet showing the underlying data that makes up that total. For large data sets, you can use the “Defer Layout Update” option at the bottom of the PivotTable Fields pane to organize your fields before updating the pivot table, which can improve performance.

Excel Custom Formatting: Numbers and Styles

Based on the sources and our conversation, custom formatting in Excel, particularly within pivot tables, refers primarily to controlling the visual appearance of numbers and values, and also extending to the overall look and feel of the pivot table itself through custom styles.

Custom Number Formatting in Pivot Tables

Custom number formatting is a powerful tool for controlling exactly how numbers and values are displayed in your pivot table report. While you can apply basic formatting like currency or accounting format through the Value Field Settings dialog box, custom formatting allows for much greater control.

To apply custom number formatting in a pivot table, you should right-click anywhere in your numeric data within the pivot table, go down to Value Field Settings, and then select Number Format from there. This is a better approach than using the formatting options on the Home ribbon, which might lead to problems later. From the Format Cells dialog that appears, you can select the Custom category.

The key to understanding custom number formatting is remembering a simple rule: the format string is typically broken into four parts separated by semicolons. These parts define how different types of values are displayed:

1. Positive numbers: The format before the first semicolon.
2. Negative numbers: The format between the first and second semicolon.
3. Zero values: The format between the second and third semicolon.
4. Text values: The format after the third semicolon.

You don’t necessarily have to define all four parts every time.

Examples of Custom Number Formatting from the Sources:

• Formatting Negative Numbers: By default, negative numbers might show in brackets. You can use custom formatting to show them with a minus sign and/or in a different color like red or blue. For example, the format #,##0.00;[Red]-#,##0.00 formats positive numbers with a thousand separator and two decimal places, while negative numbers are shown in red with a minus sign and the same number format. You can add currency symbols to these formats as well.
• Formatting Zero Values: You can define how cells with a value of zero should appear. This could be simply 0 or you could display text like “no data” by putting the desired text in quote marks in the third section of the format string (e.g., Positive;Negative;”no data”).
• Combining Text and Values: You can include text along with the numeric display. For example, you could add the word “loss” next to negative numbers by including “loss” in quote marks within the negative number part of the format string.
• Using Placeholders (# vs. 0): Within the format parts, symbols like # (hash) and 0 (zero) are used as placeholders for digits. A # is a variable placeholder, only displaying digits if they are present, while a 0 is fixed, forcing a digit (zero if necessary) to be displayed. This is useful for maintaining consistent length for numbers, such as formatting item numbers like “1” and “100” to “0001” and “0100” using 0000 as the custom format.

It is important to remember that applying custom formatting only changes the visual appearance of the number; the underlying value in the cell remains unchanged. This means you can format a zero value to show as “no data,” but Excel still recognizes it as a zero for calculations.

If you want to revert a cell or range back to its original state after applying custom formatting, you can use the General format. Custom formats you create are saved in the Custom formatting list within the workbook for potential reuse.

Custom Pivot Table Styles

Beyond number formatting, the sources also discuss creating custom pivot table styles. This refers to defining the visual look of different elements within the pivot table itself, such as header rows, banded rows or columns, first column, grand totals, etc., including aspects like font styles (e.g., italics), fill colors, and borders.

You can access options to create a new custom style from the PivotTable Design ribbon, in the PivotTable Styles group, by clicking the “more” button and selecting New PivotTable Style. This opens a dialog where you can name your style and then select and format each individual element of the pivot table.

Unlike custom number formats, custom pivot table styles are typically saved within the workbook where they were created and are not automatically available in other workbooks. However, you can copy a custom style to another workbook by copying a worksheet containing a pivot table with that style applied into the new workbook.

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!

This resource provides a detailed tutorial on using Excel pivot tables for summarizing and analyzing large datasets. It begins by explaining the purpose and usefulness of pivot tables, contrasting them with static Excel tables and demonstrating their dynamic nature. The material then focuses heavily on data cleaning techniques that are essential before creating a pivot table, covering the removal of blank rows, duplicates, and unwanted formatting, as well as using formulas and features like Flash Fill to standardize text, dates, and separate combined data. Finally, the tutorial walks through creating and building a pivot table from scratch, explaining the pivot table field list, adding and manipulating fields, understanding aggregation methods (sum, count, average, etc.), grouping data, and customizing the report layout with subtotals, grand totals, and blank rows.

Mastering Excel Pivot Tables

Based on the information from the sources, here is a discussion about Pivot Tables in Excel:

What are Pivot Tables and Why are They Useful?

Effectively, pivot tables are an interactive way of quickly summarizing large amounts of data. In our data-driven world, many individuals collect data from various sources to support better business decisions. However, simply looking at large datasets in an Excel spreadsheet doesn’t clearly highlight key metrics, issues, successes, failures, or trends. Pivot tables provide a way to take this data and make sense of it.

For example, with a dataset of over 14,000 rows of sales data including region, country, item type, sales channel, order priority, order date, order ID, ship date, units sold, unit price, unit cost, total revenue, total cost, and total profit, it’s difficult to easily see things like the top 10 countries by total profit or the number of high-priority orders. Using filter drop-downs is possible but much less efficient than using a pivot table.

The key difference between a regular Excel table and a pivot table is that pivot tables are dynamic. This means you can quickly change the analysis being performed. By moving fields around, you can instantly view the data summarized in different ways, such as seeing the sum of total profit by country after initially looking at units sold. You can add other fields to break down the analysis further, like dropping ‘item type’ into columns to see sales summarized by country and item type. You can also apply filters, for instance, to show only the top five countries to make the data more manageable. Once data is in a pivot table, it can be pivoted in various ways, allowing the creation of more pivot tables and even pivot charts. This opens up opportunities for visual analysis, which is often easier for people to interpret. Ultimately, this can lead to creating interactive dashboards showing key metrics with filters.

In summary, a pivot table is a dynamic, interactive tool for summarizing large datasets. They are useful because they help analyze large datasets in a clear and effective way.

Difference Between Excel Tables and Pivot Tables

It’s important to understand the distinction between Excel Tables and Pivot Tables, as they are not the same. Excel tables are essentially static; you can sort or filter the data, but you cannot easily analyze it in many different ways. In contrast, pivot tables are much more dynamic. With a pivot table, you can move fields around and add different fields to view your data in numerous ways, making them ideal for data analysis.

The sources strongly recommend putting your data into an Excel table prior to creating a pivot table. While it might seem like an extra step, there are many advantages to using Excel tables that make working with pivot tables much easier. One of the most useful features of Excel tables is their auto-expand capabilities. If you add new data to the bottom of an Excel table, it automatically expands to include that data. This means that any pivot table or chart linked to that Excel table will automatically include the new data after a simple refresh. If your data is not in an Excel table, you would have to manually reselect the data range to include new rows.

When data is formatted as an Excel table, it automatically gets some formatting like shading and borders, plus filter and sort drop-downs in the headers. An additional ribbon called Table Design appears when you select a cell within the table. This contextual ribbon contains tools to format the table, apply options, and access table tools.

Preparing Data Before Creating a Pivot Table (Data Cleaning)

Before analyzing data with a pivot table, it is extremely important to clean the data. Data cleaning refers to processes in Excel used to tidy up datasets, make them consistent, format them correctly, and present the data in a way that a pivot table can easily analyze and produce accurate results. Skipping this step can lead to inaccurate analysis. This is particularly crucial if data is downloaded from a third party, external source, or database, as it may not import into Excel in the expected format. Issues like columns being out of place, strange formatting, blank rows, blank cells, or duplicate entries can occur.

Several techniques are discussed for cleaning data:

• Removing Blank Rows: Blank rows make data harder to read and cause issues in pivot tables, appearing as a ‘blank’ entry. Manually deleting them is tedious for large datasets. Excel provides a quicker way:

1. Select the data range (e.g., using Ctrl+A while clicked in the data).
2. Go to the Home tab, in the Editing group, click Find & Select, and choose Go To Special.
3. Select ‘Blanks’ and click OK. This selects all blank cells/rows in the selection.
4. Go back to the Home tab, in the Cells group, click Delete, and select Delete Sheet Rows. Removing blank rows before creating a pivot table ensures accuracy and prevents the ‘blank’ entry from appearing.

• Removing Duplicates: Duplicates can also cause problems for pivot tables. The desired removal depends on the type of duplicate; for instance, removing duplicate records where every column is identical, as opposed to repeated values in a single column like ‘Online’/’Offline’ in sales channel. Excel has a Remove Duplicates utility for this.

1. Click anywhere in the data.
2. Go to the Data tab, in the Data Tools group, click Remove Duplicates.
3. A dialog box appears allowing you to select which columns to consider when checking for duplicates.

• Formatting Data: Applying the correct formatting is important.
• Columns with text (like Region, Country, Item Type) can be formatted as Text using the Format Cells dialog box (Ctrl+1).
• Dates might appear as numbers if date formatting isn’t applied. This is because Excel stores dates as numbers, counting days since January 1st, 1900. To display them correctly, select the column and apply Short Date or Long Date format from the Home tab’s Number group.
• Numeric columns (like Unit Price, Total Revenue, Total Profit) should have appropriate number formatting. Currency and Accounting formats are common for monetary values. Accounting format often aligns currency symbols to the left and decimal places, which many find easier to read than Currency format where the symbol is next to the value. This can be applied via the Home tab or the Format Cells dialog box (Ctrl+1).
• Tidying Up Text: Inconsistencies in text, such as different cases (uppercase, lowercase, proper case) or erroneous spaces (leading, trailing, or multiple spaces between words), can make analysis inaccurate.
• Changing Case: Use Excel text formulas like UPPER(), LOWER(), or PROPER(). A recommended method is to use a “helper column” next to the column needing changes, write the formula (e.g., =PROPER(B4)) in the first cell, copy it down, then copy the results and use Paste Special > Paste Values over the original column to remove the formulas, and finally delete the helper column.
• Removing Spaces: The TRIM() function removes leading, trailing, and excessive spaces within text. Even if spaces aren’t visible, applying TRIM() is a good practice. Similar to changing case, use a helper column, the TRIM() formula (e.g., =TRIM(B4)), copy/paste values, and delete the helper column.
• Removing Line Breaks: The CLEAN() function removes non-printable characters, including line breaks. Again, use a helper column, the CLEAN() formula (e.g., =CLEAN(A4)), copy/paste values, and delete the helper column.
• Splitting Data: Sometimes a single column contains multiple pieces of data that should be separate (e.g., Order Date and Order ID combined).
• Text to Columns: This feature is useful when data is separated by a consistent delimiter (like a comma, tab, space, or other character).

1. Select the column(s) you want to split.
2. Go to the Data tab, in the Data Tools group, click Text to Columns.
3. In the wizard, choose ‘Delimited’ if your data has separators or ‘Fixed width’ if data is aligned in columns.
4. Specify the delimiter(s). The preview shows how the data will be split.
5. Choose the data format for each new column (optional, General often works) and importantly, the Destination cell where the split data should start appearing.
6. Click Finish.

• Flash Fill: This feature, introduced in Excel 2013, automatically fills data based on a detected pattern. It can be used to split data (e.g., first name and last name from a full name) or combine data.

1. Type the desired output for the first item in a new column next to your data.
2. Press Ctrl+Enter to stay in the cell.
3. Go to the Data tab, in the Data Tools group, click Flash Fill (or use the shortcut Ctrl+E). Excel will attempt to apply the pattern to the rest of the column. You can also start typing the second item, and Flash Fill may show a grayed-out preview; hit Enter if it’s correct.

• Using Formulas: Excel functions like CONCAT() (or CONCATENATE() in older versions) can combine data from multiple cells. These are useful if you need to add specific text or characters (like a hyphen and spaces) between the combined data. Formulas require referencing the cells and enclosing text within quote marks.
• Replacing Data: You might need to replace specific text or values.
• Find and Replace: This utility (Ctrl+H) can find specific text and replace it with something else throughout the selected range.
• Substitute Formula: The SUBSTITUTE() function can replace specific text within a cell based on a formula (e.g., =SUBSTITUTE(B4,”UK”,”United Kingdom”)). Like other formulas, you’d use a helper column and Paste Special > Paste Values to apply the result.
• Spell Check: Running a spell check is crucial because if something is misspelled, a pivot table will treat it as a completely separate item, leading to inaccurate analysis. The Spell Checker is on the Review tab in the Proofing group (F7 shortcut). It starts checking from the currently selected cell. You can choose to ignore, change, change all, or add words to the dictionary (useful for names or brands not in the standard dictionary).

Putting Data into an Excel Table

As mentioned, it is highly recommended to put your clean data into an Excel Table before creating a pivot table. You must be clicked somewhere within your data set to do this.

There are two main ways to format data as a table:

1. Go to the Home tab, in the Styles group, click the Format as Table drop-down and choose a table style.
2. Click anywhere in the data and press the keyboard shortcut Ctrl+T. This opens the Create Table dialog box.

Both methods will ask if your table has headers. Once applied, your data gets default formatting and the Table Design contextual ribbon appears. From the Table Design ribbon, you can customize the style, add a total row, toggle banded rows or columns, and turn the filter button on/off.

In the Properties group of the Table Design ribbon, you can see and rename the table. It’s good practice to give your table a meaningful name (like Sales_Data) instead of the default generic name (like Table1) because it makes referencing the data easier, especially in workbooks with multiple tables. Table names cannot contain spaces.

Creating a Pivot Table

Once your data is clean and in an Excel table, you are ready to create a pivot table.

• Recommended Pivot Tables: Excel can analyze your data and suggest pivot table layouts.

1. Click anywhere in your data table.
2. Go to the Insert tab, in the Tables group, click Recommended PivotTables.
3. A window pops up showing different suggested pivot table summaries based on your data (e.g., sum of unit price by region, sum of profit by item type).
4. Select the one that best suits your needs and click OK. Excel creates a new worksheet with the pre-built pivot table. You can still modify this table afterward.

• Creating a Blank Pivot Table from Scratch: This gives you full control over the layout.

1. Click anywhere in your data table.
2. Go to the Insert tab, in the Tables group, click PivotTable. Alternatively, from the Table Design ribbon, in the Tools group, click Summarize with PivotTable. Both methods open the Create PivotTable dialog box.
3. Choose the data: The dialog box should automatically detect and select your Excel table (e.g., Sales_Data). You can also choose to use an external data source from another file or database.
4. Choose where to place the report: The common and recommended practice is to place the pivot table on a New Worksheet to keep your raw data separate from your analysis. You can also choose an existing worksheet.
5. Click OK. Excel creates a new worksheet containing a blank pivot table report area and the PivotTable Fields pane on the right.

Understanding the Pivot Table Interface

When you click inside the blank pivot table report area, two additional contextual ribbons appear: PivotTable Analyze and PivotTable Design. These ribbons contain commands for managing, organizing, and changing the look of your pivot table. They disappear when you click outside the pivot table.

• PivotTable Design Ribbon: Focuses on the appearance and layout.
• PivotTable Styles: Similar to table styles, allows choosing a visual style. Styles are influenced by the workbook’s theme.
• PivotTable Style Options: Toggles elements like row/column headers, banded rows/columns.
• Layout: Controls subtotals (show/hide, position), grand totals (on/off for rows/columns), and report layout (Compact, Outline, Tabular forms). You can also insert or remove blank lines after each item.
• PivotTable Analyze Ribbon: Contains functional options.
• PivotTable Name: It’s good practice to rename pivot tables from generic names (e.g., PivotTable1) to meaningful names.
• Options: Accesses various pivot table settings, including layout and format options like auto-fitting column widths.
• Group: Used for grouping selected items or ungrouping.
• Insert Slicer / Insert Timeline: Visual filters for pivot tables (not covered in detail in this source).
• Refresh: Updates the pivot table with any changes to the source data.
• Show group: Toggle buttons to show/hide the Field List pane, plus/minus buttons, and headers. If the Field List disappears, check this button.

The PivotTable Fields pane (usually on the right) is crucial for building the pivot table. At the top, it lists all the column headings from your source data as fields. Below are four areas: Filters, Columns, Rows, and Values. These areas determine the layout and type of analysis.

Building and Modifying a Pivot Table

Building a pivot table involves dragging fields from the top section of the PivotTable Fields pane into one of the four areas.

• Rows Area: Typically used for fields you want to appear as row labels (e.g., Region, Item Type).
• Columns Area: Typically used for fields you want to appear as column labels (e.g., Sales Channel, Order Priority).
• Values Area: This is where you put fields containing numerical data that you want to summarize (e.g., Total Profit, Units Sold). By default, Excel often performs a Sum on numeric fields dragged here, or a Count if the field contains text or dates.
• Filters Area: Fields dragged here create report-level filters at the top of the pivot table, allowing you to filter the entire report by selections from that field (e.g., filtering by specific Countries or Order Dates).

You can easily change the layout by dragging fields between these areas. Dragging a field outside the pane removes it from the pivot table.

• Aggregating Data: The default aggregation (Sum or Count) can be changed.
• Right-click on any value in the column you want to change the aggregation for.
• Select Value Field Settings.
• In the Summarize values by list, choose a different calculation like Average, Max, Min, Product, Count Numbers, etc..
• Click OK. You can also access Value Field Settings by clicking the drop-down arrow next to the field in the Values area.
• You can combine different methods of aggregation by dragging the same field into the Values area multiple times. Each instance can then be summarized using a different calculation (e.g., one column showing Sum of Total Profit, another showing Average of Total Profit).
• Renaming Fields/Headings: You can change the default headings in the pivot table report area (like ‘Row Labels’ or ‘Sum of Total Profit’) by double-clicking the cell and entering a new custom name. Note that renaming a heading in the pivot table report updates the name in the Values area of the fields pane, but the original field name above remains unchanged.
• Number Formatting: To ensure formatting (like currency symbols and decimal places) stays with the numbers when the pivot table layout changes, apply it via the pivot table’s specific options, not just standard cell formatting from the Home tab.

1. Right-click on a number within the column you want to format.
2. Select Number Format. Alternatively, access this via Value Field Settings > Number Format.
3. Choose the desired format (e.g., Accounting, Currency) and settings.
4. Click OK. This applies the formatting to all numbers in that value field.

• Handling Empty Cells: By default, pivot tables show blank cells where there is no data for a combination of criteria. This can affect charts or make the table harder to read. You can replace blanks with a value like 0:

1. Click inside the pivot table.
2. Go to the PivotTable Analyze ribbon, in the PivotTable group, click Options.
3. On the Layout & Format tab, under the Format group, check the box for For empty cells show: and enter the value you want to display (e.g., 0).
4. Click OK.

Grouping Data

Grouping allows you to combine items in your pivot table.

• Automatic Grouping: Excel automatically groups dates when you drag a date field into rows or columns. It analyzes the data and creates fields for years, quarters, and months if applicable. These automatically created fields (like ‘Years’ and ‘Quarters’) appear in the PivotTable Fields pane and can be used independently. You can expand/collapse these groups using the +/- buttons in the pivot table.
• Custom Grouping: You can create your own groups from non-date fields (e.g., grouping several Item Types into a ‘Food and Drink’ category).

1. Select the items you want to group by holding down Ctrl and clicking each item.
2. Go to the PivotTable Analyze ribbon, in the Group group, click Group Selection. Excel creates a new group (e.g., ‘Group1’) and a new field in the Rows/Columns area (e.g., ‘Item Type2’).
3. You can rename the group label in the pivot table (using F2 or double-clicking and changing the custom name in Value Field Settings) and rename the new group field in the fields pane (using Field Settings).

• Ungrouping: To reverse automatic or custom grouping, select an item within the group and click Ungroup in the Group group on the PivotTable Analyze ribbon.
• Inserting Blank Lines: To improve readability, especially with grouping, you can insert blank rows between groups. Go to the Design ribbon, in the Layout group, click Blank Rows, and select Insert Blank Line after Each Item. To remove them, choose Remove Blank Line after Each Grouped Item.

Layout Options

You can customize the overall appearance and structure of your pivot table report. These options are found on the PivotTable Design ribbon, in the Layout group.

• Subtotals:You can choose not to show subtotals at all.
• You can show them at the bottom of each group (often preferred) or at the top of each group (the default).
• Grand Totals:You can turn grand totals off for both rows and columns.
• You can turn them on for both rows and columns, only for rows, or only for columns. Turning them off is common when creating charts to avoid including totals.
• Report Layout: This changes how the fields are displayed in the report area.
• Compact Form: Optimizes for readability and uses space efficiently. It places subtotals at the top of groups and keeps related fields in the same column. This is the most compact view.
• Outline Form: Moves the innermost row field to a new column, creating a hierarchical structure where each field is in its own column. Subtotals appear at the top by default, but you can change their position.
• Tabular Form: Similar to Outline form, but adds grid lines within the pivot table, making it look more like a regular Excel table.
• Repeat Item Labels: In Outline or Tabular forms, you can choose to repeat the labels for outer row fields on every line instead of only showing them once. This can make the table easier to read in some cases or is necessary for certain chart types like map charts. You can turn this off if desired.

These options allow you to tailor the pivot table’s appearance to best suit your analysis and presentation needs.

Cleaning Data for Excel Pivot Tables

Data cleaning is a crucial process to undertake before analyzing large datasets, particularly when planning to use tools like pivot tables in Excel. It involves tidying up data sets, making them consistent, formatting them correctly, and presenting the data in a way that allows for easy and accurate analysis. Skipping this step, especially when importing data from external sources or databases, can lead to inaccurate analysis because data doesn’t always import in the expected format, potentially including columns out of place, strange formatting, blank rows, or duplicate entries.

Here are some of the key data cleaning techniques discussed in the sources:

• Removing Blank Rows Blank rows make data harder to read and can cause issues in pivot tables by being picked up as a “blank” entry. Manually deleting them row by row is tedious for large datasets. A quicker method involves selecting the data range, using “Go To Special” to select “Blanks,” and then using the “Delete Sheet Rows” command. Removing blank rows ensures the pivot table is accurate.
• Removing Duplicate Entries Duplicate rows, particularly where every column’s information is exactly the same, can sometimes occur when importing data and can cause problems for pivot tables. Excel’s “Remove Duplicates” utility can easily find and remove these exact duplicates. You can specify which columns to check for duplicates, but typically, you check all columns to find completely duplicated rows.
• Removing Unwanted Formatting Imported data may contain inconsistent formatting like background shading, bold text, or italics, which results in an inconsistent-looking worksheet. This formatting often isn’t desired. The “Clear Formats” option, found under the “Clear” button in the Home tab’s editing group, can quickly remove all applied formatting, including background shading, bold, italics, and number formatting, providing a clean slate. Other “Clear” options exist for different purposes, such as clearing only contents, comments/notes, or hyperlinks.
• Applying Desired Formatting After clearing unwanted formatting, applying consistent and appropriate formatting is important to make your data easier to read. This is referred to as number formatting but can be applied to any column, not just those containing numbers. The “Number group” on the Home tab provides standard options like General, Number, Currency, Accounting, and Date. Dates in Excel are stored as numbers (days since January 1, 1900), so applying a Date format (like Short Date or Long Date) is necessary to display them correctly. For numeric data, you can control decimal places using dedicated buttons or the “Format Cells” dialog box (Ctrl + 1). For monetary values, Currency and Accounting formats add symbols; Accounting format is often preferred as it aligns currency symbols and decimal points, enhancing readability for lists of numbers.
• Tidying Up Text Using Formulas Inconsistencies in text, such as case variations (uppercase, lowercase, proper case) or erroneous spaces (leading, trailing, multiple spaces between words), can negatively impact analysis. Excel provides text functions to standardize these:
• UPPER(), LOWER(), and PROPER() functions are used to change the case of text.
• TRIM() removes leading/trailing spaces and extra spaces between words.
• CLEAN() removes non-printing characters, which might appear as small square boxes, and can also remove manual line breaks within cells. These functions are typically used in a “helper column” next to the original data. Multiple functions can be combined in a single formula in a helper column to perform several cleaning steps at once, saving time.
• Using Paste Special to Convert Formulas to Values When cleaning data using formulas in a helper column, the formulas refer to the original data column. If the original column is simply deleted, the helper column will result in #REF! errors because the references are broken. To avoid this, the cleaned data in the helper column must be converted from formulas to static values. This is achieved by copying the helper column and then using the “Paste Special” > “Paste Values” option to paste only the resulting values over the original column (or a new location), discarding the underlying formulas. Once the values are pasted, the helper column can be safely deleted.
• Splitting and Combining Data Sometimes data is combined in a single cell that needs to be separated (e.g., “Order Date Order ID”), or data in separate cells needs to be combined.
• “Text to Columns” is a wizard that splits a single column of text into multiple columns based on a specified delimiter (like a comma, space, or other character) or a fixed width.
• “Flash Fill” is a faster tool (available since Excel 2013) that can split or combine data by recognizing patterns based on one or two examples provided by the user. It can be accessed via a button on the Data tab or the Ctrl + E shortcut.
• The CONCAT() function (or CONCATENATE() in older versions) joins text from multiple cells. Custom text or delimiters can be included in the joined result by enclosing them in quote marks within the function.
• Finding and Replacing Data To standardize inconsistent text entries (e.g., replacing “Democratic Republic of the Congo” with “DRC” or “United States of America” with “USA”), you can use the “Find and Replace” dialog box (Ctrl + F, then select the Replace tab). You specify what to find and what to replace it with, choosing whether or not to match the case. The SUBSTITUTE() formula can also perform find and replace using a formula, requiring the “Paste Special” > “Paste Values” trick afterward.
• Running a Spell Check Spelling errors can cause problems in pivot tables because the table will treat variations of the same word as completely separate items. Running a spell check (Review tab > Proofing group, or F7) helps ensure consistency in text entries. You can choose the dictionary language and add correctly spelled but unrecognized words to the dictionary.

Once data is cleaned, it is highly recommended to put it into an Excel Table before creating a pivot table. Excel Tables offer several advantages, including automatic formatting, built-in filter and sort buttons, and importantly, auto-expand capabilities. This means that if new data is added to the table, it is automatically included in the data source for any associated pivot tables or charts, which can then be updated by simply clicking the refresh button. Data can be converted into an Excel Table using the “Format as Table” option on the Home tab or the Ctrl + T keyboard shortcut. Tables can be given meaningful names for easier identification.

In summary, thorough data cleaning is essential for accurate and effective analysis using pivot tables, addressing issues like inconsistencies, errors, and formatting problems through various Excel tools and functions.

Excel Data Analysis with Pivot Tables

Based on the sources, data analysis is the process of summarizing large amounts of data to make sense of them. In a data-driven world where information is collected from various sources, simply looking at a large spreadsheet might not highlight key metrics, issues, successes, failures, or trends. Data analysis aims to take this data and present it in a way that allows for clearer understanding and better business decisions.

Excel provides powerful tools for data analysis, particularly Pivot Tables.

Key aspects of Data Analysis discussed in the sources:

1. The Role of Pivot Tables Pivot tables are described as an interactive and dynamic way to quickly summarize large amounts of data. Unlike static Excel tables where analysis is limited primarily to sorting and filtering, pivot tables allow you to pivot fields around and view data in all different ways. This dynamism makes it much more efficient to analyze data compared to manually using filters. Pivot tables help analyze large datasets in a clear and effective way. They facilitate asking questions about the data, such as finding top performers or seeing counts of high-priority orders. Pivot charts can be created from pivot table data to offer visual analysis options, as most people find it easier to analyze and interpret data visually. This can extend to creating interactive dashboards with filters for deeper analysis.
2. The Critical Need for Data Cleaning Before Analysis A central theme is that data cleaning is essential prior to analyzing data with a pivot table. Skipping this step, especially when importing data from external sources or databases, can lead to inaccurate analysis. Data doesn’t always import in the desired format, and inconsistencies or errors can cause problems for pivot tables. Cleaning ensures the data is tidied up, consistent, correctly formatted, and presented in a way that allows the pivot table to easily analyze it and produce accurate results. The sources highlight cleaning steps like removing blank rows, removing duplicate entries, clearing unwanted formatting, applying desired formatting, tidying text using formulas (case, spaces), splitting and combining data, finding and replacing data, and running a spell check. All these steps contribute to a “clean looking data set ready for analysis”.
3. Structuring Analysis with Pivot Table Fields To perform analysis with a pivot table, you use the Pivot Table Fields pane, which lists the column headings from your source data. These fields are dragged into four areas: Filters, Columns, Rows, and Values. These areas determine the layout of the pivot table and control the type of analysis being done. Placing fields in different areas changes how the data is summarized and viewed.
4. Aggregating Data for Analysis The Values area is typically where numeric fields are placed. By default, Excel usually performs a sum calculation for numeric values and a count for text or date fields dropped into this area. However, you can change how the data is summarized using the Value Field Settings. This allows you to choose from various aggregation methods, including Sum, Count, Average, Max, Min, Product, and more. You can even combine different aggregation methods (like sum and average) for the same data by dragging the field into the Values area multiple times and setting a different calculation for each instance. This ability to calculate averages, mins, or maxes “on the fly” expands the analysis beyond what was present in the raw source data.
5. Grouping Data for Deeper Analysis Grouping data is another way to analyze it. Excel automatically groups certain fields, like dates, into categories like years, quarters, and months. This allows you to see the data summarized at different levels (e.g., total profit by year, then by month within each year). You can also create your own custom groups for non-date fields to categorize data according to your analysis needs (e.g., grouping different item types into “food and drink” or “other”). Grouping allows for analyzing data in “multiple dimensions” by adding more fields to the Rows or Columns areas.
6. Handling Empty Cells and Layout How empty cells are displayed affects the accuracy of analysis, especially in pivot charts. Replacing blank cells with zeros in the Pivot Table Options ensures that items with no data are still represented, showing a zero value rather than being excluded from the analysis or charts. Additionally, the report layout options (compact, outline, tabular) and the choice to display or hide subtotals and grand totals affect the readability and presentation of the analyzed results.

In summary, data analysis in Excel, as presented in the sources, relies heavily on the dynamic capabilities of Pivot Tables, which allow for summarizing, slicing, dicing, and aggregating data in various ways. However, the foundation of accurate analysis is thorough data cleaning, ensuring the data is reliable and free from inconsistencies before being used in a pivot table. Using Excel Tables is also recommended as it makes managing and updating the data source for analysis more efficient.

Grouping Data in Excel Pivot Tables

Based on the sources, grouping data in Excel pivot tables is a way to summarize data by multiple fields and organize the display of that data. It allows you to analyze information at different levels or categorize data according to specific needs.

Here are key aspects of grouping data discussed in the sources:

• Automatic Grouping Excel will automatically apply grouping when you summarize data by more than one field in areas like the Rows or Columns of a pivot table.
• Date Grouping A common example of automatic grouping occurs when you drag a date field into an area like Rows. Excel looks at your source data and automatically groups the dates by categories such as years, quarters, and months. These levels appear as separate fields (e.g., “Years,” “Quarters,” “Order Date”) in the Pivot Table Fields pane. You can then use these fields independently to summarize data at different granularities, for instance, viewing total profit by year, and then expanding to see the breakdown by month within each year. If you don’t need a specific level, like quarters, you can simply remove that field from the Rows area. The “Group Field” option on the Pivot Table Analyze ribbon shows the date ranges and the levels (months, quarters, years) that Excel has pulled from the data.
• Custom Grouping You can create your own custom groups for fields that are not dates. This allows you to categorize data based on your analytical requirements. For example, you could select several ‘item type’ categories like ‘baby food’, ‘beverages’, ‘cereal’, ‘fruits’, ‘meat’, ‘snacks’, and ‘vegetables’ and group them together under a new name like “Food and Drink”. The remaining items could be grouped under “Other”.
• Creating Custom Groups To create a custom group, you select the specific items in the pivot table report that you want to include in the group. Then, you go to the Pivot Table Analyze ribbon and select the Group Selection button. Excel will create a new group (initially named generically, like “Group1”). You can rename this group directly in the pivot table report. Excel also creates a new field in the Pivot Table Fields pane corresponding to this custom group (e.g., “Item Type2” if you grouped based on ‘Item Type’). It is recommended to rename this new field as well (e.g., “Food and Drink”) for consistency. This can be done by clicking the drop-down arrow for the field in the Rows area and selecting “Field Settings,” or by right-clicking the field name in the Rows area and selecting “Field Settings”.
• Expanding and Collapsing Groups When grouping is applied, items in the pivot table report often display with little plus and minus symbols next to them. These symbols allow you to collapse or expand the details within a group, letting you focus on summary levels or drill down into specifics. You can toggle the display of these buttons on or off from the Pivot Table Analyze ribbon in the Show group.
• Multi-Dimensional Analysis Grouping contributes significantly to creating multi-dimensional pivot tables. By adding more fields and grouping them in the Rows or Columns areas, you can analyze your data by multiple factors simultaneously (e.g., analyzing profit by region, item type, and sales channel).
• Ungrouping Data If you need to revert a group, you can select an item within the group in the pivot table and click the Ungroup button on the Pivot Table Analyze ribbon.
• Grouping and Layout The report layout options can interact with grouping. For example, the Compact Form layout maintains the grouping structure. Adding blank rows using the “Blank Rows” option on the Design ribbon will insert a blank line after each grouped item, which can help emphasize groups and improve readability.

Excel Number Formatting Explained

Based on the sources and our conversation, number formatting is a crucial aspect of data cleaning and analysis in Excel, particularly to improve readability and consistency of your data. It involves ensuring that values in your cells are displayed in a way that accurately reflects their type and makes them easy to interpret.

Here’s a breakdown of the key points about number formatting discussed:

1. Purpose of Number Formatting:

• To make your data a lot easier to read.
• To ensure consistency in how numbers are displayed, such as the number of decimal places and the presence of currency symbols.
• A currency symbol, for example, always makes monetary values a lot easier to read.

1. Applying Formatting in Standard Worksheets:

• Formatting is applied using the Home tab in the Number group.
• A drop-down menu provides common formatting options (e.g., General, Number, Currency, Accounting, Short Date, Long Date).
• You can access more detailed formatting options by clicking “More Number Formats” at the bottom of the drop-down or by using the Ctrl+1 keyboard shortcut to open the “Format Cells” dialog box.
• The appropriate format depends on the type of information in the column.
• Examples discussed include:
• Applying Text formatting to columns containing text.
• Applying Date formatting to columns containing dates. Excel stores dates as numbers (days since January 1, 1900), and date formatting is needed to display them as calendar dates. If not formatted as a date, you might see the underlying numeric value. “Short date” and “long date” are common options. Custom date formats are also available via “More number formats” but are considered advanced.
• Applying Number formatting to columns like “Units Sold,” where you might need to control the number of decimal places (e.g., reducing to zero using the Increase/Decrease Decimal buttons or “Format Cells”).
• Applying Currency or Accounting formatting to monetary columns like “Unit Price,” “Total Revenue,” or “Total Profit” to add a currency symbol and control decimal places. The key difference is that Accounting format aligns the currency symbols and decimal points in a column, which is often considered easier to read, especially in long lists of numbers, whereas Currency format places the symbol right next to the value and doesn’t align decimals. The sources suggest Accounting format is frequently used.

1. Formatting and Data Cleaning Steps:

• When initially cleaning data, steps like using “Clear Formats” can remove all formatting, including desirable number formatting. Therefore, you might need to reapply the correct formatting after this step.
• Helper columns created for text cleaning formulas (like UPPER, TRIM, CLEAN, SUBSTITUTE) might inherit the formatting of surrounding columns, sometimes defaulting to “Text”. To see formula results correctly, these columns might need to be changed back to “General” format before applying the formula.
• Identifying numbers stored as text is important. Indicators include the number being aligned to the left side of the cell and a little green triangle in the corner. You can convert these using the warning symbol option “Convert to Number” or by using the VALUE formula.

1. Number Formatting in Pivot Tables:

• When you build a pivot table, the numbers in the values area are initially unformatted and inconsistent.
• It is NOT recommended to apply number formatting directly to the cells in a pivot table using the Home ribbon. This is because pivot tables are dynamic; the fields and their locations can change when you rearrange or “pivot” the data. Formatting applied to a static cell will not move with the number it was applied to if the layout changes.
• The correct method for applying number formatting in a pivot table is to apply it to the number itself, which ensures it moves with the data regardless of the layout.
• This is done by right-clicking on a number within the pivot table and selecting “Number Format”.
• Alternatively, you can access this through the Value Field Settings for the specific field in the Values area, and then clicking the “Number Format” button at the bottom.
• Both methods open the familiar “Format Cells” dialog box, allowing you to choose formats like Accounting or Currency.
• Custom number formatting is also available through this pivot table method.
• If you configure your pivot table to show zero for empty cells, these zeros will also display with the number formatting applied to that values field (e.g., showing “$ -“).

In essence, applying consistent and appropriate number formatting is a vital step, first during general data cleaning and preparation, and then specifically within pivot tables using the recommended methods to maintain accuracy and readability as you analyze your data.

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!

This resource provides a detailed tutorial on using Excel pivot tables for summarizing and analyzing large datasets. It begins by explaining the purpose and usefulness of pivot tables, contrasting them with static Excel tables and demonstrating their dynamic nature. The material then focuses heavily on data cleaning techniques that are essential before creating a pivot table, covering the removal of blank rows, duplicates, and unwanted formatting, as well as using formulas and features like Flash Fill to standardize text, dates, and separate combined data. Finally, the tutorial walks through creating and building a pivot table from scratch, explaining the pivot table field list, adding and manipulating fields, understanding aggregation methods (sum, count, average, etc.), grouping data, and customizing the report layout with subtotals, grand totals, and blank rows.

Mastering Excel Pivot Tables

Based on the information from the sources, here is a discussion about Pivot Tables in Excel:

What are Pivot Tables and Why are They Useful?

Effectively, pivot tables are an interactive way of quickly summarizing large amounts of data. In our data-driven world, many individuals collect data from various sources to support better business decisions. However, simply looking at large datasets in an Excel spreadsheet doesn’t clearly highlight key metrics, issues, successes, failures, or trends. Pivot tables provide a way to take this data and make sense of it.

For example, with a dataset of over 14,000 rows of sales data including region, country, item type, sales channel, order priority, order date, order ID, ship date, units sold, unit price, unit cost, total revenue, total cost, and total profit, it’s difficult to easily see things like the top 10 countries by total profit or the number of high-priority orders. Using filter drop-downs is possible but much less efficient than using a pivot table.

The key difference between a regular Excel table and a pivot table is that pivot tables are dynamic. This means you can quickly change the analysis being performed. By moving fields around, you can instantly view the data summarized in different ways, such as seeing the sum of total profit by country after initially looking at units sold. You can add other fields to break down the analysis further, like dropping ‘item type’ into columns to see sales summarized by country and item type. You can also apply filters, for instance, to show only the top five countries to make the data more manageable. Once data is in a pivot table, it can be pivoted in various ways, allowing the creation of more pivot tables and even pivot charts. This opens up opportunities for visual analysis, which is often easier for people to interpret. Ultimately, this can lead to creating interactive dashboards showing key metrics with filters.

In summary, a pivot table is a dynamic, interactive tool for summarizing large datasets. They are useful because they help analyze large datasets in a clear and effective way.

Difference Between Excel Tables and Pivot Tables

It’s important to understand the distinction between Excel Tables and Pivot Tables, as they are not the same. Excel tables are essentially static; you can sort or filter the data, but you cannot easily analyze it in many different ways. In contrast, pivot tables are much more dynamic. With a pivot table, you can move fields around and add different fields to view your data in numerous ways, making them ideal for data analysis.

The sources strongly recommend putting your data into an Excel table prior to creating a pivot table. While it might seem like an extra step, there are many advantages to using Excel tables that make working with pivot tables much easier. One of the most useful features of Excel tables is their auto-expand capabilities. If you add new data to the bottom of an Excel table, it automatically expands to include that data. This means that any pivot table or chart linked to that Excel table will automatically include the new data after a simple refresh. If your data is not in an Excel table, you would have to manually reselect the data range to include new rows.

When data is formatted as an Excel table, it automatically gets some formatting like shading and borders, plus filter and sort drop-downs in the headers. An additional ribbon called Table Design appears when you select a cell within the table. This contextual ribbon contains tools to format the table, apply options, and access table tools.

Preparing Data Before Creating a Pivot Table (Data Cleaning)

Before analyzing data with a pivot table, it is extremely important to clean the data. Data cleaning refers to processes in Excel used to tidy up datasets, make them consistent, format them correctly, and present the data in a way that a pivot table can easily analyze and produce accurate results. Skipping this step can lead to inaccurate analysis. This is particularly crucial if data is downloaded from a third party, external source, or database, as it may not import into Excel in the expected format. Issues like columns being out of place, strange formatting, blank rows, blank cells, or duplicate entries can occur.

Several techniques are discussed for cleaning data:

• Removing Blank Rows: Blank rows make data harder to read and cause issues in pivot tables, appearing as a ‘blank’ entry. Manually deleting them is tedious for large datasets. Excel provides a quicker way:

1. Select the data range (e.g., using Ctrl+A while clicked in the data).
2. Go to the Home tab, in the Editing group, click Find & Select, and choose Go To Special.
3. Select ‘Blanks’ and click OK. This selects all blank cells/rows in the selection.
4. Go back to the Home tab, in the Cells group, click Delete, and select Delete Sheet Rows. Removing blank rows before creating a pivot table ensures accuracy and prevents the ‘blank’ entry from appearing.

• Removing Duplicates: Duplicates can also cause problems for pivot tables. The desired removal depends on the type of duplicate; for instance, removing duplicate records where every column is identical, as opposed to repeated values in a single column like ‘Online’/’Offline’ in sales channel. Excel has a Remove Duplicates utility for this.

1. Click anywhere in the data.
2. Go to the Data tab, in the Data Tools group, click Remove Duplicates.
3. A dialog box appears allowing you to select which columns to consider when checking for duplicates.

• Formatting Data: Applying the correct formatting is important.
• Columns with text (like Region, Country, Item Type) can be formatted as Text using the Format Cells dialog box (Ctrl+1).
• Dates might appear as numbers if date formatting isn’t applied. This is because Excel stores dates as numbers, counting days since January 1st, 1900. To display them correctly, select the column and apply Short Date or Long Date format from the Home tab’s Number group.
• Numeric columns (like Unit Price, Total Revenue, Total Profit) should have appropriate number formatting. Currency and Accounting formats are common for monetary values. Accounting format often aligns currency symbols to the left and decimal places, which many find easier to read than Currency format where the symbol is next to the value. This can be applied via the Home tab or the Format Cells dialog box (Ctrl+1).
• Tidying Up Text: Inconsistencies in text, such as different cases (uppercase, lowercase, proper case) or erroneous spaces (leading, trailing, or multiple spaces between words), can make analysis inaccurate.
• Changing Case: Use Excel text formulas like UPPER(), LOWER(), or PROPER(). A recommended method is to use a “helper column” next to the column needing changes, write the formula (e.g., =PROPER(B4)) in the first cell, copy it down, then copy the results and use Paste Special > Paste Values over the original column to remove the formulas, and finally delete the helper column.
• Removing Spaces: The TRIM() function removes leading, trailing, and excessive spaces within text. Even if spaces aren’t visible, applying TRIM() is a good practice. Similar to changing case, use a helper column, the TRIM() formula (e.g., =TRIM(B4)), copy/paste values, and delete the helper column.
• Removing Line Breaks: The CLEAN() function removes non-printable characters, including line breaks. Again, use a helper column, the CLEAN() formula (e.g., =CLEAN(A4)), copy/paste values, and delete the helper column.
• Splitting Data: Sometimes a single column contains multiple pieces of data that should be separate (e.g., Order Date and Order ID combined).
• Text to Columns: This feature is useful when data is separated by a consistent delimiter (like a comma, tab, space, or other character).

1. Select the column(s) you want to split.
2. Go to the Data tab, in the Data Tools group, click Text to Columns.
3. In the wizard, choose ‘Delimited’ if your data has separators or ‘Fixed width’ if data is aligned in columns.
4. Specify the delimiter(s). The preview shows how the data will be split.
5. Choose the data format for each new column (optional, General often works) and importantly, the Destination cell where the split data should start appearing.
6. Click Finish.

• Flash Fill: This feature, introduced in Excel 2013, automatically fills data based on a detected pattern. It can be used to split data (e.g., first name and last name from a full name) or combine data.

1. Type the desired output for the first item in a new column next to your data.
2. Press Ctrl+Enter to stay in the cell.
3. Go to the Data tab, in the Data Tools group, click Flash Fill (or use the shortcut Ctrl+E). Excel will attempt to apply the pattern to the rest of the column. You can also start typing the second item, and Flash Fill may show a grayed-out preview; hit Enter if it’s correct.

• Using Formulas: Excel functions like CONCAT() (or CONCATENATE() in older versions) can combine data from multiple cells. These are useful if you need to add specific text or characters (like a hyphen and spaces) between the combined data. Formulas require referencing the cells and enclosing text within quote marks.
• Replacing Data: You might need to replace specific text or values.
• Find and Replace: This utility (Ctrl+H) can find specific text and replace it with something else throughout the selected range.
• Substitute Formula: The SUBSTITUTE() function can replace specific text within a cell based on a formula (e.g., =SUBSTITUTE(B4,”UK”,”United Kingdom”)). Like other formulas, you’d use a helper column and Paste Special > Paste Values to apply the result.
• Spell Check: Running a spell check is crucial because if something is misspelled, a pivot table will treat it as a completely separate item, leading to inaccurate analysis. The Spell Checker is on the Review tab in the Proofing group (F7 shortcut). It starts checking from the currently selected cell. You can choose to ignore, change, change all, or add words to the dictionary (useful for names or brands not in the standard dictionary).

Putting Data into an Excel Table

As mentioned, it is highly recommended to put your clean data into an Excel Table before creating a pivot table. You must be clicked somewhere within your data set to do this.

There are two main ways to format data as a table:

1. Go to the Home tab, in the Styles group, click the Format as Table drop-down and choose a table style.
2. Click anywhere in the data and press the keyboard shortcut Ctrl+T. This opens the Create Table dialog box.

Both methods will ask if your table has headers. Once applied, your data gets default formatting and the Table Design contextual ribbon appears. From the Table Design ribbon, you can customize the style, add a total row, toggle banded rows or columns, and turn the filter button on/off.

In the Properties group of the Table Design ribbon, you can see and rename the table. It’s good practice to give your table a meaningful name (like Sales_Data) instead of the default generic name (like Table1) because it makes referencing the data easier, especially in workbooks with multiple tables. Table names cannot contain spaces.

Creating a Pivot Table

Once your data is clean and in an Excel table, you are ready to create a pivot table.

• Recommended Pivot Tables: Excel can analyze your data and suggest pivot table layouts.

1. Click anywhere in your data table.
2. Go to the Insert tab, in the Tables group, click Recommended PivotTables.
3. A window pops up showing different suggested pivot table summaries based on your data (e.g., sum of unit price by region, sum of profit by item type).
4. Select the one that best suits your needs and click OK. Excel creates a new worksheet with the pre-built pivot table. You can still modify this table afterward.

• Creating a Blank Pivot Table from Scratch: This gives you full control over the layout.

1. Click anywhere in your data table.
2. Go to the Insert tab, in the Tables group, click PivotTable. Alternatively, from the Table Design ribbon, in the Tools group, click Summarize with PivotTable. Both methods open the Create PivotTable dialog box.
3. Choose the data: The dialog box should automatically detect and select your Excel table (e.g., Sales_Data). You can also choose to use an external data source from another file or database.
4. Choose where to place the report: The common and recommended practice is to place the pivot table on a New Worksheet to keep your raw data separate from your analysis. You can also choose an existing worksheet.
5. Click OK. Excel creates a new worksheet containing a blank pivot table report area and the PivotTable Fields pane on the right.

Understanding the Pivot Table Interface

When you click inside the blank pivot table report area, two additional contextual ribbons appear: PivotTable Analyze and PivotTable Design. These ribbons contain commands for managing, organizing, and changing the look of your pivot table. They disappear when you click outside the pivot table.

• PivotTable Design Ribbon: Focuses on the appearance and layout.
• PivotTable Styles: Similar to table styles, allows choosing a visual style. Styles are influenced by the workbook’s theme.
• PivotTable Style Options: Toggles elements like row/column headers, banded rows/columns.
• Layout: Controls subtotals (show/hide, position), grand totals (on/off for rows/columns), and report layout (Compact, Outline, Tabular forms). You can also insert or remove blank lines after each item.
• PivotTable Analyze Ribbon: Contains functional options.
• PivotTable Name: It’s good practice to rename pivot tables from generic names (e.g., PivotTable1) to meaningful names.
• Options: Accesses various pivot table settings, including layout and format options like auto-fitting column widths.
• Group: Used for grouping selected items or ungrouping.
• Insert Slicer / Insert Timeline: Visual filters for pivot tables (not covered in detail in this source).
• Refresh: Updates the pivot table with any changes to the source data.
• Show group: Toggle buttons to show/hide the Field List pane, plus/minus buttons, and headers. If the Field List disappears, check this button.

The PivotTable Fields pane (usually on the right) is crucial for building the pivot table. At the top, it lists all the column headings from your source data as fields. Below are four areas: Filters, Columns, Rows, and Values. These areas determine the layout and type of analysis.

Building and Modifying a Pivot Table

Building a pivot table involves dragging fields from the top section of the PivotTable Fields pane into one of the four areas.

• Rows Area: Typically used for fields you want to appear as row labels (e.g., Region, Item Type).
• Columns Area: Typically used for fields you want to appear as column labels (e.g., Sales Channel, Order Priority).
• Values Area: This is where you put fields containing numerical data that you want to summarize (e.g., Total Profit, Units Sold). By default, Excel often performs a Sum on numeric fields dragged here, or a Count if the field contains text or dates.
• Filters Area: Fields dragged here create report-level filters at the top of the pivot table, allowing you to filter the entire report by selections from that field (e.g., filtering by specific Countries or Order Dates).

You can easily change the layout by dragging fields between these areas. Dragging a field outside the pane removes it from the pivot table.

• Aggregating Data: The default aggregation (Sum or Count) can be changed.
• Right-click on any value in the column you want to change the aggregation for.
• Select Value Field Settings.
• In the Summarize values by list, choose a different calculation like Average, Max, Min, Product, Count Numbers, etc..
• Click OK. You can also access Value Field Settings by clicking the drop-down arrow next to the field in the Values area.
• You can combine different methods of aggregation by dragging the same field into the Values area multiple times. Each instance can then be summarized using a different calculation (e.g., one column showing Sum of Total Profit, another showing Average of Total Profit).
• Renaming Fields/Headings: You can change the default headings in the pivot table report area (like ‘Row Labels’ or ‘Sum of Total Profit’) by double-clicking the cell and entering a new custom name. Note that renaming a heading in the pivot table report updates the name in the Values area of the fields pane, but the original field name above remains unchanged.
• Number Formatting: To ensure formatting (like currency symbols and decimal places) stays with the numbers when the pivot table layout changes, apply it via the pivot table’s specific options, not just standard cell formatting from the Home tab.

1. Right-click on a number within the column you want to format.
2. Select Number Format. Alternatively, access this via Value Field Settings > Number Format.
3. Choose the desired format (e.g., Accounting, Currency) and settings.
4. Click OK. This applies the formatting to all numbers in that value field.

• Handling Empty Cells: By default, pivot tables show blank cells where there is no data for a combination of criteria. This can affect charts or make the table harder to read. You can replace blanks with a value like 0:

1. Click inside the pivot table.
2. Go to the PivotTable Analyze ribbon, in the PivotTable group, click Options.
3. On the Layout & Format tab, under the Format group, check the box for For empty cells show: and enter the value you want to display (e.g., 0).
4. Click OK.

Grouping Data

Grouping allows you to combine items in your pivot table.

• Automatic Grouping: Excel automatically groups dates when you drag a date field into rows or columns. It analyzes the data and creates fields for years, quarters, and months if applicable. These automatically created fields (like ‘Years’ and ‘Quarters’) appear in the PivotTable Fields pane and can be used independently. You can expand/collapse these groups using the +/- buttons in the pivot table.
• Custom Grouping: You can create your own groups from non-date fields (e.g., grouping several Item Types into a ‘Food and Drink’ category).

1. Select the items you want to group by holding down Ctrl and clicking each item.
2. Go to the PivotTable Analyze ribbon, in the Group group, click Group Selection. Excel creates a new group (e.g., ‘Group1’) and a new field in the Rows/Columns area (e.g., ‘Item Type2’).
3. You can rename the group label in the pivot table (using F2 or double-clicking and changing the custom name in Value Field Settings) and rename the new group field in the fields pane (using Field Settings).

• Ungrouping: To reverse automatic or custom grouping, select an item within the group and click Ungroup in the Group group on the PivotTable Analyze ribbon.
• Inserting Blank Lines: To improve readability, especially with grouping, you can insert blank rows between groups. Go to the Design ribbon, in the Layout group, click Blank Rows, and select Insert Blank Line after Each Item. To remove them, choose Remove Blank Line after Each Grouped Item.

Layout Options

You can customize the overall appearance and structure of your pivot table report. These options are found on the PivotTable Design ribbon, in the Layout group.

• Subtotals:You can choose not to show subtotals at all.
• You can show them at the bottom of each group (often preferred) or at the top of each group (the default).
• Grand Totals:You can turn grand totals off for both rows and columns.
• You can turn them on for both rows and columns, only for rows, or only for columns. Turning them off is common when creating charts to avoid including totals.
• Report Layout: This changes how the fields are displayed in the report area.
• Compact Form: Optimizes for readability and uses space efficiently. It places subtotals at the top of groups and keeps related fields in the same column. This is the most compact view.
• Outline Form: Moves the innermost row field to a new column, creating a hierarchical structure where each field is in its own column. Subtotals appear at the top by default, but you can change their position.
• Tabular Form: Similar to Outline form, but adds grid lines within the pivot table, making it look more like a regular Excel table.
• Repeat Item Labels: In Outline or Tabular forms, you can choose to repeat the labels for outer row fields on every line instead of only showing them once. This can make the table easier to read in some cases or is necessary for certain chart types like map charts. You can turn this off if desired.

These options allow you to tailor the pivot table’s appearance to best suit your analysis and presentation needs.

Cleaning Data for Excel Pivot Tables

Data cleaning is a crucial process to undertake before analyzing large datasets, particularly when planning to use tools like pivot tables in Excel. It involves tidying up data sets, making them consistent, formatting them correctly, and presenting the data in a way that allows for easy and accurate analysis. Skipping this step, especially when importing data from external sources or databases, can lead to inaccurate analysis because data doesn’t always import in the expected format, potentially including columns out of place, strange formatting, blank rows, or duplicate entries.

Here are some of the key data cleaning techniques discussed in the sources:

• Removing Blank Rows Blank rows make data harder to read and can cause issues in pivot tables by being picked up as a “blank” entry. Manually deleting them row by row is tedious for large datasets. A quicker method involves selecting the data range, using “Go To Special” to select “Blanks,” and then using the “Delete Sheet Rows” command. Removing blank rows ensures the pivot table is accurate.
• Removing Duplicate Entries Duplicate rows, particularly where every column’s information is exactly the same, can sometimes occur when importing data and can cause problems for pivot tables. Excel’s “Remove Duplicates” utility can easily find and remove these exact duplicates. You can specify which columns to check for duplicates, but typically, you check all columns to find completely duplicated rows.
• Removing Unwanted Formatting Imported data may contain inconsistent formatting like background shading, bold text, or italics, which results in an inconsistent-looking worksheet. This formatting often isn’t desired. The “Clear Formats” option, found under the “Clear” button in the Home tab’s editing group, can quickly remove all applied formatting, including background shading, bold, italics, and number formatting, providing a clean slate. Other “Clear” options exist for different purposes, such as clearing only contents, comments/notes, or hyperlinks.
• Applying Desired Formatting After clearing unwanted formatting, applying consistent and appropriate formatting is important to make your data easier to read. This is referred to as number formatting but can be applied to any column, not just those containing numbers. The “Number group” on the Home tab provides standard options like General, Number, Currency, Accounting, and Date. Dates in Excel are stored as numbers (days since January 1, 1900), so applying a Date format (like Short Date or Long Date) is necessary to display them correctly. For numeric data, you can control decimal places using dedicated buttons or the “Format Cells” dialog box (Ctrl + 1). For monetary values, Currency and Accounting formats add symbols; Accounting format is often preferred as it aligns currency symbols and decimal points, enhancing readability for lists of numbers.
• Tidying Up Text Using Formulas Inconsistencies in text, such as case variations (uppercase, lowercase, proper case) or erroneous spaces (leading, trailing, multiple spaces between words), can negatively impact analysis. Excel provides text functions to standardize these:
• UPPER(), LOWER(), and PROPER() functions are used to change the case of text.
• TRIM() removes leading/trailing spaces and extra spaces between words.
• CLEAN() removes non-printing characters, which might appear as small square boxes, and can also remove manual line breaks within cells. These functions are typically used in a “helper column” next to the original data. Multiple functions can be combined in a single formula in a helper column to perform several cleaning steps at once, saving time.
• Using Paste Special to Convert Formulas to Values When cleaning data using formulas in a helper column, the formulas refer to the original data column. If the original column is simply deleted, the helper column will result in #REF! errors because the references are broken. To avoid this, the cleaned data in the helper column must be converted from formulas to static values. This is achieved by copying the helper column and then using the “Paste Special” > “Paste Values” option to paste only the resulting values over the original column (or a new location), discarding the underlying formulas. Once the values are pasted, the helper column can be safely deleted.
• Splitting and Combining Data Sometimes data is combined in a single cell that needs to be separated (e.g., “Order Date Order ID”), or data in separate cells needs to be combined.
• “Text to Columns” is a wizard that splits a single column of text into multiple columns based on a specified delimiter (like a comma, space, or other character) or a fixed width.
• “Flash Fill” is a faster tool (available since Excel 2013) that can split or combine data by recognizing patterns based on one or two examples provided by the user. It can be accessed via a button on the Data tab or the Ctrl + E shortcut.
• The CONCAT() function (or CONCATENATE() in older versions) joins text from multiple cells. Custom text or delimiters can be included in the joined result by enclosing them in quote marks within the function.
• Finding and Replacing Data To standardize inconsistent text entries (e.g., replacing “Democratic Republic of the Congo” with “DRC” or “United States of America” with “USA”), you can use the “Find and Replace” dialog box (Ctrl + F, then select the Replace tab). You specify what to find and what to replace it with, choosing whether or not to match the case. The SUBSTITUTE() formula can also perform find and replace using a formula, requiring the “Paste Special” > “Paste Values” trick afterward.
• Running a Spell Check Spelling errors can cause problems in pivot tables because the table will treat variations of the same word as completely separate items. Running a spell check (Review tab > Proofing group, or F7) helps ensure consistency in text entries. You can choose the dictionary language and add correctly spelled but unrecognized words to the dictionary.

Once data is cleaned, it is highly recommended to put it into an Excel Table before creating a pivot table. Excel Tables offer several advantages, including automatic formatting, built-in filter and sort buttons, and importantly, auto-expand capabilities. This means that if new data is added to the table, it is automatically included in the data source for any associated pivot tables or charts, which can then be updated by simply clicking the refresh button. Data can be converted into an Excel Table using the “Format as Table” option on the Home tab or the Ctrl + T keyboard shortcut. Tables can be given meaningful names for easier identification.

In summary, thorough data cleaning is essential for accurate and effective analysis using pivot tables, addressing issues like inconsistencies, errors, and formatting problems through various Excel tools and functions.

Excel Data Analysis with Pivot Tables

Based on the sources, data analysis is the process of summarizing large amounts of data to make sense of them. In a data-driven world where information is collected from various sources, simply looking at a large spreadsheet might not highlight key metrics, issues, successes, failures, or trends. Data analysis aims to take this data and present it in a way that allows for clearer understanding and better business decisions.

Excel provides powerful tools for data analysis, particularly Pivot Tables.

Key aspects of Data Analysis discussed in the sources:

1. The Role of Pivot Tables Pivot tables are described as an interactive and dynamic way to quickly summarize large amounts of data. Unlike static Excel tables where analysis is limited primarily to sorting and filtering, pivot tables allow you to pivot fields around and view data in all different ways. This dynamism makes it much more efficient to analyze data compared to manually using filters. Pivot tables help analyze large datasets in a clear and effective way. They facilitate asking questions about the data, such as finding top performers or seeing counts of high-priority orders. Pivot charts can be created from pivot table data to offer visual analysis options, as most people find it easier to analyze and interpret data visually. This can extend to creating interactive dashboards with filters for deeper analysis.
2. The Critical Need for Data Cleaning Before Analysis A central theme is that data cleaning is essential prior to analyzing data with a pivot table. Skipping this step, especially when importing data from external sources or databases, can lead to inaccurate analysis. Data doesn’t always import in the desired format, and inconsistencies or errors can cause problems for pivot tables. Cleaning ensures the data is tidied up, consistent, correctly formatted, and presented in a way that allows the pivot table to easily analyze it and produce accurate results. The sources highlight cleaning steps like removing blank rows, removing duplicate entries, clearing unwanted formatting, applying desired formatting, tidying text using formulas (case, spaces), splitting and combining data, finding and replacing data, and running a spell check. All these steps contribute to a “clean looking data set ready for analysis”.
3. Structuring Analysis with Pivot Table Fields To perform analysis with a pivot table, you use the Pivot Table Fields pane, which lists the column headings from your source data. These fields are dragged into four areas: Filters, Columns, Rows, and Values. These areas determine the layout of the pivot table and control the type of analysis being done. Placing fields in different areas changes how the data is summarized and viewed.
4. Aggregating Data for Analysis The Values area is typically where numeric fields are placed. By default, Excel usually performs a sum calculation for numeric values and a count for text or date fields dropped into this area. However, you can change how the data is summarized using the Value Field Settings. This allows you to choose from various aggregation methods, including Sum, Count, Average, Max, Min, Product, and more. You can even combine different aggregation methods (like sum and average) for the same data by dragging the field into the Values area multiple times and setting a different calculation for each instance. This ability to calculate averages, mins, or maxes “on the fly” expands the analysis beyond what was present in the raw source data.
5. Grouping Data for Deeper Analysis Grouping data is another way to analyze it. Excel automatically groups certain fields, like dates, into categories like years, quarters, and months. This allows you to see the data summarized at different levels (e.g., total profit by year, then by month within each year). You can also create your own custom groups for non-date fields to categorize data according to your analysis needs (e.g., grouping different item types into “food and drink” or “other”). Grouping allows for analyzing data in “multiple dimensions” by adding more fields to the Rows or Columns areas.
6. Handling Empty Cells and Layout How empty cells are displayed affects the accuracy of analysis, especially in pivot charts. Replacing blank cells with zeros in the Pivot Table Options ensures that items with no data are still represented, showing a zero value rather than being excluded from the analysis or charts. Additionally, the report layout options (compact, outline, tabular) and the choice to display or hide subtotals and grand totals affect the readability and presentation of the analyzed results.

In summary, data analysis in Excel, as presented in the sources, relies heavily on the dynamic capabilities of Pivot Tables, which allow for summarizing, slicing, dicing, and aggregating data in various ways. However, the foundation of accurate analysis is thorough data cleaning, ensuring the data is reliable and free from inconsistencies before being used in a pivot table. Using Excel Tables is also recommended as it makes managing and updating the data source for analysis more efficient.

Grouping Data in Excel Pivot Tables

Based on the sources, grouping data in Excel pivot tables is a way to summarize data by multiple fields and organize the display of that data. It allows you to analyze information at different levels or categorize data according to specific needs.

Here are key aspects of grouping data discussed in the sources:

• Automatic Grouping Excel will automatically apply grouping when you summarize data by more than one field in areas like the Rows or Columns of a pivot table.
• Date Grouping A common example of automatic grouping occurs when you drag a date field into an area like Rows. Excel looks at your source data and automatically groups the dates by categories such as years, quarters, and months. These levels appear as separate fields (e.g., “Years,” “Quarters,” “Order Date”) in the Pivot Table Fields pane. You can then use these fields independently to summarize data at different granularities, for instance, viewing total profit by year, and then expanding to see the breakdown by month within each year. If you don’t need a specific level, like quarters, you can simply remove that field from the Rows area. The “Group Field” option on the Pivot Table Analyze ribbon shows the date ranges and the levels (months, quarters, years) that Excel has pulled from the data.
• Custom Grouping You can create your own custom groups for fields that are not dates. This allows you to categorize data based on your analytical requirements. For example, you could select several ‘item type’ categories like ‘baby food’, ‘beverages’, ‘cereal’, ‘fruits’, ‘meat’, ‘snacks’, and ‘vegetables’ and group them together under a new name like “Food and Drink”. The remaining items could be grouped under “Other”.
• Creating Custom Groups To create a custom group, you select the specific items in the pivot table report that you want to include in the group. Then, you go to the Pivot Table Analyze ribbon and select the Group Selection button. Excel will create a new group (initially named generically, like “Group1”). You can rename this group directly in the pivot table report. Excel also creates a new field in the Pivot Table Fields pane corresponding to this custom group (e.g., “Item Type2” if you grouped based on ‘Item Type’). It is recommended to rename this new field as well (e.g., “Food and Drink”) for consistency. This can be done by clicking the drop-down arrow for the field in the Rows area and selecting “Field Settings,” or by right-clicking the field name in the Rows area and selecting “Field Settings”.
• Expanding and Collapsing Groups When grouping is applied, items in the pivot table report often display with little plus and minus symbols next to them. These symbols allow you to collapse or expand the details within a group, letting you focus on summary levels or drill down into specifics. You can toggle the display of these buttons on or off from the Pivot Table Analyze ribbon in the Show group.
• Multi-Dimensional Analysis Grouping contributes significantly to creating multi-dimensional pivot tables. By adding more fields and grouping them in the Rows or Columns areas, you can analyze your data by multiple factors simultaneously (e.g., analyzing profit by region, item type, and sales channel).
• Ungrouping Data If you need to revert a group, you can select an item within the group in the pivot table and click the Ungroup button on the Pivot Table Analyze ribbon.
• Grouping and Layout The report layout options can interact with grouping. For example, the Compact Form layout maintains the grouping structure. Adding blank rows using the “Blank Rows” option on the Design ribbon will insert a blank line after each grouped item, which can help emphasize groups and improve readability.

Excel Number Formatting Explained

Based on the sources and our conversation, number formatting is a crucial aspect of data cleaning and analysis in Excel, particularly to improve readability and consistency of your data. It involves ensuring that values in your cells are displayed in a way that accurately reflects their type and makes them easy to interpret.

Here’s a breakdown of the key points about number formatting discussed:

1. Purpose of Number Formatting:

• To make your data a lot easier to read.
• To ensure consistency in how numbers are displayed, such as the number of decimal places and the presence of currency symbols.
• A currency symbol, for example, always makes monetary values a lot easier to read.

1. Applying Formatting in Standard Worksheets:

• Formatting is applied using the Home tab in the Number group.
• A drop-down menu provides common formatting options (e.g., General, Number, Currency, Accounting, Short Date, Long Date).
• You can access more detailed formatting options by clicking “More Number Formats” at the bottom of the drop-down or by using the Ctrl+1 keyboard shortcut to open the “Format Cells” dialog box.
• The appropriate format depends on the type of information in the column.
• Examples discussed include:
• Applying Text formatting to columns containing text.
• Applying Date formatting to columns containing dates. Excel stores dates as numbers (days since January 1, 1900), and date formatting is needed to display them as calendar dates. If not formatted as a date, you might see the underlying numeric value. “Short date” and “long date” are common options. Custom date formats are also available via “More number formats” but are considered advanced.
• Applying Number formatting to columns like “Units Sold,” where you might need to control the number of decimal places (e.g., reducing to zero using the Increase/Decrease Decimal buttons or “Format Cells”).
• Applying Currency or Accounting formatting to monetary columns like “Unit Price,” “Total Revenue,” or “Total Profit” to add a currency symbol and control decimal places. The key difference is that Accounting format aligns the currency symbols and decimal points in a column, which is often considered easier to read, especially in long lists of numbers, whereas Currency format places the symbol right next to the value and doesn’t align decimals. The sources suggest Accounting format is frequently used.

1. Formatting and Data Cleaning Steps:

• When initially cleaning data, steps like using “Clear Formats” can remove all formatting, including desirable number formatting. Therefore, you might need to reapply the correct formatting after this step.
• Helper columns created for text cleaning formulas (like UPPER, TRIM, CLEAN, SUBSTITUTE) might inherit the formatting of surrounding columns, sometimes defaulting to “Text”. To see formula results correctly, these columns might need to be changed back to “General” format before applying the formula.
• Identifying numbers stored as text is important. Indicators include the number being aligned to the left side of the cell and a little green triangle in the corner. You can convert these using the warning symbol option “Convert to Number” or by using the VALUE formula.

1. Number Formatting in Pivot Tables:

• When you build a pivot table, the numbers in the values area are initially unformatted and inconsistent.
• It is NOT recommended to apply number formatting directly to the cells in a pivot table using the Home ribbon. This is because pivot tables are dynamic; the fields and their locations can change when you rearrange or “pivot” the data. Formatting applied to a static cell will not move with the number it was applied to if the layout changes.
• The correct method for applying number formatting in a pivot table is to apply it to the number itself, which ensures it moves with the data regardless of the layout.
• This is done by right-clicking on a number within the pivot table and selecting “Number Format”.
• Alternatively, you can access this through the Value Field Settings for the specific field in the Values area, and then clicking the “Number Format” button at the bottom.
• Both methods open the familiar “Format Cells” dialog box, allowing you to choose formats like Accounting or Currency.
• Custom number formatting is also available through this pivot table method.
• If you configure your pivot table to show zero for empty cells, these zeros will also display with the number formatting applied to that values field (e.g., showing “$ -“).

In essence, applying consistent and appropriate number formatting is a vital step, first during general data cleaning and preparation, and then specifically within pivot tables using the recommended methods to maintain accuracy and readability as you analyze your data.

By Amjad Izhar
Contact: amjad.izhar@gmail.com
https://amjadizhar.blog

Affiliate Disclosure: This blog may contain affiliate links, which means I may earn a small commission if you click on the link and make a purchase. This comes at no additional cost to you. I only recommend products or services that I believe will add value to my readers. Your support helps keep this blog running and allows me to continue providing you with quality content. Thank you for your support!