Book Image

Data Analysis and Business Modeling with Excel 2013

Book Image

Data Analysis and Business Modeling with Excel 2013

Overview of this book

Table of Contents (18 chapters)
Data Analysis and Business Modeling with Excel 2013
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
10
Creating Interactive Spreadsheets Using Tables and Slicers
Index

Preparing data


Now that we have gathered our data, we can start asking questions of interest about our dataset. For example, we can ask how much money did Fast Forms make in 2014? To answer this question, we will start by using the well-known VLOOKUP function, and then, we will use the exciting new data model's features.

The pmthistory table has all of the applications that were submitted in 2014. It does not have the dollar amount that was charged to the customer. It does have a column named rateplanid and this column points to the table called rateplan that will tell us the dollar amount we are looking for. In the database world, the rateplanid column in the pmthistory table is known as a foreign key. The column id in the rateplan table is known as the primary key. Let's create a VLOOKUP function to pull the price column from the rateplan table into the pmthistory table:

  1. Select the pmthistory tab and type price into cell G1, as shown here:

  2. In cell G2, type =vlookup and then select cell D2...