Ashkan Khalili is a personal technology columnist, and founder of Green Media System and Global Career Organization, a widely-read tech and how-to blog since 2001. Email: director@global-career.org
Use Excel's new data modeling features to help you turn data into meaningful business information.
If
you’re importing foreign data or sharing a workbook, the data often
ends up in several sheets. As a result, turning data into meaningful
information can be difficult. That’s where Excel 2013’s new data
modeling capabilities can help even casual users. By building a
relationship between sheets, Excel 2013 makes summarizing data spread
across multiple sheets easy.
Putting this new feature
into practice is easy, but it works mostly behind the scenes. As a
result, it can be difficult to get a handle on how to implement it. In
the next 10 steps, we’ll define a reporting need and meet it using Excel
2013's new data model.
This article is for users
unfamiliar with the feature and trainers supporting Excel users. This
feature isn't for serious database developers, and please don't blast me
for Microsoft's unfortunate choice for naming this new feature.
1. Defining the need
Figure A
shows two sheets with related data. As you can see, each record in
Daily Totals contains a value that’s further described by date,
personnel, and city. The records in Sites list the cities in each
region. Now, suppose you want to total the values in Daily Totals by
region.
Figure A
These two sheets contain records that are related.
Working through your data won’t always be this easy. I’ve purposely kept this example simple to keep the focus on the steps.
2. Convert data to tables
To
use Excel's data model, convert the data into tables. When working with
your own data, make sure your data set has a row of headers. Our data
sets already have a row of headers. To convert data to a table, do the
following:
Click anywhere inside the data set and click the Insert tab.
Click Table in the Tables group.
In the resulting dialog box, shown in Figure B,
click OK. In this case, the header option is already checked. When
applying this technique to your own data, you’ll want to check or
uncheck this option, appropriately.
Figure B
Convert your data to a table.
Use
the above steps to convert both data sets. Naming the new tables isn’t
necessary, but it will make working with them easier. To that end, click
inside a table and click the contextual Design tab. Then, enter a
meaningful name in the Table Name field, as shown in Figure C. Name them DailyTotalsTable and SitesTable.
Figure C
Named tables are easier to work with.
3. Finding the related data
There’s
no regional information in the data set that contains the values you
want to summarize. With data modeling, that’s not a problem. All you
need is a relationship between the table with the values you want to
summarize and the regional data you’ll use to summarize those values. A
relationship is a connection between two tables based on a single column
in both. In other words, when two data sets share a similar column of
data, they are related by that common column. In the case of our
example, the City column relates the two data sets.
4. Create the relationship
Creating
a relationship between two data sets is new to Excel, but don’t let
that worry you—it’s easy. To create a relationship between the two
tables do the following:
Click the Data tab.
Click Relationships in the Data Tools group. (If this option is dimmed, return to #2 and create the tables.)
From the first Table dropdown, choose DailyTotalsTable.
In the Column (Foreign) dropdown, choose City.
In the Related Table dropdown, choose SitesTable.
In the Related Column (Primary) dropdown, choose City, as shown in Figure D.
Click OK.
Click Close to return to the sheet.
Figure D
Specify the column that both columns share to create a relationship between the two tables.
5. Generate a blank PivotTable
To
summarize the values, we’ll generate a PivotTable. Click inside
DailyTotalsTable and click the Insert tab. In the Tables group, click
PivotTable. When Excel displays the dialog shown in Figure E, click OK.
Figure E
Generate a blank PivotTable in a new worksheet.
You
can use the new Recommended PivotTables option, but it won’t consider
your second table, so it’s easier to start with a blank table.
6. Add the second table
Currently, the PivotTable frame evaluates only one table, DailyTotalsTable. Add SitesTable as follows:
Click the MORE TABLES link shown in Figure F.
In the resulting dialog, click Yes. Doing so engages the new data modeling feature.
As you can see in Figure G, both tables are now part of your PivotTable structure.
Figure F
Clicking MORE TABLES will engage the data modeling feature.
Figure G
Both tables are now available to you.
7. Add fields
It's
time to start adding fields to the PivotTable. First, click the expand
arrow to the left of DailyTotalsTable to see its fields. Check Value and
City. Then, use the scroll bar to access SitesTable. Expand its fields
and select Region. Doing so adds these fields to the PivotTable frame,
as shown in Figure H.
Figure H
Thanks to the data modeling feature, you can add fields from both tables to the PivotTable frame.
8. Add regions to summarize
At this point, the PivotTable probably won’t be perfect, so it’s time to start tweaking a bit. Figure I shows the result of dragging the Region field to the COLUMNS section.
Figure I
Adding Regions summarizes the values accordingly.
9. A quick switch
If
you don’t like that layout, you can quickly switch the column and row
headings. Simply drag the City field to the COLUMNS section and the
Region field to the ROWS section, as shown in Figure J.
Figure J
Switching the rows and columns is easy.
10. Oops!
If
you’re paying close attention, you might have noticed the (blank) row.
Can you guess where that came from? By reviewing the city headings for
those items, you can quickly troubleshoot the problem. Using Figure A,
you can determine that there’s no region value for Boston and Cleveland.
Fortunately, it’s a quick fix. Add the values to SitesTable, as shown
in Figure K.
Figure K
Add the missing regions for Boston and Cleveland.
After
adding the new regional records, refresh the PivotTable. To do so,
click inside the table and then click the contextual Analyze tab. In the
Data group, click Refresh. Figure L shows the refreshed PivotTable.
Figure L
After refreshing the table, it displays the regional values correctly.
11: Bonus
Did you notice the little magnifying glass icon in Figure L?
Clicking that will let you drill deeper into the details that aren’t
currently visible. Clicking this icon with the Cleveland value selected,
displays the dialog shown in Figure M. Figure N shows the results of drilling to the City, the cities in the Central region.
Figure M
This dialog defaults to SitesTable, but you can switch to DailyTotalsTable.
Figure N
Drilling offers quick access to unseen details without creating a new PivotTable.
0 comments:
Post a Comment
Appreciate your concern ...