CHAPTER 1
Preparing the Data for an Excel Pivot Table

If you are like most office workers, you probably have tons of data coming from all directions that you somehow must summarize and make it all make sense. Maybe it is endless lists of sales, bills, invoices, customers, vendors, employees, benefits, payments, orders, products, inventory, collections, books, charges, or countless other possible lists. Additionally, it seems that the lists of information come from all different sources, and it never ends.

When I started in IT a long time ago, it was pretty much up to the IT team to gather all the data and then make reports and charts from the data. At the time, programming languages such as COBOL, Fortran, Basic, Pascal, ColdFusion, VBA, dBase, FoxPro, and others were used to write long, complex programs that would open the data file, go through the file record by record, clean up the data if necessary, accumulate totals, and then finally generate the reports or charts that were asked for. It was a time-consuming process that was prone to errors and many other challenges.

For most companies, those days are long gone. Now it is up to you, the individual, or the people you work with to gather all the data from different sources and make some kind of sense out of it. Somehow you are expected to know how to sort, filter, summarize, chart, and report on the data for the next staff meeting to show something meaningful from the data. Oh, and, by the way, the meeting is this afternoon! No pressure. It's only your job, your career, your life!

What are you going to do?

Relax, it's going to be OK. This whole book is designed to help you make sense of all that data, and you don't even have to be a programmer to get great results from your data. This book will show how to create and manage a pivot table, which is a powerful reporting tool built into Excel. A pivot table can take in huge amounts of data, and it allows you to summarize your data just about any way you want, all without you having to be on the IT team. By using the steps in this book, you really should be able to get great results from your data by using pivot tables.

NOTE This chapter will present a number of examples. To get the most from these examples, you can download sample files from www.wiley.com/go/GGRXL_PivotTables . The examples throughout the chapter will note which book file is being used.

What Is Data?

For the purposes of this book, data can be defined as a list of rows of information or transactions that have a common theme. The data itself could represent any number of lists of information. It could be names of customers, employees, teachers, students, grades, bills, invoices, inventory, sales, credits, debits, investments, addresses, cities, countries, and so on and so on. The data could be any list of items that people keep track of. It could be a short list with just a few rows, or it could be a long list with hundreds of thousands of rows. Microsoft Excel spreadsheets can hold more than 1,048,000 rows down and more than 16,000 columns across. Larger databases, going into the hundreds of millions of rows, can be managed in Power Pivot, which is discussed in Chapter 8, "Improving Your Pivot Table with Power Pivot." Each row in the list within the Excel worksheet list is a separate transaction or record. Each column is a different field of information.

What the Data Should Look Like

For the data to be used in a pivot table, the data has to be set up in a certain way so it will be optimized for the pivot table. If necessary, Power Query, which is discussed later in this chapter, along with other traditional Excel techniques, can be used to clean the data. Figure 1.1 shows an example of "good" data, and Figure 1.2 shows an example of "bad" data. The following is a list of ways the data should be structured so it is ready to be made into a pivot table:

• It is important that the column headings or field names appear on the top row of the data. The row that contains the field names is also called the header row.
• There should be only one row for the column headings. Excel will use the top row of the list as the headers.
• If the data you want to use does not have a header row, then you should take the time to manually insert a row at the top of the data and give each column a meaningful name.
• The header row does not necessarily have to be row 1 of the Excel spreadsheet, but it does have to be the first row of the list of data. Similarly, the first column of information does not have to be in column A of the Excel spreadsheet; it just has to be the first column of the list.
• If there is anything above the header row, then there has to be at least one completely blank row before the header row, and if there is anything before the first column of data in the list, there has to be at least one blank column before the first column of data in the list.
• After the header row, there should be no entirely blank rows until the bottom of the data, and there should be no entirely blank columns until the right side of the list. The reason for this is when the pivot table is first made, Excel will automatically select a range of data, and it will stop at the next completely blank row it finds above the current cell and also below the current cell, and the next completely blank column it finds to the left of the current cell and to the right of the current cell.
• There can be blank cells here and there, but an entire row or column should not be blank within the list of data.
• Each separate transaction should take up only one row of the Excel spreadsheet.
• Each column should have just one piece of information, and it should be consistent all the way up and down the column. A column called Country, for example, should contain only names of countries.
• The column should have the same data type, for example being all text, numbers, or dates within the same column.
• The list of data should not contain any merged cells. A merged cell is when more than one cell is combined into one big cell. These merged cells really get in the way of a successful pivot table.

Figure 1.1: An example of good data

Figure 1.2: An example of bad data

NOTE Checking for Merged Cells

The following is a quick way to see if your list of data has any merged cells:

1. Click one of the cells within the data range.
2. Use the Ctrl+A keyboard shortcut to select the entire range of data.
3. Click the Home tab.
4. Click the Find icon, as shown in Figure 1.3.

   Figure 1.3: Selecting the Find icon

5. In the Find And Replace dialog box, click Options. This will show a list of options, as shown in Figure 1.4.

   Figure 1.4: Clicking Options

6. Click Format. This will display the Find Format dialog box, as shown in Figure 1.5.

   Figure 1.5: The Alignment tab in the Find Format dialog box

7. Click the Alignment tab.
8. Select the Merge Cells option so that it is checked.
9. Click OK.
10. Click Find All. This will highlight any merged cells within the list.
11. Close the Find And Replace dialog box.
12. If any merged cells were found within the list of the data, then highlight the list of data, right-click the range, and then use the Format Cells option to unmerge the cells within the list.

Types of Data That Can Be Used in Excel Pivot Tables

The data for a pivot table can potentially come from many sources. The data can already be in Excel, or it can come from external sources. Data external to Excel would eventually have to be imported into an Excel worksheet. If there are more than 1,048,000 rows in the table, however, it would have to be imported into Power Pivot instead, which will be discussed in Chapter 8, "Improving Your Pivot Table with Power Pivot". The following are the most popular data sources that can be imported into Excel and then made into a pivot table:

• An Excel workbook
• A text or CSV file
• An XML dataset
• A JSON dataset
• A PDF file (not preferable, but it could possibly work if it is the only thing available)
• A SQL Server database
• An Access database
• An Oracle database
• An IBM DB2 database
• A MySQL database
• A PostgreSQL database
• A Sybase database
• A Teradata database
• A SAP HANA database
• Data from Azure
• Data from PowerBI
• Data from Dataverse
• Data from Dataflows
• A SharePoint list
• Microsoft Exchange
• Microsoft Dynamics 365
• Salesforce
• A website that has a table of data
• An OData feed
• A Hadoop file
• An ODBC data source
• An OLEDB data source

There are other possible data sources, but these are the main ones that are being used. Additionally, most of the widely used databases are ODBC compliant, OLE compliant, or both, so that almost every popular database can be a source of data that can be imported into Excel and then used as the main data for a pivot table. If the database you are using is...