Excel is a spreadsheet that users often force into datasheet mode because they don't know how to use Access. If you're going to use Excel as a database, you need to store your data in database terms. The main clue that an Excel sheet isn't structured for flexibility is having lots of blank cells. In this article, we'll fix a data set that's full of holes—and consequently, problems.
I'm using Office 2016 on a Windows 10 64-bit system, but you can apply this article to earlier versions. You can work with your own data or download the demonstration .xlsx or .xls file.
The sheet in Figure A is a good example of data that only seems sound. Teachers are using this sheet to track academic awards presented to students. Unfortunately, the structure limits each award to five recipients. If the teachers decide to present a sixth award, they'll need a new column. On the other hand, not every award has a recipient, and there are lots of blanks. (I'm working with the Table object, but you can work with a range instead.)
When considering structure, there are two clues that point to problems:
- A lot of blank cells
- Adding a column to accommodate existing data
In the case of our example data set, each award should be a record of its own, whether you use Excel or Access.
This sheet limits each award to only five recipients.
Let's see how well the structure, as is, works with the PivotTable feature:
- Click anywhere inside the data set. Click Insert (on the Home tab) and the click PivotTable in the Tables group. The resulting dialog shows the default settings (Figure B). If you're using an older menu version, you'll find PivotTable on the Data menu.
- You don't need to change the default settings, so click OK.
- In the resulting frame, drag the Award field to the Rows control (Figure C). So far, so good.
- Now, drag Recipient 1 to the Columns control—that can't be right. Adding all the recipient fields doesn't help. (Figure D).
- Moving the recipient fields to the Values area doesn't work out much better (Figure E).
Excel does a good job of anticipating settings.
Dragging the Award field to the Rows control looks okay.
Something has clearly gone awry.
It doesn't matter where you put the fields, the results aren't meaningful.
A flexible structure
The problem is having multiple recipient fields. Figure F shows the reworked structure, which has one row (or record) for each award. This structure might not look like a spreadsheet, but you don't need a spreadsheet for this task.
The reworked structure looks like this.
Now, using the instructions above, base another PivotTable on the newly structured data. There are several possible layouts; Figure G displays the awards in the Columns area and the recipients in the Rows area. You can move fields and almost every arrangement will make sense, whereas before, nothing worked. You can also see how many awards by subject are awarded and how many awards are going to each student.
This is only one layout for the newly structured data.
The data set still contains a blank row—the teachers won't present any student with a history award. The default settings won't omit this record in the PivotTable. Instead, the PivotTable displays a blank item. Not only does the PivotTable display it, it counts it. This may or may not be what you want.
Fortunately, you can quickly filter out the blank record. From the Row Labels dropdown, choose (blank) at the bottom of the list, as shown in Figure H. As you can see in Figure I, that empty row is gone, and the grand total updates from 14 to 13, accordingly.
Uncheck the (blank) item in the dropdown filter list.
Remove the blank row for the history award.
When you find features aren't working as expected or your reporting needs can't be met, your solution often lies in rethinking the data's structure. Blank cells aren't wrong, but they often lead to problems.
- An Excel conditional format that expands with grouped data
- How to choose the right hyphen character in Word
- Office Q&A: An advanced Excel filter to match multiple values and a PowerPoint picture timesaver
- How to use PowerPoint 2016's stunning new Morph transition
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.