Excel 2019

Data Analysis

The Computer Workshop, Inc.

800-639-3535
[Link]
training@[Link]
Lesson Notes
 Excel 2019

Data Analysis

Course Number: 0200-405-19-W

Course Release Number: 1
Software Release Number: 2019

5/6/2020

Developed by:
Brian Ireson
Suzanne Hixon
Thelma Tippie

Edited by:
Jeffery DeRamus
Cheri Howard

Published by:
RoundTown Publishing
5131 Post Road, Suite 102
Dublin, Ohio 43017

for

The Computer Workshop, Inc.

5200 Upper Metro Place, Suite 140
Dublin, Ohio 43017
(614) 798-9505

Copyright © 2020 by RoundTown Publishing. No reproduction or transmittal of any part of this publication,
in any form or by any means, mechanical or electronic, including photocopying, recording, storage in an
information retrieval system, or otherwise, is permitted without the prior consent of RoundTown Publishing.

Disclaimer:
Round Town Publishing produced this manual with great care to make it of good quality and accurate, and
therefore, provides no warranties for this publication whatsoever, including, but not limited to, the implied
warranties of merchantability or fitness for specific uses. Changes may be made to this document without
notice.

Trademark Notices:
The Computer Workshop, Inc. and The Computer Workshop logo are registered trademarks of The
Computer Workshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], and [Access] are registered
trademarks of Microsoft Corporation. [Photoshop] and [InDesign] are a registered trademark of Adobe. All
other product names and services identified throughout this book are trademarks or registered trademarks
of their respective companies. All NASA information was obtained from public resources. Using any of
these trade names is for editorial purposes only and in no way is intended to convey endorsement or other
affiliation with this manual.
Preface

Table of Lesson 1: Tables

Contents Tables.........................................................................................3
Creating a Table........................................................................4
Using Home Tab.................................................................4
Using the Insert Tab...........................................................5
Using the Quick Analysis Tool........................................6
Table Components.................................................................10
Records and Fields.................................................................14
Resizing A Table.....................................................................15
Using the Resize Handle.................................................15
Resizing a Table Using the Design Ribbon...................15
Adding Fields.........................................................................17
Using the Right Click Insert Menu................................17
By Selecting an Adjacent Column.................................18
Using the Ribbon..............................................................18
Adding Records......................................................................19
Adding Records Inside the Table...................................19
Adding Records at the End of the Table.......................19
Deleting Records or Fields....................................................20
The Total Row.........................................................................25
Adding a Total Row........................................................25
Hiding the Total Row......................................................26
Using the QAT to Create a Total Row...........................26
Data Forms..............................................................................28
Adding the Form Tool to the QAT................................28
Using a Form to Enter Records......................................29
Slicers.......................................................................................32
Adding Slicers to a Table................................................32
Formatting the Slicer........................................................33
Using the Slicer.................................................................33
Clearing a Slicer Filter.....................................................34
Closing or Deleting a Slicer............................................34

Lesson 2: Importing Data

Importing Data from other sources.....................................39
Using Excel and Access.........................................................40
Creating a Table from an Access Object..............................41
Using the Data Ribbon....................................................41
Setting the Refresh Properties........................................42
Importing Data Using the Microsoft Query Connection.46
Setting up a Querry Connection....................................46
Importing Data from a Text File..........................................55
Opening A Text File in Excel..........................................55
Importing a Text file into a Workbook..........................58

Page iv Excel 2019: Data Analysis, Rel. 1, 5/6/2020

 Preface

Table of Lesson 3: Data Management

Contents, Understanding Structured Data....................................65
continued Guidelines for Data Structure........................................65
Cleaning Up Raw Data....................................................65
Removing Blank Rows..........................................................66
Removing Blank Rows....................................................66
Removing Duplicates............................................................69
Remove Duplicates..........................................................69
Conditional Formatting...................................................72
Using Conditional Formatting To Find Duplicates....72
Comparing Two Lists With Conditional Formatting..73
Sorting Data............................................................................77
Applying a Simple Sort...................................................77
Basic Sorting in a Table....................................................78
Sorting on Multiple Fields..............................................81
Flash Fill..................................................................................85
Flash Fill to Combine or Separate Data........................85
AutoFilters...............................................................................89
Basic Filtering...................................................................89
Custom AutoFilters................................................................92
Creating a Custom AutoFilter........................................92
Using the Search Feature................................................94
Using Wildcards...............................................................94
Clearing Filters.................................................................95
Advanced Filter......................................................................97
Using an Advanced Filter...............................................97
Clearing the Filter............................................................98
Copying Filtered Records.....................................................99
Copying Filtered Records to a New Location .............99

Lesson 4: Database Functions

Database Functions..............................................................105
Basic Syntax of D-Functions...............................................107
Creating a D-Function Formula.........................................108
Entering the Function Manually..................................109
Expanding D-Functions .....................................................114
Adding Drop-down Menu's...............................................116
Data Validation Lists.....................................................116

Excel 2019: Data Analysis, Rel. 1, 5/6/2020 Page v

Preface

Table of Lesson 5: Data Modeling

Contents, Data Modeling......................................................................123
Enabling Power Pivot....................................................123
continued
Understanding Relationships.............................................126
Relational Databases......................................................126
Preparing the Tables............................................................127
Structuring the Data......................................................127
Convert the Data to a Table..........................................127
Rename the Table...........................................................127
Creating Relationships........................................................129
Find the Related Data....................................................129
Creating Relationships..................................................129
Managing the Data Model..................................................133
To Open Excel's Power Pivot window........................133
Power Pivot Views.........................................................134
Adding a New Connection ..........................................136
Creating PivotTables............................................................138
Creating PivotTables......................................................138
Working with a PivotTable.................................................140
Adding a Calculated Column............................................145
Inserting a Function column........................................145

Page vi Excel 2019: Data Analysis, Rel. 1, 5/6/2020

Preface

Using this Welcome to the Advanced Excel 2013 course. This manual and
the data files are designed to be used for learning, review and
Manual reference after the class. The data files can be downloaded any
time from The Computer Workshop website:
http:\\[Link]
There is no login or password required to access these files. You
will also find handouts and supplementary materials on the
website in the Download section.

To Download Data Files

Once on The Computer Workshops website, look at the bottom

of any page to find the link Download. Clicking this link opens
the Download page where you can choose either Data Files or
Handouts.
1. Data Files opens a list of general application types.
2. Click once on the Microsoft Office Courses link.
3. Click once on the software related to the course.
4. Click once on the version related to the course.
5. If there are multiple folders, click on the TCW folder.
6. Click on the course name to download the data files.

You can choose to open or save the zipped folders content to

your computer.

The handouts are in PDF format and also available to you

without login or password. Simply open the PDF and either
print or save to your computer.

Page vii Excel 2019: Data Analysis, Rel. 1, 5/6/2020

Preface

Conventions Conventions Used in this Manual

The hands-on exercises (Actions) are written in a two-column

format. The left column (“Instructions”) gives numbered
instructions, such as what to type, keys to press, commands
to choose from menus, etc. The right column (“Results/
Comments”), contains comments describing results of, reasons
for, quick keys, etc. for the instructions listed on the left.

›› Key names and Functions are bold and enclosed in

square brackets:
[Enter], [Tab], [F5], [F10]
›› Keys you press simultaneously are separated by a plus
(+) sign, typed in bold and enclosed in square brackets.
You do not press the plus.
[Shift + F5]
›› Keys you press in sequence are separated by a space,
bold and enclosed in square brackets.
[Home] [Down Arrow]
›› Ribbon tab names are in bold and italic: Example:
Home
›› Group names are in bold: Example: Font
›› Dialog box names are in italic: Example: Save As
›› Button names are bold and enclosed in square brackets:
Example: [Sort]
›› Information you are to type will be in bold. Example:
This is the first day of the rest of your life.
›› Information that you need to supply will be indicated
with pointed brackets. Example: Type: <your name>.

Page viii Excel 2019: Data Analysis, Rel. 1, 5/6/2020

Excel 2019: Data Analysis
Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data

Management
Lesson Overview

You will cover the following concepts in this

chapter:
›› Data Management
›› Locating Blanks
›› Removing Blank Rows
›› Removing Duplicates
›› Combining Cell Values
›› Splitting Cell Values
›› Flash Fill
›› Tables
›› Creating a Table
›› Autofilters
›› Advanced Filter
›› Data Forms
Lesson Notes
 Lesson 1: Tables & Data Management

Data Understanding Structured Data

Management While data in Excel can be laid out in many different ways some
analytical features require the data be in a specific structure.
As an example: creating tables, sorting, and /or filtering data
will not work properly if there are gaps in the data. Since Excel
recognizes adjacent rows and columns of data as a dataset, a
blank row or column indicates the end of the data set, which can
give partial views of the complete data set.

Guidelines for Data Structure

›› Only one row of labels for the header row.
›› Each column contains only one type of data.
›› Continuous rows and columns of data; no gaps and no
decorative rows or columns.
›› Break data down into the smallest value necessary for
sorting or filtering.
›› An address should be broken down into columns
Address | Appt | City | State | Zip
›› Each row of data represents only one record.
›› A spreadsheet containing a list of employees
personal information, one employee per row.
›› No duplicate rows of data.

Cleaning Up Raw Data

Before you are able to begin working with data, it may be
necessary to ensure there are no problems within the data.
Excel offers several tools to speed this process up significantly;
duplicate removal, splitting combined elements into component
data, and combining data into new columns of required
information. It is a good idea to quickly check for and correct
possible issues early on to avoid issues further down the road.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 3

Lesson 1: Tables & Data Management

Locating Blanks While removing blank rows can be easily managed, you may
need to see where the blanks are before removing the entire row.
This can be done for an individual column or the entire data set
by using the Conditional Formatting tool.

Conditional Formatting Blanks

›› Select the column or data set to be searched.
›› On the Home Tab in the Styles Group, click the
[Conditional Formatting] button drop-down.

›› Choose New Rule... from the menu.

›› The New Formatting Rule dialog opens.

›› From the list of Rule Type, select Format only cells

that contain.
›› In the Edit the Rule Description section, click the
first field drop-down and select Blanks from the list.

Page 4 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Locating Blanks, ›› Click the [Format...] button.

continued ›› The Format Cells dialog opens.

›› Activate the Fill Tab in the Format Cells dialog.

›› Choose an easily noticed color and click [OK].
›› Click the [OK] button to close the dialog and apply the
formatting.

Sorting Based On Cell Color

Note ›› If the Conditional Formatting has been applied in a
Sort tools can also
be accessed from single column, right-click a colored cell in the column.
the Home Tab in the
Editing Group in the
›› Choose Put Selected Color On Top from the Sort options
[Sort & Filter] button, or in the menu.
on the Data Tab in the
Sort & Filter Group.
- OR -
›› If the data set has been Conditionally Formatted,
right-click any cell in the data set.
›› Choose Custom Sort... from the Sort options in the
menu.
›› The Sort dialog opens.
›› Click the Sort by field drop-down and choose the
first column to sort by.
›› Click the Sort on field drop-down and choose Cell
Color.
›› Click the Order field drop-down and choose the
color and set the location to On Top.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 5

Lesson 1: Tables & Data Management

Locating Blanks, ›› Click the [Add Level] button.

continued ›› Repeat the setting for this level just as before.
(If there is no color listed in the Order field drop-down, change the Sort by
field value to the next column.)

›› Repeat until all columns containing colored cells

are included in the custom sort.
›› Then click the [OK] button to apply the sort.
You are now able to see what information is missing and decide
wether or not to remove the record.

Page 6 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Removing Blank Removing Blank Rows

Rows When managing the data, the first thing to consider is
eliminating any blank rows or columns which are breaking up
the data set. Using the Conditional Formatting tools to locate
individual blanks cells allow you to see what data can be
removed based on the entire record.

There are times when a columns' data is integral to a valid

record, a blank in this column on a row would completely negate
the entire record. This type of situation makes the need to search
every column for blanks unnecessary. In cases such as these,
removing rows based on a blank is eaily done by selecting the
blanks and deleting the entire row.

Selecting Blank Cells

›› Select a key column. (An ID column would be a prime
example.)
›› Open the Go To dialog by:
›› On the Home Tab, in the Edit Group click the Go To
Special option from the [Find & Select] drop-down
button.

›› Use the [F5] key to open the Go To dialog and click

the [Special] button.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 7

Lesson 1: Tables & Data Management

Removing Blank ›› In the Go To Special dialog, choose Blanks, and click

the [OK] button.
Rows,
continued

›› Any blank cells are selected.

›› On the Home Tab in the Cells Group click the
[Delete] drop-down and choose Delete Sheet Rows.

›› All blanks rows are removed.

Counting Blanks
To help in getting an idea of how many blanks exist within a data
set, you can use the Count, CountA, or CountBlank formulas.
›› Count: This returns the number of cell containing
numeric data from a range.
=COUNT(range1,[range2])
›› CountA: this function returns the number of cell
containing data from a range.
=COUNTA(rangee1,[range2])
›› CountBlank: This returns the number of empty cells
from a range.
=COUNTBLANK(range)
Page 8 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020
 Action 1:1 - Locating Blank Cells

Instructions: Results/ Comments:

1. Open the [Link] file. This is an example of messy data which
needs to be cleaned up before beginning to
work.

2. Save the file as [Link] [F12].

3. Active the MissingDataPoints

worksheet.

4. Select cell A8. The first cell in the data set.

5. Holding both the Ctrl and Shift keys Using the Ctrl And Shift keys allows for
down, tap the Right Arrow key once, quick and efficient directional selection.
then the Down Arrow key three times.

6. Activate the Home Tab. If necessary.

7. In the Style Group, click the The New Formatting Rule dialog opens.
[Conditional Formatting] button drop-
down and choose New Rule.. from the
menu.

8. Select the Format only cells that contain The options below in the Edit the Rule
in the list of rule types at the top of the Description section change, offering
dialog. control associated with the selection of
rule type.

9. In the first field, change the Cell Value to The controls change again to reflect your
Blanks. choice.

10. Click the [Format] button. The Format Cells dialog opens.

11. Activate the Fill Tab, choose a color, and This will be the color used to highlight
click the [OK] button. blank cells.

12. Click the [OK] button to apply the All blank cells are now highlighted.
formatting.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 9
 Action 1:1 - Locating Blank Cells, continued

Instructions: Results/ Comments:

13. Select cell A24. This is the cell the sort will be based on.

14. Right-click the colored cell, hover on the All the records are sorted with all blanks
Sort option and choose Put Selected Cell in the column A on top.
Color On Top from the menu.

15. Re-select all the data in the data set. Cells A8:N78.

16. On the Home Tab, click the [Sort & The Sort dialog opens. Using the right-
Filter] button drop-down, then select click method would also give access to
Custom Sort from the menu. the custom sort. This will allow you to set
blanks across all the columns top the top
of the data set.

17. The first level sort should already be set. If not then set the first sort level to Sort By
to Column A, the Sort On to Cell Color,
the Order to color, and leave the locate set
to On Top

18. Click the [Add Level] button. This will allow for a secondary search and
sort. Each new level will be run after the
previous level is completed.

19. Continue adjusting the parameters of All records with blank cells are shown at
each sort level. Add levels related to the top of the data set in descending order
columns A, D, E, H, and J. When done, of importance.
click the [OK] button.

20. Right-click the row header for row 8 and Since this record is invalid without an ID,
choose Delete from the menu. the record needed to be removed.

21. On the Home Tab, click the [Conditional All highlights are removed.
Formatting] button and choose to Clear
Rules from Entire Sheet.

22. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 10
 Action 1:2 - Removing Blank Rows

Instructions: Results/ Comments:

1. [Link] file should still If not, re-open the file.
be open.

2. Activate the InvalidRecords worksheet. In this case, any records without an ID

must be removed.
3. Select column A.

4. On the Home Tab click the [Find & Use the [F5] key to open the Go To dialog
Select] button drop-down in the Editing and click the [Special] button to open the
Group and choose Go To Special. Go To Special dialog.

5. Click the Blanks radio button and click Only blank cells are selected.
[OK].

6. On the Home Tab, click the [Delete] All the blank rows have been removed.
button drop-down in the Cells Group Note: if there are blank cells within the
and choose Delete Sheet Rows. data set doing this could remove records
from the data set. This is another reason
why having blank cells in the data can
cause problems.

7. Click into any cell containing data. To deselect the current selection.

8. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 11
Lesson 1: Tables & Data Management

Removing Remove Duplicates

Duplicates One feature that makes restructuring data simpler is Remove
Duplicates. It is available for regular or tabular data. It
examines selected data and removes duplicate lines based on
a repeated values within column values. Data can have empty
cells here and there, but the column value used to find duplicates
cannot have any empty cells. The first record found in the
Note process is maintained while all subsequent records are removed.
When removing
duplicates, only the
first instance of the Since only the first record is retained when a duplicate is found.
duplicate is retained. You can also consider using conditional formatting to identify
duplicates before removal to ensure the correct record is being
removed. More on this later.

›› Click any cell in the range of data that contains

duplicates.
›› For Normal Data:
›› On the Data Tab, click the [Remove Duplicates]
button in the Data Tools Group.

›› For Tabular Data:

›› On the Table Tools Design Tab, click the [Remove
Duplicates] button in the Tools Group.

›› In either case, the Remove Duplicate dialog opens.

Page 12 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Removing ›› If the data set has headers be sure to check the

Duplicates,
My data has headers checkbox. (It may already be
checked.)
continued
›› If you are looking for a complete duplication of a
record, leave all the Column checkboxes checked to
include them in the comparison.
›› If you are looking for certain aspects of the records
to be duplicates then click the [Unselect All]
button and check the box next to each column to be
compared.
›› Click the [OK] button.
›› A message box appears indicating the number of
duplicate rows to be removed and how many rows
will remain in the list. Click [OK].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 13

 Action 1.3 - Removing Duplicates

Instructions: Results/ Comments:

1. The MyEmployeeStart file should still If not, re-open the file.
be open.

2. Select the InvalidRecords sheet then Holding the [Ctrl] as sheet tab is dragged
click and drag it beside the original to a new position will duplicated the entire
sheet while holding the [Ctrl] key. spreadsheet. You should now see a second
sheet tab labeled as InvalidRecords(2).

3. Repeat step 2 to create a third copy of There should now be three copies of the
the sheet. same sheet in the workbook.

4. Save the file. [Ctrl+S].

5. Select the first InvalidRecords sheet tab. The original sheet is active.

6. Click any cell containing data. If necessary.

7. On the Data Tab, click the [Remove The Remove Duplicate dialog opens and all
Duplicates] button in the Data Tools connected cells are selected.
Group.

8. Ensure that the My data has headers If the data set does not have headers, then
checkbox is checked in the Remove the My data has headers checkbox should
Duplicates dialog. not be checked. Which will include the
first row within the search for duplicates.

9. Click the [Unselect All] button. All the check marks in the checkboxes
beside each column are removed.

10. Click the checkbox for Emp# and click This will be the only column being
[OK] the button. searched for duplicate entries. A message
window opens, stating; "7 duplicate values
found and removed; 64 unique values
remain."

11. Read the message and click the [OK]

button to close the message window.

12. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 14
 Lesson 1: Tables & Data Management

Removing When using the Remove Duplicate function, the first record of
many found will be the only one kept, all others are removed.
Duplicates, While this will work in most cases there will be times when
continued you need to see the duplicates in order to determine which is
the correct one to be retained. The Conditional Formatting tool
allows for this to be done in a quick and efficient manner.

Using Conditional Formatting To Find Duplicates

›› Select the range of cells to be searched and formatted.
Note ›› Think of this as selecting the column in the Remove
Select the top cell Duplicates dialog.
in the column and
hold the [CTRL] and ›› On the Home Tab, click the [Conditional Formatting]
[SHIFT] keys then button drop-down and choose New Rule from the
tap the down arrow key menu.
to extend the selection to
the last cell containing
data in the column.

›› In the New Formatting Rule dialog choose Format only

unique or duplicate values from the list of Rule Types.

›› In the Format All drop-down field choose Duplicate.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 15

Lesson 1: Tables & Data Management

Removing ›› Click the [Format] button.

Duplicates,
continued

›› In the Format Cells dialog, click the Fill Tab and choose
any color you want.
›› You can choose to apply any formatting changes to
the Numbers, Text, Borders, and/or Fill.
›› Click the [OK] button to close the Format Cells dialog.
›› Click the [OK] button to close the New Formatting Rule
dialog and apply the your formatting to the duplicate
values.
Once the duplicate values are formatted you can sort the data
set based on the cells color and examine the duplicate records to
determine which are the ones to be deleted. Select the unwanted
rows and delete them by right-clicking the selection and
choosing Delete from the menu.

Comparing Two Lists With Conditional Formatting

There will be times when you have data in two tables or
worksheets that require a comparison to find duplicate values.
This can be done by using a Countif formula within conditional
formatting.

›› Select the data in the column which may contain

duplicate values
›› Click the [Conditional Formatting] button drop-down
in the Styles Group on the Home Tab.
›› Choose New Rule from the menu.

Page 16 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Removing ›› The New Formatting Rule dialog opens.

Duplicates,
continued

›› Choose Use a formula to determine which cells to format in

the Select a Rule Type: field.

›› In the Format values where this formula is true: field,

enter the following formula
›› =countif(
›› Click the worksheet or table containing the
comparison data,
›› Select the cell range containing the data,
›› Type in a comma,
›› Click back to the first cell of the data being
formatted,

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 17

Lesson 1: Tables & Data Management

Removing ›› This will be an absolute address which needs to be

converted into a relative address. Use the F4 key
Duplicates, to cycle through the cell addressing until all $ are
continued removed.
›› Type in the ).
›› This will apply formatting to all matching cells.
›› Click the [Format] button to open the Format Cells
dialog.

Do not use ›› Choose what ever formatting options you want and
the [Arrow] click the [OK] button.
keys to move
forward or backwards ›› Click the [OK] button to apply the formatting to all
in the formula. It cells that match the other cell range.
will change the cells
being referenced in Now you are able to sort the data in the column based on cell
the formula. Use the color, select either the unwanted duplicates or unique value rows
mouse to reposition and delete them. Once your unwanted data has been removed
the cursor in the clear the Conditional Formatting.
formula if necessary.

Page 18 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 1.4 - Conditionally Formatting Duplicate Values

Instructions: Results/ Comments:

1. Select the InvalidRecords(3) sheet. This is the third sheet created earlier.

2. Select cell A2. The first cell containing the data to

be formatted, In this case the unique
employee ID number.

3. Hold the [Ctrl] and [Shift] keys then tap The rest of the column is selected.
the [Down Arrow] key

4. On the Home Tab, click the [Conditional The Conditional Formatting options are
Formatting] button drop-down in the displayed.
Style Group.

5. Choose New Rule... from the menu. The New Formatting Rule dialog opens.

6. Choose the Format only unique or The options related to the Format only
duplicate values option in the Select a unique or duplicate values are displayed.
Rule Type: field.

7. Choose Duplicate from the Format All You can choose to apply formatting to
field drop-down. either unique or duplicate values form the
drop-down.

8. Click the [Format] button. The Format Cells dialog opens.

9. Click the Fill Tab in the Format Cells The fill cells options are displayed.
dialog.

10. Choose any color from the list and click This will be the color applied when a
the [OK] button. duplicate is found. Choose a color that will
stand out from the rest of the formatted
data set.

11. Click the [OK] button in the New The dialog is closed and the formatting
Formatting Rule dialog. applied to all duplicates.

12. Save and close the file. [Ctrl+S] and [Ctrl+W].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 19
Lesson 1: Tables & Data Management

Combining Cell Data can come broken down into the smallest usable parts
but that may not what is required in the current file, it would
Values be better to re-combine the data into a single cell. Excel offers
several methods to assist in this type of undertaking; a simple
add formula, the new CONCAT function, or TEXTJOIN function.

Simple Combining Formula

This is a very basic formula used to combine content into a single
cell. If the contents are in cells, use the cell addresses.
First String Second String

=text1&text2&.....
Ampersand will join the elements
You can add strings of your own by wrapping the string in
quotation marks.

CONCAT Function
A new function which replaces the Concatenate function.
Although the Concatenate function will still work, ensuring older
files using that function continue to work as excepted.

The Concat function is used to combine text strings from multiple

cells. The text strings can be held in cells or added from within
the formula itself. Should a delimiter such as a blank space, be
required, it must be added within the formula.

Syntax
Function First String

=CONCAT(text1,[text2],.....)
Second String

If the string is held within a cell, the formula will use the cell
addresses. To add the subsequent strings, use a comma to
separate one string from the next.

Page 20 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Combining Cell Function First String Third String

Values,
continued =CONCAT("text1"," ", "text2")

Second String

When required text or a required delimiter is not in a cell,

wrapping it inside quotation marks will add those into the
results of the formula.

TEXTJOIN Function
Similar in function to the CONCAT formula, this can add a
delimiter directly into the returned value. Instead of having
to add a quoted space or comma to separate each string the
TEXTJOIN functions first argument allows you to define a
delimiter once..

Syntax
=TEXTJOIN(delimiter,ignore_empty,text1,.....)
›› Delimiter: as a text entry it should be held inside of
quotation marks. for a space you would enter- " "
for a comma with a space you would enter - ", "
›› Ignore_Empty: this will be either True or False. True
will ignore empty cells in the returned value while
False would add empty cells as blank spaces in the
formula results.
›› Text1,Text2,..: these are the cell addresses that are to be
joined by the formula.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 21

Lesson 1: Tables & Data Management

Combining Cell Case Functions

Values, When raw data has a mix to cases, creating issues of inconsistent
continued formatting, Excel has other text functions to help correct those
issue.
›› PROPER: will capitalize the first letter in each text
string.
›› UPPER: will capitalize the entire text string.
›› LOWER: will remove any capitals from the text strings.
These are often used to apply text formatting by nesting other
formulas inside of the argument. As an example, see the formula
below:

=PROPER(CONCAT(text1,text2))

Page 22 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 1.5 - Combining Cells

Instructions: Results/ Comments:

1. Open the CleanUp file from the data
files folder.

2. Select the Names sheet.

3. Select cell E3. You will combine the first name in this cell.

4. Enter the following formula: This is a simple combination formula.

=A3&", "&B3&" "&C3 Using the [Ctrl+Enter] keys applies the
[Ctrl+Enter] to apply the formula. formula and keeps cell E3 selected.

5. Use the autofill to combine the other Double clicking the autofill handle runs
names. the formula down

6. Select cell M3. This cell will use a CONCAT function to

combine the names.

7. Enter the following formula: The " " are used to add the blank space
=CONCAT(J3," ",K3," ",I3) delimiters between the the cell values.
[Ctrl+Enter] to apply the formula.

8. Use the autofill to combine the other

names.

9. Select cell E22. This cell will use a TEXTJOIN function to

combine the names.

10. Enter the following formula: The first argument of this formula defines
=TEXTJOIN(" ",True,B22,C22,A22) what the delimiter will be, TRUE will
[Ctrl+Enter] to apply the formula. ignore any blank cells in the returned
value, then the list of cell addresses are
what will be joined.

11. Use the autofill to combine the other

names.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 23
 Action 1.5 - Combining Cells, continued

Instructions: Results/ Comments:

12. Re-select cell M3. The data is using a mix of upper and lower
case text and the formula result reflect
these inconsistences.

13. Enter the following formula: Nesting the CONCAT function inside a
=PROPER(CONCAT (J3," ",K3," ",I3)) PROPER function will return the data
[Ctrl+Enter] to apply the formula. formatted in the desired manner.

14. Use autofill to correct the rest of the

names in this list.

15. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 24
 Lesson 1: Tables & Data Management

Splitting Cell It may become necessary to split a cell into smaller data
components spanning adjacent columns. Excel offers a variety
Values of tools and methods to accomplish this task. Just as there
are function formula used to combine cells, there are function
used to extract data from cells; LEFT, MID, RIGHT, the [Text to
Columns] button, and Flash Fill.

Text to Columns
This tool works best when the data has a consistent structure
with a common character to use as the delimiter.
›› Examine the Column to be broken into multiple
columns in order to determine how many columns
will be needed.
›› Select that number of columns to the right of the
Note column being separated, right-click on the selected
If columns are
not added before columns and choose Insert from the menu.
completing the Text ›› Select the column to be separated.
to Columns, existing
data will be replaced ›› Activate the Data Tab.
to accommodate the
additional columns. ›› Click the [Text to Columns] button.

›› The Convert Text to Columns Wizard dialog opens to

Step 1 of 3.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 25

Lesson 1: Tables & Data Management

Splitting Cell ›› If necessary, select the Delimited radio button, and

click the [Next] button to advance to Step 2 of 3.
Values,
continued

›› Check the checkbox for the appropriate delimiter.

›› Other will allow you to define the delimiter.
›› Watch the Data preview window to see how the
data will be broken apart.
›› When the data has empty adjacent cells, checking
the Treat consecutive delimiters as one will
combine empty cells into a single cell.
›› Once the delimiter is set, click the [Next] button to
advance to Step 3 of 3.

›› Select each column to set the data type with the

Column data format radio buttons.

Page 26 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Splitting Cell ›› Unnecessary columns can be selected and skipped

by choosing the Do not import column (skip) radio
Values, button.
continued
›› Once the data formatting is done, you are able to
set the Destination of where the data will be placed.
›› Click the [Finish] button.

Function Formulas
Right: This function returns the right-most character or characters
from a string. The number of characters specified will be what is
returned.

Syntax

=RIGHT(text,[num_chars])
›› Text: The cell address which contains the text string to
be extracted.
›› Num_chars: if this is not included in the formula, then
only the last character is extracted from the string.
Entering a value will return that number of characters
from the string, blanks are considered characters.

Left: this function returns the left most character or characters

from a text string. The number of character specified will be what
is returned.

Syntax

=LEFT(text,[num_chars])
›› Text: The cell address which contains the text string to
be extracted.
›› Num_chars: if the is not included in the formula, then
only the first character is extracted from the string.
Entering a value will return that number of characters
from the string, blanks are considered characters.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 27

Lesson 1: Tables & Data Management

Splitting Cell Mid: This function will return a specific number of characters
from a text string. You are able to set the starting position, in
Values, number of character from the left. as well as the number of
continued character being extracted.

Syntax

=MID(text,start_num,num_chars)
›› Text: The cell address which contains the text
string to be extracted.
›› Start_num: The number of chacters over from the
left where the extraction is to begin. (Blank spaces
are chacters)
›› Num_chars: Sets to number of characters to be
extracted from the text string.

When dealing with text string of variable lengths which do also

contain fixed parts, the Len functions can prove a useful addition
to a Left or Right function.

Len: returns the number of characters in a string.

Syntax
=LEN(cell)
›› Cell: contains the string whose characters are to be
counted. Spaces are included in the results as thet are
hidden characters.

Nesting Len Inside Left or Right

Syntax
=LEFT(cell,LEN(cell)-value)
›› LEFT(cell,: where the left characters will be extracted
from.
›› LEN(cell): counts the number of characters in the Left
functions cell.
›› -value): sets the starting point of left character
extraction from the cell. This completes the LEFT
function.

Page 28 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 1.6 - Splitting Cells- Text to Columns

Instructions: Results/ Comments:

1. The CleanUp file should still be open. If not, re-open it.

2. Activate the Splitting_TextToColumns

worksheet. The first column of data needs to be
broken up into Class, Location, and Date.

3. Select columns B:D.

4. Right-click the selected columns and To avoid replacing data, it is a good idea
choose Insert from the menu. to set up space for the new columns to be
added before splitting up the data.

5. Select column A. This is the column to be split apart.

6. On the Data Tab, in the Data Tools The Text to columns dialog opens.
Group, click the [Text to Columns]
button.

7. Check to see the Delimited radio button Step 1 is completed and the dialog
is active and click the [Next] button. advances to step 2.

8. In the Delimiters section, check only the As delimiter checkboxes are modified,
Other checkbox and in the field enter a - the preview of how the data is separated
.(Hyphen) changes. Since a hyphen separates each
Check the Treat consecutive delimiters component part of the data, that is the
as one checkbox, then click the [Next] delimiter needed to break the data into the
button. desired sections. Step 2 is completed and
the dialog advances to step 3.

9. Select the third column in the preview This ensures the data type is correctly set
and in the Column data format section, and formatted.
choose the Date: radio button.

10. Set the cursor into the Destination: field By not using cell A1 as the destination, the
and set the cell address to B1. Then click original data is not replaced and lost.
the [Finish] button.

11. Add the headers for columns C:D. Location and Date

12. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 29
 Action 1.7 - Splitting Cells- Using Functions

Instructions: Results/ Comments:

1. The CleanUp file should still be open. If not, re-open it.

2. Activate the Splitting_Functions

worksheet.

3. Select column K:L.

4. Right-click the selected columns and

choose Insert from the menu. To add space before splitting up data.

5. Select cell K1 and type in:

Course_Category_Number. This is the column header.

6. Select cell K2 and enter the following

formula: This formula will extract the first four
=LEFT(J2,4) characters from the value in cell J2. The
[Ctrl+Enter] when done. [Ctrl+Enter] keys apply the formula and
keep call K2 selected.
7. Use Autofill to complete the rest of the
column. Double -click the autofill handle.

8. Select cell L1 and tpye in: This column header.

Course_Application_Version_Number.

9. Select cell L2 and enter the following This formula extracts the last four
formula: characters from the string in cell J2.
=RIGHT(J2,4)
[Ctrl+Enter] when done.

10. Use Autofill to complete the rest of the

column.

11. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 30
 Lesson 1: Tables & Data Management

Flash Fill Flash Fill to Combine or Separate Data

Flash Fill can often replace the need for formulas like PROPER,
CONCAT, TEXTJOIN, LEFT, and RIGHT. Flash Fill recognizes
patterns to combine, separate, or reformat data based on an
example created by the user. Flash Fill uses multiple applications
and lines of code in the background of Excel to anticipate the
data you want it to fill in the list. If it cannot get a complete list
because the pattern is not recognizable, you can add additional
examples to expand the list and Excel will apply them along with
the previous examples to create a more complete list.

›› Click in a blank cell next to the data. Do not leave an

empty column between the data and the flash fill column.
Type the content you want to extract from your list and
press [Ctrl+Enter].
›› Make sure the active cell is still the one with the
Note example data or the active cell is below the example
You can be at any data. Click the [Fill] button from the Editing Group
row in the column
where the Flash
on the Home Tab, and select Flash Fill.
Fill is to be run in order
to use this feature.
Although, the sample
data you create must be
from the same row you
are in.

- OR -

›› Click on the Data Tab to activate it.

›› Select the [Flash Fill] button in the Data Group.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 31

 Action 1.8 - Using Flash Fill

Instructions: Results/ Comments:

1. Click the FlashFill sheet tab.

2. Select cell I1 and type: This will be the header for the new column
Full Name.

3. Select cell I2 and type: This is how the fisrt and last names will be
Jones, Alan. combined.

4. Select cell I3 and begin typing: As you begin entering the second entry,
Adams Flash Fill recognizes the pattern and
when the Flash Fill list is displayed, tap prompts to apply it.
the [Enter] key.

5. Auto adjust the width of column I. Set the cursor between columns I and J,
double-click when the cursor is a double-
headed arrow.

6. Select cell J1 and type: A new column header is added.

Email.

7. Select cell J2 and type: This will both extract and combine data
[Link]@[Link]. from existing data with addition you have
entered.

8. Select cell J3 and use the [Ctrl+E] Flash fill is run and the column of email
shortcut. addresses has been added.

9. Save and close the file. [Ctrl+S] and [Ctrl+W].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 32
 Lesson 1: Tables & Data Management

Tables Managing and analyzing related data is made easier when the
range is converted into an Excel table. Tables are comprised of
adjacent columns of data, each with unique labels or headings,
and each row represents an individual entry within the table.
Another way to consider the structure of a table is that the
columns are fields and rows are records. When creating tables, it
is recommended to not include any blanks rows or columns.

Tables offer a variety of tools to assist in managing of the

Note data they hold. When any cell in a table is active the Table
Other Excel Tools Design Tab are active in the ribbon, this tab has tools for
features, like Filters
and PivotTables,
formatting, adding or removing table elements, exporting, or
may not work efficiently refreshing table data. Filtering is automatically engauged as
if the data is separated by tables are created. A type of freezing panes is also in play, when
blank rows or columns. you scroll down in a table the table headers replace the column
headers.

Table Elements
Header Row: Tables can have a header row. When the header
row is enabled, filtering is also turned on by default. Filtering
offers the ability to both sort and filter data in the data.

Calculated Columns: When entering a formula in a cell within

in a table column or in a blank column beside the table, the
formula is instantly applied to all other cells in the column. If the
column was not part of the table, it is added to the table.

Total Row: Tables can have a total added, the row comes with
a drop-down which offers a list of common built-in formulas.
These are similar to using the AutoSum functions found on the
Home and Formula Tabs.

Banded Rows or Columns: To make the table easier to read cell

shading can be added to alternating rows and/or columns. (Do
not apply both since it will make it hard to understand the data.)

When using the Get Data tools, Excel will automatically bring the
data in as a Table by default. Although, you are able to choose to
bring the data in as a PivotTable, or PivotChart with Table.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 33

Lesson 1: Tables & Data Management

Creating a Table Using Home Tab

›› Select a cell in the range of data to be included in the
Table. It is not necessary to select all the data but all the
data must be connected.
Note
[Ctrl + T] will also ›› Activate the Home Tab,
create a table from
the dataset. ›› Click on the [Format as Table] button in the Styles
Group.
›› This will display a gallery of Table styles.

›› Click one of the Table style options to format the

selected range as a Table.
›› The Format as Table dialog will be displayed.

›› If there are column headings in the first row of the

range you selected for your Table, check the box
that says, My table has headers.
›› Make sure the cell range shown is the range that
you want for your Table; if it is not, just type the
correct range in the Where is the data for your
table field.

Page 34 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Creating a Table, ›› Click the [OK] button to create your Table.

continued ›› In the example here, note the Autofilter buttons are
automatically added to a Table.
Autofilter button

Using the Insert Tab

›› Select a cell in the range of data to be included in the
Table
›› Activate the Insert Tab.
›› Click on the [Table] button in the Tables Group.

›› The Create Table dialog.

›› If there are column headings in the first row of the

range you selected for your Table, check the box
that says, My table has headers.
›› Make sure the cell range shown is the range that
you want for your Table; if it is not, just type the
correct range in the Where is the data for your
table field.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 35

Lesson 1: Tables & Data Management

Creating Tables, Using the Quick Analysis Tool

continued ›› Select the range to be converted into a table.
›› Point to the lower right corner of the range and
click the Quick Analysis Tool pop-up.
›› Select the Tables tab and click the [Table] button.

Page 36 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 1.9– Creating a Table Using

Instructions: Results/ Comments:

1. Open the SalesTables file. This file is found in the data files folder.

2. Save the file as MySalesTables. [F12].

3. Select the SalesData spreadsheet and Notice there are no blank columns or rows
click on any cell containing data. included in this dataset.

4. On the Home Tab in the Styles Group, A gallery of styles will be displayed.
click the [Format As Table] button.

5. Choose the first style from the gallery. The Format As Table dialog is displayed.

6. In the Format As Table dialog, check to Since all the data is contiguous, Excel
see should recognize all the connected data as
=$A$1:$H$193 is displayed in the the source for the table.
Where is the data for your table field.

7. Also, make sure the My table has Excel automatically checks this option
headers checkbox is selected. since the first row of the dataset is not
necessarily in-line with the data beneath. If
you remove the checkmark, your headers
will be replaced with Column 1, Column
2, etc.

8. Click [OK]. The selected table style is now applied and

the data is in a table format.

9. Observe the Table Tools Design Tab This is a contextual tab that is only
which has been added to the Ribbon. available when any cell in the table is
actively selected.

10. Click any empty cell. Notice that the Table Tools Design Tab is
gone.

11. Click any cell in the table. Notice that the Table Tools Design Tab is
back although, it may not be the active tab.

12. Save your workbook. Leave it open. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 37
 Action 1.9– Creating a Table, continued

Instructions: Results/ Comments:

1. Make the SalesData(2) spreadsheet This is the second sheet in the workbook.
active.

2. Click any cell containing data.

3. On the Insert Tab, click the [Table] The Create Table dialog is displayed.
button in the Table Group.

4. Make sure the Where is the data for This defines the contiguous range of cells
your table field is displaying the range that make up the table.
$A$1:$H$193.

5. Make sure the My data has headers Excel will automatically add Autofilter
checkbox is checked. drop-downs to each header in the table.

6. Click the [OK] button. The table is created.

7. Notice the Table Tools Design Tab is

active.

8. Save the file. [Ctrl+S].

9. Make the SalesData(3) spreadsheet This is the third sheet in the workbook.
active.

10. Select all the cells containing data. Click into any cell containing data and use
the keyboard shortcut [Ctrl+A] to select all
connected data.

11. Click the Quick Analysis smart tag. It will be located at the bottom right corner
of the selected range. You can also use the
shortcut of [Ctrl+Q] to bring the smart tag
into view without having to scroll to it.

12. Click the Table category at the top of the It is the fourth option.
Quick Analysis options.

13. Click the [Table] button. The table is created.

14. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 38
 Lesson 1: Tables & Data Management

Autofilters An Autofilter is an Excel feature that lets you filter out records
from a Table. When you select an Autofilter option, only records
that meet the specified criteria will be shown. When you create a
table from raw data or import data into Excel as a table, filtering
is automatically turned on. Each column header displays a drop-
down that allows you to filter the data quickly.

If the Table does not have Filters turned on, go to the Table Tools
Note Design Tab and in the Table Styles Options Group click the
Table now have
Filter Buttons on by checkbox for Filters.
default.

Basic Filtering
To filter a Table based on a specific criteria do the following:
›› Click the Autofilter arrow next to the column heading
you want to filter.
›› Filtering options will correspond to the type of data
held in the field (column).
›› Data can be Text, Numbers, or Dates.
›› You will see the corresponding field values from your
Table in ascending order. Notice that each unique field
entry is present in the list. If you have a lot of records
in your Table, the Autofilter list will scroll to show all
of the fields.
›› Remove the checkmarks by the list items you want
filtered out, and leave checkmarks by list items you
want shown.
›› Unchecking Select All will allow for speedier
filtering since you will not have to uncheck as many
boxes.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 39

 Action 1.10 - Basic Filtering

Instructions: Results/ Comments:

1. Activate the SalesData sheet. The first sheet with the data formatted as
a table.

2. If the Autofilter drop-downs are not The Autofilters should be active.

visible, check the Filters checkbox on the
Table Tools Design Tab.

3. Click the Autofilter drop-down for the The Autofilter options are displayed.
Sales Rep column.

4. From the drop-down menu uncheck the Since you are picking only one specific
Select All checkbox and check the Clotts item, unchecking all the unwanted items
checkbox and click the [OK] button. would be very time consuming. By
unchecking the Select All option you will
only need to find the individual item to
filter for. Only records where Clotts was
the sales rep are displayed in the table. All
the other data is hidden, not deleted.

5. Click the Autofilter drop-down on the

Product ID header.

6. Uncheck the Select All checkbox and Now only sales of product 3227 made by
check the 3227. checkbox and click [OK]. Clotts are displayed.

7. On the Data Tab, in the Sort & Filter All filter are cleared and all the data is re-
Group, click the [Clear] button. displayed in the table.

8. Save the file. [Ctrl+S].

9. Select the SalesData(4) sheet. The last sheet that doesn't have the data in
a table.

10. Select any cell with data. It is not necessary to select the entire data
set before applying filtering.

11. On the Data Tab, in the Sort & Filter The Autofilter drop-downs are placed in
Group, click the [Filter] button. the header row.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 40
 Action 1.10 - Basic Filtering, continued

Instructions: Results/ Comments:

12. Click the Autofilter drop-down on the The Autofilter options are displayed.
Sales Rep header.

13. Uncheck the Select All checkbox and Only sales by Adams are displayed, just
check the Adams checkbox. as before. You can also applying filter to
multiple columns as when in the Table.

14. On the Data Tab in the Sort & Filter All the data is re-displayed. You can also
Group, click the [Clear] button. click the Autofilter button on the State
column and choose Clear Filter From
"Sales Rep'.

15. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 41
Lesson 1: Tables & Data Management

Autofilters, If you display the Autofilter menu for a column you will see
continued either Text Filters, Date Filters or Number Filters depending
on the type of data held in the column. If you click on any of
these Filter options, you will see a submenu of further filtering
selections.

Note
A Number filter
allows you to apply
a filter to numerical
data. A Text filter allows
you to apply a filter to
textual data referred to in
the records. A Date filter
allows you to apply a
filter to date or time data
referred to in the records.

Clicking one of these options will display the Custom Autofilter

dialog box. Using the Custom Autofilter options, you can set up
a customized Filter for your Table.

Creating a Custom Autofilter

›› Select the Autofilter drop-down button for the field
that you want to filter.
›› Choose either Text Filters, Date Filters or Number Filters.
›› Click Custom Filter from the submenu. This will open
the Custom Autofilter dialog.

›› Use the drop-down arrows and option buttons to

establish filtering criteria for your records. The options
in the drop-down include;
Text & Number Options
Equals Does not equal
Is greater than Is less than
Is greater than or equal to Is less than or equal to
Begins with Does not begin with
Ends with Does not end with
Contains Does not contain

Page 42 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Autofilters, Date Options

continued Equals Does not equal
Is After Is after or equal to
Is before Is before or equal to
Begins with Does not begins with
Ends with Does not end with
Contains Does not contain

›› The next drop-down list will contain values from your

Table belonging to the current field.
›› Select the And option button or the Or option button to
incorporate additional criteria into your Filter.

›› When you use the And/Or option buttons to build

Custom Filters, remember:
›› When using the And option, both conditions (A and
B) must be satisfied for the record to be shown.
›› When using the Or option, records that satisfy
either condition will be shown.
›› Use the option buttons to combine filtering conditions,
or just filter based on options from the first two drop
lists.
›› Clicking the [OK] button will remove from view any
data that does not fit within the parameters of your
Custom Filtering.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 43

Lesson 1: Tables & Data Management

Autofilters, When the data is filtered, the column header will show a funnel
continued icon next to the drop-down list button. You are able to filter by
one or as many columns as needed to find specific data within
the dataset.

Using the Search Feature

There are times when you know what to filter for, in these cases
it is easiest to simply click into the search field and type in what
is needed.

Using Wildcards
There are times when you want to search for a set of variables.
Using wildcards in the search allows for boarder searches. There
are two character used as wildcards;
›› * represents any number of any characters.
›› ? represents any single character.
If you where to enter a search of PRO*: the results would be any
words that simply begin with PRO, no matter how long the word
is.

If you entered PRO????: the results would be any word that

begins with PRO and contains four more letters.

Page 44 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Autofilters, Clearing Filters

continued Once you have found specific data and now need to see all the
data in the dataset, you will need to clear any or all applied
filters.

›› Click the filtered column Autofilter drop-down and

click the Clear Filter option on the menu.

›› If multiple columns are filtered you would do it for

each column.
- OR -

›› Use the Filter controls in the Sort & Filter Group on

the Data Tab.

›› Click either the Clear or the Filter button to clear all

the filers at once.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 45

 Action 1.11 - Using Filtering Tools

Instructions: Results/ Comments:

1. Select the SalesData sheet.

2. Select the Autofilter drop-down for the

Unit Price column.

3. Select Number Filters and choose Between The Custom Autofilter dialog is displayed.
from the menu.

4. In the Custom Autofilter dialog box, This establishes the parameters of the data
Under the is greater than or equal to you wish to view based on information in
field, type: 20 the Unit Price column.
and under the is less than or equal to
field, type: 35

5. Click [OK] and examine the filtered Only information matching the defined
data. parameters ares displayed. All the other
data is hidden, not deleted.

6. Click the Autofilter drop-down for the

Unit Price column.

7. Choose Clear Filter from the menu. This removes filtering from this column. If
several columns were being filtered, you
could use the [Clear] button on the Data
Tab in the Sort & Filter Group to clear all
the filters.

8. Click the Autofilter drop-down for the

Customer Name field.

9. In the Search field type in:

Merlin.

10. Clear the filter. Use the Autofilter drop-down or the

[Clear] button on the Data Tab.

11. Save your workbook. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 46
 Lesson 1: Tables & Data Management

Advanced Filter If you can’t get the results you want from a Custom Filter, you
can construct an Advanced Filters to create a query that can
extract specified information from the dataset. This is a two step
process; the first step is to create a set of cells to define which
columns in the dataset are being search and then the define
parameters of the search within the specified columns.
The second step in the process uses the Advanced Filters to
extract the desired information.
To use an
Advanced Filter,
your data does not
Using an Advanced Filter
necessarily need to be Establish a Criteria Range
in an Excel Table, but ›› Type or copy the column headings that correspond to
it should adhere to the the Fields in the dataset on which you want to Filter.
basic dataset principles,
excluding blank rows Paste or type them into a cell outside the Table range.
and columns. This heading must be exactly the same as the corresponding
heading in the Data Table that you want to base the Filter
on; so it may be easiest to copy and paste.

Criteria
Range

›› Type the constraints below the Column Heading in the

Criteria Range.
In this example, you want to show only the Records where the
Age Field is less than 40.

Applying the Advanced Filter

›› Click on any cell in the Data Table, and then click the
[Advanced] button on the Data Tab.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 47

Lesson 1: Tables & Data Management

Advanced Filter, ›› The Data Table will be outlined with a marquee,

continued and the Advanced Filter dialog opens.

›› Make sure the Table range is the range you want to

Filter. It should already be defined by default.
›› Click into the Criteria range: field and select the cells
that contain your filtering criteria (cells F1:F2 in this
example).
›› If Filter the list, in-place option button is selected, the
Filtered Records will appear in the same location as
the original Table. The rows that do not fit the criteria
will simply be hidden.
›› Click [OK] to filter the dataset using the constraints
specified in the Criteria Range.

Clearing the Filter

After examining or working with the filtered data, you will need
to clear the advanced filter.

›› To display the full Table again, select the Data Tab and
in the Sort and Filter Group, click the [Clear] button.

Page 48 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Advanced Filter, You may want to extract your Filtered Records to a new place
in the worksheet or even to a different worksheet altogether.
continued
Copying your Filtered Records to a new location leaves the view
of your original dataset unchanged.

Copying Filtered Records to a New Location

›› Set up a Criteria Range as before, with column headings
and the constraints you need. Make sure the Field
names match those in the dataset exactly.
›› Prepare a range for the Filtered Records to be copied
to.
›› Add the column headings for the result set in the
range you will be copying to. You do not have to
use all of the Fields in the entire Record, just the
Fields of your choice.
If you do not put column headings in the copy to range, all of the Fields
specified in the Table range will be copied.
›› Click a cell in the dataset. Select the Data Tab and in
You can also specify
the Sort and Filter Group, click the [Advanced] button.
Unique records only The Advanced Filter dialog will be displayed.
by clicking the ›› Set your options as before, but this time, choose Copy
appropriate checkbox
to Another Location from the option buttons in the
in the Advanced Filter
dialog. This will ensure
Action section of the dialog.
that duplicate Records ›› In the Copy to: text box, select the range that you have
are not selected or prepared for the copied Records with your mouse or
copied. type it in directly. If you don’t know how large a range to
include, just select the column headings in the destination
area.
›› Click [OK] to copy the Filtered Records to the
destination range. The example at below shows only
the Fields for weight and age filtered based on a
criteria of people with a height less than 70.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 49

 Action 1.12 - Using an Advanced Filter, Filtering In PLace

Instructions: Results/ Comments:

1. Select the SalesData(2) sheet. The second sheet in the workbook.

2. Select cell D1. This will be the first filed of the Advanced
Filter.

3. Copy the cell and paste it into cell K1. Copy / pasting will ensure there are no
discrepancies with the headers in the data
set.

4. Select cell H1. This will be the second field of the

Advanced Filter.

5. Copy the cell and paste it into cell L1. Our goal is to construct an Advanced Filter
to retrieve the records where a specific
Sales Rep sold more than 25 units per sale.

6. Select cell K2, and type: The Sales Rep the filter will search for.
Clotts.

7. Select cell L2, and type: This is the second criteria that must be met
>25 in the filter. When the criteria are in the
same row it means AND.

8. Select the Data Tab, and in the Sort The Advanced Filter dialog opens.
& Filter Group, click the [Advanced]
button.

9. In the Advanced Filter dialog, set the

following:
Filter the list: in-place
List range: $A$1:$H$193
Criteria range: $K$1:$L$2

10. Click [OK]. Only records where Clotts sold more than
25 units are displayed.

11. On the Data Tab in the Sort & Filter All the records in the data set are re-
Group, click the [Clear] button. displayed.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 50
 Action 1.13 - Using an Advanced Filter, Copy to Another Location

Instructions: Results/ Comments:

1. Select the Data Tab, and in the Sort The Advanced Filer dialog opens.
& Filter Group, click the [Advanced]
button.

2. In the Advanced Filter dialog, set the

following:
- Copy to another location
- List range: $D$1:$D$193
- Criteria range: blank
- Copy to: O1
- Unique Values Only = Checked

3. Click [OK]. A list of Sales Reps is added starting in cell

O1.

4. Save your workbook. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 51
Lesson 1: Tables & Data Management

Data Forms You can use a Data Form to add, find, change, and delete rows
in a range or table. To add the Form, you need to use the Form
Tool. The Form Tool is not included on the Ribbon or in the
QAT by default.

Adding the Form Tool to the QAT

›› Click on the [More] button on the QAT.
›› Select More Commands from the drop-down menu.
This will open the Excel Options dialog.
›› Click on the drop-down arrow of the Choose Commands
from text box.
›› Select All Commands.

›› Find the [Form] button in the command list and

double-click on it or click on [Add] to add it to the
QAT.

Page 52 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 1: Tables & Data Management

Data Forms, Using a Form to Enter Records

continued ›› Select a cell in the table or data set.
›› Click on the [Form] button in the QAT. A dialog
appears with the Field names and fields to enter
information related to the Record.
›› Click on the [New] button to add a new Record.
›› Use the [Find Prev] and [Find Next] buttons to
navigate between Records.
›› Use the [Criteria] button to find specific Records.
›› Wildcards and search criteria can be used to locate
specific Records. For example, typing F* in the
Comapny Name: field will find all Records where the
Company Name begins with an "F". Or typing >2000
in the Invoice Total: field will find all invoices with
amounts greater than $2,000.
›› Click the [Find Next] button after typing in the
Criteria, to locate the required Record

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 53

 Action 1.14- Adding the Form Command to The QAT

Instructions: Results/ Comments:

1. Click the File Tab. The Backstage view is displayed.

2. Click the Options command. The Excel Options dialog is opened.

3. Select the Quick Access Toolbar The Quick Access Toolbar modifications
category. are available.

4. Change the Choose commands from: The list of available commands is changed,
field to All Commands from the drop- every command in Excel is displayed in
down list. the left panel. The list is laid out in an
alphabetical manner.

5. Find the Form... in the list of All Scroll through the list to find the
Commands. command.

6. Click the [Add] button. The command is added in the right panel
that shows any commands already on the
QAT. Double-clicking the command in the
left panel will also add the command to
the right panel.

7. Click the [OK] button. The Excel Option dialog is closed and the
command is now on the QAT.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 54
 Action 1.15- Using The Form Command

Instructions: Results/ Comments:

1. The SalesData sheet should still be Click the first sheet in the workbook sheet
active and select any cell in the table. list if necessary.

2. Click the Form command on the QAT. The Form dialog opens, notice that the
dialog is named the same as the active
sheet. It is displaying the first record in
the table.

3. Click the [Find Next] button. The second record is displayed in the Form
dialog.

4. Click the [Criteria] button. All the fields are cleared, allowing you to
enter search criteria.

5. In the Sales Rep field, type in; In this case you are looking for any records
<Clotts> where Clotts was the Sales Rep.
and click the [Find Next] button.

6. Click the [Find Next] button. The next record where Clotts was the Sales
Rep is displayed.

7. Click the [New] button All the fields are cleared, allowing you to
enter a new record into the table.

8. Enter the following information; This represents a new purchase record.

Order ID= 194
Order Date = (today's date)
Customer Name = (your first name)
Sales Rep = Smith
Product Name = Product 1
Product ID = 3223
Unit Price = 32
Qty= 19

9. Click the [New] button. The record is added to the table and you
are ready to begin entering another new
record.

10. Click the [Close] button. The Form dialog closes.

11. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 1: Tables & Data Management, Page 55
Tips and Notes
 Excel 2019: Data Analysis
Rel. 1.0, 5/6/2020

Lesson 2: Power Query

Lesson Overview

You will cover the following concepts in this

chapter:
›› Introduction to Power Query ›› Spliting Data
›› Get & Transform Data ›› Adding a Column
›› Navigator ›› Data Types
›› Power Query Editor ›› Applied Steps
›› Data from Another Workbook ›› Loading a Query
›› Data from a Text File ›› Editing a Query
›› Data from an Access Database ›› Saving and Running A Query
›› Transforming Data
Lesson Notes
 Lesson 2: Power Query

Introduction to Data coming into Excel for analysis is a common occurrence.

As illustrated in the last lesson, cleaning up the data within
Power Query Excel is not too difficult a task. Power Query, a data connection
technology is now the standard tool used to import or gather
data into Excel. Users of Excel 2010 and 2013 versions could
download and install the Power Query add-in while the 2016
version it had been rolled in to the program alongside the legacy
data connection and management tools.

The Power Query tool offers a streamlined manner to connect to,

import, and manage data. You can connect to one or several data
sources in order to build complex models to conduct meaningful
analysis, combining data sources, merging tables, adding or
removing columns. Once a query has been created, it is possible
to save and reuse the query. This can save a considerable amount
of time when re-running reports in the future.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 59

Lesson 2: Power Query

Get & Transform Getting Data

Data Data can be imported into Excel from many different sources
by using tools found in the Get & Transform Data Group of
commands on the Data Tab. The data can be imported directly
into a workbook as is, and then edited or cleaned-up within
Excel.

Note
When accessing
data from web sites,
that data must be in
tables in order for Excel
to access it. Common types of external data sources have buttons in full view
within the Get & Transform Group. There are many others
available by using the [Get Data] drop-down button. This menu
of options offers a list of data types with fly-out menus.

Once a choice of data type is made the next step in the process is
to locate the data source in the Import Data dialog.

After the data source is located and opened, the Navigator dialog
opens, which is the next step in the process.

Page 60 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 2: Power Query

Navigator The Navigator dialog shows users a list of available tables or data
sets on the left and a preview of the data on the right. Choosing
a source table, data set, or spreadsheet will change the preview
to that of the selected source. If more than a single table from the
source is required, checking the Select multiple items checkbox
allows all selected tables to be imported.
Data Table Search Refresh
Data Table List Data Preview

Loads directly into Excel workbook

Opens data set in Power Query

Clicking the [Load] button will place the selected data sets into
the workbook, beginning in the actively selected cell. Choosing
Load To... from the drop-down of the [Load] button opens an
Import Data dialog. This dialog allows you to determine how and
where the data will be placed into the workbook.

Clicking the [Transform Data] button will open the data set in
Power Query.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 61

Lesson 2: Power Query

Power Query The Power Query window is very similar to the Excel interface
in that, both have a Quick Access Toolbar and tabbed ribbon
Editor navigation components.
QAT Ribbon Data Set

Query List Pane Query Properties & Steps Pane

›› QAT: (Quick Access Toolbar) users can add common

commands to this toolbar for easy access.
›› Ribbon: just as in Excel, the ribbon is a tabbed set of
related tools.
›› Query List pane: all imported data sources are
displayed here, clicking a query will display the
selected data set in the Main view
›› Data Set View: this is the main view where data is
previewed and modified.
›› Query Steps pane: as modifications are made to the
data set, those steps are listed in this pane. This can
be seen as a history of all actions to prepare the data
for importation. Any step in the list can be removed
without removing steps made before or after the
selected step.

Page 62 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 2: Power Query

Power Query Once all the data modifications are completed; unnecessary row
and columns removed, data has been split into more manageable
Editor, components, and columns of data being combined the data is
continued loaded into an Excel workbook by using the [Close & Load]
button on the Home Tab. This button offers the same options and
functionality as in the Navigator dialog.

Choosing the Close & Load To.. option from the button's drop-
down will display the Import Data dialog .

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 63

Lesson 2: Power Query

Data from Using the Get & Transform tool allows you to connect to data
stored in other workbooks. The data can be a simple data set or
Another formatted as a table, either can be brought into and managed in
Workbook new or existing workbooks.

›› On the Data Tab, in the Get & Transform Group click

the [Get Data] button.
›› Choose From File in the drop-down and then From
Workbook.
›› The Import Data dialog opens.

›› Navigate to the other workbooks location and click

the [Import] button.
›› The Navigator dialog opens.
›› Choose the spreadsheet or table from the list on the
left of the dialog.

›› If more than one data source is required, check

the Select multiple items checkbox; and select all
sources.

Page 64 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 2: Power Query

Data from ›› Loading the data directly into Excel is done by clicking
the [Load] button. This will place the data in cell A1 of
Another a new worksheet as a table.
Workbook,
continued

›› If you need the data to come in on an existing

worksheet or as a PivotTable; click the drop-down
arrow of the [Load] button and choose Load To ...

›› The Import Data dialog opens and you are able to

set where and how the data will be placed into the
workbook.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 65

 Action 2.1 - Importing Data From Another Excel Workbook

Instructions: Results/ Comments:

1. Create a blank new workbook. [Ctrl + N].

2. Activate the Data Tab.

3. Locate the Get & Transform Group. This is the first group on the Data Tab.

4. Click the [Get Data] button drop- The [Get Data] button offers many options
down and choose From File, then From for connecting to external data sources.
Workbook. When the choice is made the Import data
dialog opens.

5. In the Import Data dialog, navigate to the The Navigator dialog opens.
data files folder and choose the Invoices.
xlsx file.

6. Locate the list of available worksheets These are all named tables and worksheets
and tables in the source. in the file. Another example of will it is
good practice to name the worksheets and
tables in your files.

7. Check and uncheck the Select multiple When the Checkbox is checked,
items checkbox . checkboxes are added in front of each
options Checking these will include
them in the importation of data. When
unchecked you are able to select only one.

8. Select the Invoices from the list of The data in that table is displayed in the
available sources displayed on the left of preview on the right of the window.
the window.

9. Click the [Load] button. A new worksheet is added to the

workbook with the imported data in a
formatted table.

10. Rename Sheet2 as Invoices. Double-click the sheet tab to rename it.

11. Save the file in the data files folder as [F12].

[Link].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 66
 Lesson 2: Power Query

Data from a Text Many of us will receive data in the form of CSV or tab delimited
text files. The process for getting and importing those types of
File data will be done in the same manner as importing data from
other Excel files. Using the tools in the Get & Transform Data
Group, you will search for and connect to the source data by
clicking the [From Text/CSV] button. When importing a text
file, the Navigator dialog just shows the preview along with a set
of fields above the preview.

Should the preview show the data breaking in an unexpected

manner; use the Delimiter field to set the correct delimiter. From
the Delimiter field drop-down you are able to choose from a list
of commonly used delimiters or set your select your own.

Choosing Custom will add a new field below the Delimiter field
where you are able to type in the delimiter of your choosing.

Choosing Fixed Width from the Delimiter field will add a new
number field below the delimiter field. Allowing you to set
the number of characters to divide the content by in order to
generate columns .

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 67

 Action 2.2 - Importing Data From a Text File

Instructions: Results/ Comments:

1. [Link] should still be open. If not, re-open it.

2. Activate the Data Tab.

3. Click the [From Text/CSV] button locate The Import Data dialog opens.
in the Get & Transform Group

4. In the Import Data dialog, navigate The Navigator dialog opens.

to the data files folder and open the
[Link] file.

5. Locate the Delimiter field. As a textually based source file, you are
able to set the delimiter if needed.

6. Using the Delimiter field drop-down The preview now shows all the data in a
change the delimiter from Comma to Tab. single column. This is because the source
is not using tabs to separate the data.

7. Change the delimiter back to Comma. The data is broken into multiple columns
again.

8. Click the [Load] button drop-down and A second Import Data dialog opens.
choose Load To...

9. Leave the Select how you want to view This will apply a table format to the data.
this data in your workbook choice as
Table.

10. In the Where do you want to put the This allows you to define where the data is
Data? section, placed.
choose the Existing worksheet radio This is where it will be placed.
button. The data is placed and formatted as a
Click into the field below, table. The table does not use the column
highlight any existing text and delete it, headers as headers, this will be fixed later.
select cell A1 on Sheet1,
click the [OK] button.

11. Rename Sheet1 as NA Customers. Double-click the sheet tab to rename.

12. Save the file and leave it open. [Ctrl +S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 68
 Lesson 2: Power Query

Data from an Gathering data from an Access Database follows similar lines as
within importing Excel or text content. Sometimes we want to
Access Database bring in a table from a database but not know what other tables
are related; the Navigator dialog allows you to Select multiple
items. When the Select multiple items checkbox is checked and
a table is selected, the [Select Related Tables] button becomes
active. Clicking this button will select all related tables in the
database at once; alleviating the need to know and understand
the entire database structure.

To connect to the access database use the [Get Data] button drop-
down and choose From Microsoft Access Database from the list of
database types.

›› Should your database type not be listed, contact the

manufacturer of your database to see if they have and
can send you the required drivers to connect to Excel.
In the Import Data dialog, navigate to and select the desire
database then click the [Open] button.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 69

Lesson 2: Power Query

Data from an The Navigator dialog opens, in the left of the dialog is the list of
tables in the database. You will notice that the [Select Related
Access Database, Tables] button is greyed out and inactive. In order to make it
continued active; you must first check the Select multiple items checkbox,
then select a table from the list. Then the [Select Related Tables]
button is active. Clicking the button will allow the Navigator
to follow all the primary to foreign key threads to include all
necessary data in the import..

Page 70 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 2.3 - Importing Data From a Database

Instructions: Results/ Comments:

1. [Link] should still be open. If not, re-open it.

2. Activate the Data Tab.

3. Click the [Get Data] button drop-down The Import Data dialog opens.
and choose From Database, then From
Microsoft Access Database.

4. In the Import Data dialog, navigate to The Navigator dialog opens.

the data files folder and choose the
[Link].

5. Select CompanyNames from the list of The table is displayed in the preview area.
tables.

6. Check the Select multiple items The preview is removed and checkboxes
checkbox. are added to each table in the list.

7. Check the CompanyNames checkbox. The preview is re-displayed.

8. Click the [Select related Tables] button. Since the Countries and Orders tables have
a relation with CompanyNames table they
are added to the selection.

9. Uncheck the checkboxes for The table are now deselected.

CompanyNames and Countries.

10. Click the [Select related Tables] button Any tables related to the Orders table are
again. now also selected.

11. Uncheck the Select multiple items All selections are cleared and the
checkbox. checkboxes are removed.

12. Select the Orders table and click the The data is loaded onto a new worksheet
[Load] button. as a formatted table.

13. Rename Sheet3 as Orders. Double-click the sheet tab to rename.

14. Save the file and leave it open. [Ctrl +S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 71
Lesson 2: Power Query

Transforming Raw data is not often configured in the best and most useful
manner; requiring users to spend time removing unnecessary
Data data and splitting data into smaller more manageable pieces.
This quickly becomes very time consuming when updated data is
required every week or two. With the Transform tools available
in Power Query you can take care of most of those changes
before bringing the data into Excel. Once the connection has been
established along with a process to transform the raw data into
useful data developed, the query can be saved and rerun as new
data is comes in.

Opening Power Query

This begins with importing data into Excel, but instead of simply
loading the data set, choosing the [Transform] button in the
Navigator dialog.

›› On the Data Tab, use the appropriate Get Data option.

›› In the Import Data dialog, navigate to and open the
source data.
›› In the Navigator dialog, choose the required data
sources.
›› Click the [Transform] button to open the raw data in
Power Query.
›› Power Query opens.

QAT Ribbon Data Set

Query List Pane Query Properties & Steps Pane

Page 72 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 2: Power Query

Transforming Removing Rows and Columns

Data, Row Removal
continued ›› On the Home Tab you will find the Reduce Rows
Group.

›› Click the [Remove Rows] button, a menu of option is

displayed. There is no need to select the rows before
Note using this tool.
Using the [Keep
Rows] button works
just as the [Remove
Rows].

›› Choose Remove Top Rows from the menu.

›› The Remove Top Rows dialog opens.

›› Enter the number of rows to be remove from the top of
the data set and click the [OK] button.
Column Removal
›› When removing columns, select the columns to be
removed or kept.
›› use the Shift key for continuous selection
›› use the Ctrl key for noncontinuous selection

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 73

Lesson 2: Power Query

Transforming ›› Once the columns have been selected, got to the Home
Tab and locate the Manage Columns Group.
Data,
continued

›› Click the [Remove Columns] button drop-down.

›› Choose the appropriate option to either remove or

keep the selected columns.

Making Top Row the Headers

After any unwanted rows have been removed from the top, the
top row may contain the actual column headers.

›› On the Home Tab, locate the Transform Group.

›› Click the [Use First Row as Headers] button.

›› Notice the column headers of A,B,C.. have been

replaced with your data.

Removing Duplicates and Blank Rows

One important task is checking for and removing duplicate
records from the data set. Using the [Remove Rows] button
drop-down, you will find the ability to remove duplicates.

›› On the Home Tab, click the [Remove Rows] button

drop-down and choose Remove Duplicates.
›› Click the [Remove Rows] button again and choose
Remove Blank Rows from the menu.

Page 74 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 2.4 - Transforming Data in Power Query

Instructions: Results/ Comments:

1. [Link] should still be open. If not, re-open it.

2. Activate the Data Tab.

3. Click the [From Text/CSV] button The Import Data dialog opens.
located in the Get & Transform Group

4. In the Import Data dialog, navigate to The Navigator dialog opens.

the data files folder and choose the
[Link] file.

5. Click the [Transform] button. The data is opened in the Power Query
Editor window.

6. Examine the Power Query Editor interface. Locate the QAT, click through the tabs in
the ribbon, note the left Queries pane is
collapsed, and the Query Settings pane is
expanded on the right.

7. Click the arrow at the top of the Queries The Queries pane is on the left of the data
pane to expand the pane. Use the arrow view. When expanded, it showa and
again to collapse the pane. gives access to all queries in the current
workbook.

8. On the Home Tab, locate and click the The Remove Top Rows dialog opens.
[Remove Rows] button. Choose Remove
Top Rows from the menu.

9. Enter 4 in the Number of rows field and The top four rows are removed from the
click the [OK] button. data.

10. On the Home Tab, locate and click the Any blank rows are removed from the
[Remove Rows] button. Choose Remove data.
Blank Rows from the menu.

11. On the Home Tab, locate and click the Any duplicate rows are removed from the
[Remove Rows] button. Choose Remove data.
Duplicate Rows from the menu.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 75
 Action 2.4 - Transforming Data in Power Query, continued

Instructions: Results/ Comments:

12. On the Home Tab, locate and click the The default names of Column A, Column
[Use First Row as Headers] button in the B, etc... are replaced with the values in the
Transform Group. first row of the data.

13. Select the Name header cell, right-click A [Rename] button can also be found on
on it and choose Rename from the menu. the Transform Tab in the Any Column
Group.

14. Rename the column as F_Name.

15. Rename the Name_1 header as L_Name. Use either the button or right-click method
to rename the header.

16. Scroll to right to locate and select Select the first column then hold the Ctrl
the Age, Vision, Dental, and Health key as you select the others.
columns.

17. On the Home Tab, locate and click The columns are removed from the data.
the [Remove Columns] button in the If you wanted to keep only these columns,
Manage Columns Group. use the [Remove Columns] button drop-
down and choose Remove Other Columns.

18. Leave the file as is. Do not exit the Power Query Editor.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 76
 Lesson 2: Power Query

Spliting Data One aspect of the Rules of Normalization is that data should
be broken down into the smallest logical components. As an
example consider an address; to be normalized it should have
a column(field) for street, city, state, and zip to truly be used
effectively.
Data may not be normalized when you first receive it, so it may
be necessary to re-organize the data in a more useful manner.
This can also be done within the Power Query environment.

Splitting Data
›› Select the column which needs broken into smaller

component parts.
›› Oh the Home Tab locate the [Split Column] button in
the Transform Group.
›› Clicking the button opens the menu of option for
splitting the column.

›› Choosing any of the top three options opens

a dialog, where you are able to set the specific
parameters to split the data.
›› The others will do as their names suggest.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 77

Lesson 2: Power Query

Spliting Data, ›› Choosing the By Delimiter option opens the Split

continued Column by Delimiter dialog.

›› In this dialog, you are able to choose from a list

of common delimiters from the Select or enter
delimiter field.
›› Choosing Custom from the list adds a field where
you type the character to use as a delimiter.
Consider using an @ to split the user name from the
domain name in a list of email addresses.
›› Below the delimiter selector area are radio buttons
offering choices on how the delimiter will be
applied.
›› The Advanced Options arrow will expand the
dialog.

›› Once all the parameters have been set, click the

[OK] button to apply the split.

Page 78 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 2.5 - Split Column

Instructions: Results/ Comments:

1. Select the Address column. The Power Query Editor should still be
open. If not, repeat the previous exercise.

2. On the Home Tab, locate and click the A drop-down menu is displayed allowing
[Split Column] button in the Transform you to choose how the column will be
Group. split.

3. Choose the By Digit to Non-Digit option The leading number of the addresses are
from the drop-down list. separated from the text and there are now
two columns to represent the address data.

4. Select the Address.1 heading and rename Use either of the renaming methods from
it Address_Number. the previous exercise.

5. Select the Address.2 heading and rename

it Street_Name.

6. Select the Date column.

7. On the Home Tab, locate and click the The list of options are displayed.
[Split Column] button in the Transform
Group.

8. Choose the By Delimiter option from the The Split Column by Delimiter dialog opens.
drop-down list.

9. From the Select or enter delimiter field Often when choosing By Delimiter, the
drop-down choose Custom (if necessary), data is analyzed and the correct delimiter
in the new Delimiter field type in a / , is put in place.
in the Split at section, choose Each The data is now broken into three separate
occurrence of the delimiter radio button (if columns.
necessary),
and click the [OK] button.

10. Select the Date.1 heading and rename it Try double-clicking the header in order to
DOB_Month. rename it.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 79
 Action 2.5 - Split Column, continued

Instructions: Results/ Comments:

11. Select the Date.2heading and rename it
DOB_Day.

12. Select the Date.3 heading and rename it

DOB_Year.

13. Leave the file as is. Do not exit the Power Query Editor.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 80
 Lesson 2: Power Query

Adding a At times, the raw data may be to segmented to be used

effectively. It may be necessary to create new columns by
Column combining existing columns or even creating calculated columns.

Create a Combined Column

This example will be a joining of the first and last names columns
to create a full name column.

›› Activate the Add Column Tab.

›› Click on the [Custom Column] button in the General
Group.

›› The Custom Column dialog opens.

›› Name the column by typing into the New column

name field.
›› In the Custom column formula field, set your
cursor after the equal sign.
›› In the Available columns list select the first Name
Note column and click the [Insert] button.
Quotation marks
are used to string
›› Then type in: &" "&. This will add the blank space
together any values
that are not part of an after the first name and allow you to add the next
existing field. This is column. The & acts as an add function.
similar to concatenating
but uses a symbol
instead of a function.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 81

Lesson 2: Power Query

Adding a ›› Then select the last name column from the list of
Available columns and click the [Insert] button.
Column,
continued ›› Click the [OK] button to add the column.
›› The new column will be added to the far right
of the column, click the header and drag it into
position.

Create a Calculated Column

›› Activate the Add Column Tab.
›› Click on the [Custom Column] button in the General
Group.
›› The Custom Column dialog opens.
›› Name the new column in the New column name field.
›› Set the cursor into the Custom column formula field.
›› Select the first column of data to be used in the formula
from the Available fields list, and click the [Insert]
button. (Double-clicking the choice in the Available
fields list will also insert it.)
›› Add a mathematical operator. (+, -, *, /)
›› Select the second column of data to be used in the
formula and click the [Insert] button, or add a specific
value.
›› Click the [OK] button when done.
If there are any errors in the syntax of your formula, you will be
notified and the [OK] button will also not be active.

Page 82 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 2.6 - Adding Columns

Instructions: Results/ Comments:

1. The Power Query Editor should still be The Power Query Editor should still be
open. open. If not, repeat the previous exercise.

2. Activate the Add Column Tab.

3. Locate and click the [Custom Column] This should be the second button on the
button in the General Group. tab. The Custom Column dialog opens.

4. In the New column name field enter the This will be the column name.
name: Full Name.

5. Set the cursor into the Custom column This will add the values in the F_Name
formula field beside the equal sign, column, to a blank space, and the values in
choose the F_Name column in the the L_Name column together to combine
Available columns field and click the these fields.
[Insert] button, Double-clicking the column name in the
type in &" "& , Available columns list will also insert the
choose the L_Name column in the column into the formula.
Available columns field and click the
[Insert] button, then click the [OK] New columns are added to the far right of
button. the columns.

6. Click the Column header and drag it into The column is now in the correct position.
position after the L_Name column.

7. Click the [Custom Column] button The Custom Column dialog opens.
again.

8. In the New column name field enter the This will be the column name.
name: YearlySalary.

9. Set the cursor into the Custom column This will multiply the values in the
formula field, beside the equal sign, WEEKLY PAY column by 52. If there is
choose the WEEKLY PAY column in the an error in your formula, you will see a
Available columns field and click the warning and the [OK] button is inactive.
[Insert] button,
type in *52 , then click the [OK] button.

10. Click the Column header and drag it into The column is now the correction position.
position after the Weekly Pay column.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 83
Lesson 2: Power Query

Data Types As data is brought into the Power Query Editor it is analyzed and
data type is applied to each column. Data will fall into one of
three data types: text, dates, or numbers. Within each of these
data types are formatting variations: date, date and time, whole
number, currency, percentages. When the format is applied
within Power Query, the data is simply being defined not visually
formatted. Formatting will be done in Excel after the data is
imported.

Applying Data Types

›› Select the column of data to be typed.
›› On the Home Tab, locate the [Data Type] button drop-
down,

›› Choose the appropriate data type.

›› Each column header cell displays an icon to

represent the data type.

Applied Steps As changes are made to the raw data, each step in the process is
recorded and displayed in the Applied Steps pane. When a data
modification does not return an expected or desired result, that
step can easily be removed from within the Applied Steps pane.

Using the applied Steps Pane.

›› Select the step in the Applied steps pane.
›› Click the [X] button to delete the step.
›› Step can be deleted from any point within the list.

Page 84 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 2.7 - Applying Data Types and Using the Applied Steps Pane

Instructions: Results/ Comments:

1. Select the YearlySalary column Notice the header of the column has a text/
number icon to the left of the name.

2. On the Home Tab, locate the [Data Type] Currently the data type is set to Any.
button in the Transform Group.

3. Click the button drop-down and choose The icon to the left of the column header is
Currency from the menu. now a dollar sign.

4. Select the SSN column. This column is currently set as text.

5. Click the [Data Type] button drop-down The column now displays an error for the
and choose Whole Number from the entire column. This is because the SSN
menu. uses dashes as separators,which numbers
can't have.

6. Look at the Query Settings pane and

locate the list of applied steps in the
transformation.

7. Select the Added Custom 1 step. Any step listed after are not in play in the
data. This allows you to easily find the
last step which was correct.

8. Select the last step and click the X to the That step is removed from the list and
left of the step. undone from the data. Unfortunately it
also removed the data type change from
the YearlySalary column.

9. Re-select the YearlySalary column and Apply the Currency data type.
apply the correct data type.

10. Leave the file as is. Do not exit the Power Query Editor.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 85
Lesson 2: Power Query

Loading a Query Once the data has been transformed and it is ready to be brought
into Excel, you can choose how the data will be placed into the
workbook.

Close & Load

›› On the Home Tab.
›› Click the [Close & Load] button.

›› The data is placed on a new worksheet as a table.

Close & Load To...

›› On the Home Tab.
›› Click the [Close & Load] button drop-down.

›› From the menu, choose Close & Load To...

›› The Import Data dialog opens.
›› Choose what form the imported data will take and
where the data is to be loaded.

›› Click the [OK] button to complete the importation.

Page 86 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Action 2.8 - Loading the Transformed Data

Instructions: Results/ Comments:

1. On the Home Tab, locate the [Close & This is the first button on the Home Tab.
Load] button.

2. Clicking the drop-down of this button These function the exact same way
will allow you to choose Close & Load or the Load and Load To... work in the
Close & Load To.... Navigator dialog.

3. Choose Close & Load if using the drop- The data is loaded as a Table to a new
down or simply click the top half of the worksheet.
button.

4. Save the file and leave it open. [Ctrl + S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 87
Lesson 2: Power Query

Editing a Query When the data has been imported into Excel, you will work with
it as any other data. As the workbook now has data connected
to an outside source the Queries & Connections pane is displayed
to the right of the interface. Should this pane be in the way, it
can be closed. To bring the Queries & Connections pane back into
view, click the [Queries & Connections] button in the Queries &
Connections Group on the Data Tab.

The Queries & Connections pane lists any and all external sources
the current workbook is using. Hovering over an existing
connection will bring up a preview of the sources data along
with connections details and options.

Clicking the [Edit] at the bottom of the preview pane will take
the data back into the Power Query Editor. All applied steps are
still listed in the Applied Steps pane and you are able to continued
transforming the data as needed. When finished editing, click
the [Close & Load] button as before.

To refresh data can be done on either the Data or Query Tabs.

›› On the Query Tab the [Refresh] button is in the Load
Group.
›› On the Data Tab the [Refresh] button is in the Queries
& Connections Group.

Page 88 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 2: Power Query

Saving and Once the query has been created, you may want or need to save
it to use again in other files.
Running A
Query The Query Tools Query Tab is a contextual tab, if you are
working inside the imported data, the tab is available, if not in
the data set the tab is not displayed on the ribbon.

Export Connection File

›› Select any cell within the imported data set.
›› Activate the Query Tab.
›› Click the [Export Connection File] button.

›› The File Save dialog opens.

›› Name the file.
›› Do not change the location where this is being
saved.

Running a Query
›› On the Data Tab, click the [Existing Connections]
button in the Get & Transform Data Group.
›› The Existing Connections dialog opens, allowing you to
search for the saved queries.
›› If your query is not in the list, click the [Browse for
More...] button.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 89

 Action 2.9 - Refreshing the Data/ Saving Queries

Instructions: Results/ Comments:

1. The [Link] file should still be If not, re-open it.
open.

2. The Queries & Connections pane should When data has been imported into a
be open, if not go to the Data Tab and workbook, this pane should be active.
click the [Queries & Connections] This pane shows all imported data
button. connections.

3. Hover over the EmployeeList connection. A preview panel opens, showing some
details about the connection, and
connection tools.

4. Click away from the Queries and The preview disappears.

Connections pane.

5. Activate the Queries Tab.

6. Click the [Refresh] button in the Load The data is refreshed. If you hover over
Group. the EmpoyeeList connection again the Last
Refreshed detail will show when you ran
the refresh command. The Data Tab also
has a [Refresh] button available.

7. On the Query Tab, locate and click the This is the last button on the tab. Clicking
[Export Connection File] button. it opens a File Save dialog.

8. Give it a meaningful name and click the Do not change the location of where it is
[Save] button. being saved. This makes finding it later
easier.

9. Save and close the file. [Ctrl + S] and [Ctrl + W].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 90
 Action 2.11 - Running Queries

Instructions: Results/ Comments:

1. Create a new blank workbook. [Ctrl + N].

2. On the Data Tab, click the [Existing The Existing Connections dialog opens.
Connections] button in the Get &
Transform Group.

3. Select the query you just saved and click The Import Data dialog opens.
the [Open] button.

4. Leave the settings as they are in the The data is imported as a table to a new
Import Data dialog and click the [OK] worksheet.
button.

5. Close the file without saving. [Ctrl + W].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 2: Power Query, Page 91
Tips and Notes
Excel 2019: Data Analysis
Rel. 1.0, 5/6/2020

Lesson 3: Database
Functions
Lesson Overview

You will cover the following concepts in this

chapter:
›› Database Functions
›› Basic Syntax of D-Functions
›› Creating a D-Function Formula
›› Expanding D-Functions
›› Adding Drop-Down Menu's
Lesson Notes
 Lesson 3: Database Functions

Database When there are large amounts of data within your datasets the
D-Functions can make searching for specific information faster.
Functions While simple functions are used in many instances there will
be times where calculations need to be made based on a set of
criteria. The D-Functions allow you to query the dataset based
on a defined set of specified criteria to control what is being
calculated by modifying the formula's criteria. Using database
functions is similar to advanced filtering; you must establish a
When using criteria range before the function itself.
the D-Function
formulas you
There are many D-Functions in Excel designed to help you extract
can change the criteria
and the formula subsets of data from within large datasets. The D-Functions
results will update include:
immediately.
›› DAVERAGE: Calculates the average of values in a field
of a list or database, that satisfy specified conditions
›› DCOUNT: Returns the number of cells containing
numbers in a field of a list or database that satisfy
specified conditions
›› DCOUNTA: Returns the number of non-blank cells
in a field of a list or database, that satisfy specified
conditions
›› DGET: Returns a single value from a field of a list or
database, that satisfy specified conditions
›› DMAX: Returns the maximum value from a field of a
list or database, that satisfy specified conditions
›› DMIN: Returns the minimum value from a field of a
list or database, that satisfy specified conditions
›› DPRODUCT: Calculates the product of values in
a field of a list or database, that satisfy specified
conditions
›› DSTDEV: Calculates the standard deviation (based on
a sample of a population) of values in a field of a list or
database, that satisfy specified conditions
›› DSTDEVP: Calculates the standard deviation (based
on an entire population) of values in a field of a list or
database, that satisfy specified conditions
›› DSUM: Calculates the sum of values in a field of a list
or database, that satisfy specified conditions.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 95

Lesson 3: Database Functions

Database ›› DVAR: Calculates the variance (based on a sample of

a population) of values in a field of a list or database,
Functions, that satisfy specified conditions
continued
›› DVARP: Calculates the variance (based on an entire
population) of values in a field of a list or database,
that satisfy specified conditions

Page 96 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 3: Database Functions

Basic Syntax of While each type of D-Function will return different values, they
all share the same arguments. So the component which changes
D-Functions will be the function name itself as the argument structure
remains consistent.

Here is the base syntax of the D-Function formula.

=Dfunction(Database,Field,Criteria)
The Arguments breakdown as follows:
›› Database: The range of cells containing the data being
searched.
›› Field: This is the column being calculated on in the
function.
›› If the column header has text then be sure that the
All of the text you enter here is wrapped within quotation
database marks.
functions use the ›› Numeric values do not require quotations.
same argument
format. ›› If using number to represent columns then enter a
1 for the first column, 2 for the second column, and
so on.
›› Criteria: A cell range containing the conditions
that must be met in order to be included in the final
calculation.
›› Any range of cells can be the criteria argument, it
must include at least one column label and a cell
below the column label that defines the condition
to be considered in the calculation.

Some common and useful database functions are:

DAVERAGE Used to average values in a field based on specified criteria

DSUM Sums the values in a field that meet the entered criteria
DCOUNT Counts the cells that contain numbers that meet the specified criteria
DMAX and Return the largest and smallest values respectively from records that meet
DMIN the specified conditions.
DPRODUCT Multiplies values in a field according to specific conditions
Returns a single record value from a record that meets the specified
DGET
conditions.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 97

Lesson 3: Database Functions

Creating a Standard database functions include those built-in functions that

are in the Database category found in the function library of the
D-Function Insert Function dialog.
Formula
Using the Insert Function Dialog
›› Set up a criteria range outside of the database range of
cells.

Database field headings

Criteria from within each field

›› Use field headings from the database that you want

the information to be filtered and calculated by.
›› The heading must match the headings in the
database exactly. Consider copying and pasting
these headings.
›› In the row under the field headings, type the
You will not find criteria you want your calculation to be based on.
the Database
category in the ›› Choose a cell to place the function in.
Function Library on the
Formulas Tab. They are ›› Click the [Insert Function] button on the Formula
found in the Database Bar or the [Insert Function] on the Formulas Tab.
category within the
Insert Function window.

›› In the Insert Function dialog, choose Database from

the Or select a category: drop-down list.
›› Select the database function you wish to use from
the Select a function: field, then click the [OK]
button.

Page 98 Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

 Lesson 3: Database Functions

Creating a ›› The Function Arguments dialog is displayed.

D-Function
Formula,
continued

›› In the Database field;

type your cell range, database name, or click
into the text field and select the Database data
set on the spreadsheet.
›› In the Field field;
type the name of the column heading field that
will be searched to extract your desired data, or
click on the field name in the spreadsheet.
›› In the Criteria field;
select the entire criteria range you made earlier.
›› Click the [OK] button to finish the formula.

Entering the Function Manually

Select the cell where the result is to be placed and entered
the function directly into the cell or formula bar using the
When you Autocomplete list. Adding a "D" to several standard functions
are manually will convert the function to a database function, which allows
entering a you to specify criteria to control and limit the results returned.
formula, when the
desired function is ›› Define the Criteria range of cells, as before.
highlighted, use the
Tab key to enter the ›› Select the result cell.
function into the
formula.
›› Begin typing the function;
›› =DSum(
›› Define the Database range of cells or enter the
Name if the range has been named.
While typing
in a formula, ›› Comma.
watch the
tooltip to help enter
›› Enter the field heading, exactly as written in the
the formula. The database range. ( If the Field name is text, it must
segment of the tooltip be within quotation marks.)
that is bold represents
›› Comma.
the formula argument
being entered. ›› Enter the criteria range of cells that was defined at
the beginning of this process.
›› Close the Parenthesis then tap the Enter key.

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020 Page 99

 Action 4.1 - Using a D-Sum function

Instructions: Results/ Comments:

1. Open the Class_List.xlsx workbook. The file is in the lesson folder.

2. Save the file as My_Class_List. Save the file in the lessons folder.

3. Select cells A1:N1. The selection covers all the column

headers in the data set.

4. Copy the selected cells. Right click and choose Copy or use the
shortcut of [Ctrl+C].

5. Select cell P1 and paste the cells. Right click and choose Paste or use the
shortcut of [Ctrl+V]. You are beginning
to establish the criteria range for the
D-Function.

6. Select cell P5 and type in;

< # of Students >.

7. Select cell P6 and type in;

< # of Classes >.

8. Select cell P7 and type in;

<Average # of Students >.

9. Select cell P8 and type in; These cells represent the desired
< Total Earnings >. information to be extracted from the raw
data set.

10. Select cell R2, type in; You will be looking for any 2010 entries in
< 2010 >. the Version column of data.

11. Select cell S2, type in; You will be looking for any Level 2 entries
< Level 2 >. in the Class column of data.

12. Select cell Z2, type in; You will be looking for any number of
< >5 >. students greater than five within the
Number of Student column of data.

13. Select cell Q5. This is where the DSUM formula will be
entered

Excel 2019: Data Analysis,Rel. 1.0, 5/6/2020

Lesson 3: Database Functions, Page 100
 Action 4.1
3.1 - Using aStandard
D-Sum function,
Databasecontinued
Functions, continued

Instructions: Results/ Comments:

14. Click the [Insert function] button on the The Insert Function dialog opens.
Formula bar.

15. In the Insert Formula dialog, select the You have chosen the type of function to
following: insert and the Function Arguments dialog
Category: Database opens.
Function: DSum
and click [OK].

16. In the Function Arguments dialog, input The Database field refers to the data set
the following: being queried.
Database: A1:N267 The Field field refers to which column of
Field: Z1 data will be summed.
Set Criteria: P1:AC2 The Criteria field refers to the search
and click [OK] to apply the formula. parameters.

17. Cell Q5 now lets you know how many

students took a 2010 Level 2 class, but
only if there more than five student in
the class.

18. Select cell Z2 and delete the current This removes the specific parameter of
contents. more the five students in the class from the
search criteria.

19. Notice that the value in cell Q5 changes. The value in the cell now show the full
total of students who took 2010 Level 2
classes.

20. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, 5/6/2020

Lesson 3: Database Functions, Page 101
 Action 4.2 - Using a D-Count function

Instructions: Results/ Comments:

1. The My_Class_List.xlsx file should still If not, re-open the file from the lessons
be open. folder.

2. Select cell Q6. In this cell you want to know how many
times the 2010 Level 2 class has be run.

3. Enter the following formula The formula has counted every instance of
< =DCOUNT(A1:N267,T1,P1:AC2) >. a 2010 Level 2 class within the dataset.

4. Select cell T2, type in; By adding a new criteria to the search
< >6-30-2016 >. parameters , the search results are
narrowed to only classes run after June 30th
2016.

5. Notice the changes in cells Q5 and Q6. The results in both cells are updated to
reflect the additional search parameter
added to the criteria range.

6. Select cell T2, type in; The formulas now return only 2010 Level 2
< <6-30-2016 >. classes run before June 30th 2016.

7. Clear the contents in cell T2. The search no longer is limited by any date
constraints.

8. Save the file. [Ctrl+S].

Excel 2019: Data Analysis,Rel. 1.0, 5/6/2020

Lesson 3: Database Functions, Page 102
 Action 4.3 - Using a D-Average function

Instructions: Results/ Comments:

1. The My_Class_List.xlsx file should still If not, re-open the file from the lessons
be open. folder.

2. Select cell Q7. In this cell you want to know the average
number of students who attended 2010
level 2 classes.

3. Enter the following formula The formula returns the average number
< =DAVERAGE(A1:N267,Z1,P1:AC2) >. of students that attended 2010 Level 2
classes. Although, it is showing the results
with decimals.

4. Double click into cell Q7. By double clicking a cell, you editing
the cell contents and in this case able to
modify the formula.

5. Edit the formula to this : By wrapping the D-Average formula

< =ROUND(DAVERAGE(A1:N267,Z1,P inside a ROUND function and specifying
1:AC2),0) >. the number of decimals as 0, forces the
results of the original formula to be
rounded to a whole value.
You could have also decreased the
number of decimals by click the [Decrease
Decimals] button in the Number Group on
the Home Tab.

6. Add another DSUM formula in cell Q8 The formula should be

to calculate the earnings from the 2010 =DSUM(A1:N267,AC1,P1:AC2).
Level 2 classes.

7. Right click cell Q8 and click the [$] The cell now has the Accounting
button in the Mini Toolbar. formatting applied.

8. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. [Decrease Decimals] , 5/6/2020

Lesson 3: Database Functions, Page 103
Lesson 3: Database Functions

Expanding So far, you have successfully been using the D-functions to

find information in the dataset by searching for a single item
D-Functions within each column of the dataset. When you need to find data
based on more than one data point within a column, it becomes
necessary to add more rows within the criteria range in the
formula. Each additional row allows you to expand the search
parameters used in the formula.

When adding search criteria along a single row in the formula,

you have been looking for all the criteria to be met in order to
get a result. As you add new search criteria in the same column
of the criteria range, the formula searches for all matching data
points within the column.

Below are some examples of how the criteria can be arranged to

search for specific information.

Looking only for version 2016 Level 2 classes

Looking only for versions 2013 and 2016, any classes

Looking for all version 2016 and all Level 2 classes

Looking for all version 2016 and only version 2013 Level 2 classes

Page 104 Excel 2019: Data Analysis, Rel. [Decrease Decimals] , 5/6/2020
 Action 4.5 - Expanding the Criteria Range

Instructions: Results/ Comments:

1. The My_Class_List.xlsx file should still If not, then re-open the file.
be open.

2. Change the criteria ranges from P1:AC2 You are adding another row each the
to P1:AC3 in cells Q5:Q8. criteria range to allow for multiple
parameters to be searched for within the
3. Select cell R3, type in; formulas.
< 2013 >.
You are now searching for all records of
4. Notice that all the values in cells Q5:Q8 2010 Level 2 and any 2013 version classes.
have been updated.
The values now reflect the expanded
search criteria .

5. Select cell R3 and press the [Delete] key. The cell contents are removed.

6. Select cell T2, type in; You are again search for classes run after
< >6-30-2016 >. June 30th 2016.

7. Select cell T3, type in; You are now also limiting the search to
< <10-1-2016 >. classes run before October 1st 2016. In this
manner you are able to search for classes
run within a specific time period.

8. Notice the values in cells Q5:Q8 are

updated.

9. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. [Decrease Decimals] , 5/6/2020

Lesson 3: Database Functions, Page 105
Lesson 3: Database Functions

Adding Drop- To continue the simplification of data retrieval from large

datasets using the D-FunctionS, you will now use the Data
Down Menu's Validation tool to add drop-down menus within the criteria
range.

The first set in this process will be to extract unique values from
columns within the dataset using the Advanced Filter tool used
earlier. Once you have extracted the unique values from each
column you can move on to the next step in the process.

Data Validation Lists

To create the drop-down menu use a Data Validation List. Select
the cell where to drop-down list needs to be placed. To access
the Data Validation tool; go to the Data Tab and click the [Data
Validation] button in the Data Tools Group.
The Data Validation dialog opens

›› In the Allow field, choose List from the available

options.

Page 106 Excel 2019: Data Analysis, Rel. [Decrease Decimals] , 5/6/2020
 Lesson 3: Database Functions

Adding Drop- ›› Click into the Source field,

Down Menu's, ›› You can type your list in manually here. If you are
continued typing the list in yourself, use a comma to separate
each list entry.
-OR-
›› Refer to a cell range that contains the list entries by
highlighting the desired cells.
›› You could add Input and Error Messages by clicking
the appropriate tab in the dialog.
›› Click the [OK] button.
The active cell now has a drop-down arrow when selected. Click
the arrow allows users to choose any item from the list you
created.

Excel 2019: Data Analysis, Rel. [Decrease Decimals] , 5/6/2020 Page 107
 Action 4.6 - Extracting Lists from the Dataset

Instructions: Results/ Comments:

1. The My [Link] file should still be If not, then re-open the file.
open.

2. Select cells C1 and D1. These are two of the columns that you will
extract unique values from.

3. Copy the cells and paste them into cells This is where the filtered data will be
P10 and Q10. placed.

4. Select cell I1 This is another of the columns that you

will extract unique values from.

5. Copy the cells and paste them into cell This is where the filtered data will be
R10. placed.

6. Select cell L1 This is another of the columns that you

will extract unique values from.

7. Copy the cells and paste them into cell This is where the filtered data will be
S10. placed.

8. On the Data Tab, in the Sort & Filter The Advanced Filter dialog opens.
Group, click the [Advanced] button.

9. Click the Copy to another location radio The returned values will now be copied to
button. another location in the spreadsheet.

10. Click into the List Range: field and type This is the dataset that contains the source
in; < A1:N267 >. data.

11. Click into the Criteria range: field and This is the field within the data set to be
type in: < P10 >. searched.

12. Click into the Copy to: field and type in; This is where the data will be placed, if
< P10:P11 >. you don't include the column header the
filter will return all columns from the
dataset.

13. Check the Unique records only checkbox. As implied by the name, only unique
values will be returned by the filter.

Excel 2019: Data Analysis,Rel. [Decrease Decimals] , 5/6/2020

Lesson 3: Database Functions, Page 108
 Action 4.6 - Extracting Lists from the Dataset, continued

Instructions: Results/ Comments:

14. Click the [OK] button. The dialog closes and the warning dialog
appears. This dialog asks if you want to
extend the copy location range to include
all the found records.

15. Click the [Yes] button. The data is placed.

16. Repeat steps 8 through 15 for each of the Make the necessary adjustments to the
remaining columns of required data. filtering for each column of data.

Excel 2019: Data Analysis, Rel. [Decrease Decimals] , 5/6/2020

Lesson 3: Database Functions, Page 109
 Action 4.7 - Using Data Validation to Create Drop-downs

Instructions: Results/ Comments:

1. The My_Class_List.xlsx file should still If not,then re-open it.
be open.

2. Select cell R2. This is the first cell where you will place
the drop-down menu for users to choose
items.

3. Click the Data Tab, then click the [Data The Data Validation dialog opens.
Validation] Button in the Data Tools
Group.

4. On the Setting Tab in the Data Validation This is the type of input setting the Data
dialog, choose List from the Allow: field Validation tool inserts.
drop-down.

5. Click into the Source: field. This is where you can define the list
manually or enter a cell range containing
the values to be used as the list.

6. Highlight cells P11:P14. These cells contains the list entries.

7. Click the [OK] button. The Data Validation dialog closes and a
drop-down arrow in displayed in cell R2.

8. Use the Autofill handle to pull down to Cell R3 now is also setup.
cell R3.

9. Repeat steps 2 through 8 to add drop- Each section of the criteria range used in
down list for cells S2:S3, X2:X3, and the D-FunctionS formulas now has drop-
AA2:AA3 respectively. down to make user input flawless. Make
sure you refer to the correct source rang of
cell in each Data Validation.

10. Clear any values currently within the The D_FUNCTION formulas should all
Criteria range. read 0.

11. Try using the new drop-downs to Depending on the choices made using the
modify the data being returned by the drop-downs, the formula values change.
D-Function formulas.

12. Save and close the file. [Ctrl+S] and [Ctrl+W].

Excel 2019: Data Analysis,Rel. [Decrease Decimals] , 5/6/2020

Lesson 3: Database Functions, Page 110
Excel 2019: Data Analysis
Rel. 1.0, 5/6/2020

Lesson 4: Data
Modeling
Lesson Overview

You will cover the following concepts in this

chapter:
›› Data Modeling
›› Understanding Relationships
›› Preparing the Tables
›› Creating Relationships
›› Managing the Data Model
›› Creating PivotTables
›› Working with a PivotTable
›› Adding a Calculated Column
Lesson Notes
 Lesson 4: Data Modeling

Data Modeling Excel has added data modeling as new feature, you no longer
have to add the plug-in as in the previous version. This tool
allows you to connect tables of data, creating a relational data
structure within Excel. These related tables are used in Pivot
Tables, Pivot Charts, and Power View reports greatly extending
their functionality. The data used in can be in the Excel file or
can be imported by using the Get External Data tools.

If the Power Pivot Tab is not displayed in the ribbon you may
need to enable Power Pivot from the Excel Options dialog.

Adding the Power Pivot Tab

›› Click the File Tab.
›› Click the Options option.
›› The Excel Options dialog opens.
›› Click the Customize Ribbon category on the left side of
the dialog.
›› Check the Power Pivot checkbox.

›› Click the [OK] button.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 113

Lesson 4: Data Modeling

Data Modeling, ›› The Power Pivot Tab should now be displayed on the
continued ribbon.

Data modeling tools are also found in the Data Tools Group on
the Data Tab.

Although you can easily build huge data models in Excel, there
are some considerations to keep in mind when working with
Data Models.
›› Large models containing many tables and columns are
overkill for most analyses.
›› Excel has a file size limit of 10MB, and having lots of
large tables of data will reach the limit quickly.
›› As the file size grows, it will negatively affecting
other applications and reports sharing the same
system resources due to high demands of system
memory.
›› Avoid calculated columns, since they will need regular
updating to keep current data in the Data Model.
(Draws more system resources)
›› Ensure that the data has a Primary key column of data
to create relationship between the data tables.
›› It is recommended to use a 64 bit version of the
program with a minimum of 8GB of RAM, 16GB
preferred.

Page 114 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Lesson 4: Data Modeling

Data Modeling, Other Limitations

continued The table below lists other constraints within a Data Model

Object Maximum Limit

Characters in a table or column name 100 characters
Number of tables in a model 2,147,483,647
Number of columns and calculated columns in a table 2,147,483,647
Concurrent requests per workbook 6
Number of connections 5
Number of distinct values in a column 1,999,999,997
Number of rows in a table 1,999,999,997

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 115

Lesson 4: Data Modeling

Understanding To create a Data Model, it is necessary to understand how Excel

interprets data and values that contribute to the Data Model
Relationships itself. We will begin by examining the Relational Database
concepts to gain a better understanding of Data Models'
foundational structure.

Relational Databases
A relational database's structure inherently recognizes
relationships among the data. These relationships let you quickly
search and retrieve specific information, view the same dataset in
multiple ways and reduce data errors and redundancy.

To avoid repeating all the master information in every detailed

table, you create relationships using one unique field, then let
Excel do the rest.

There are two basic types of relationships that will be established

between Tables:

›› One to one relationship - for every record in the master

table, there is one matching record in the detail table.
›› One to many relationship - for every record in the
master table, there can be many records in the detail
table that link back to the master table.

For example, you may have an Employee Table with ID numbers.

In the Employee Table, the Employee ID Number is unique for
each individual employee. This is considered a Primary Key.
No two employees can have the same Employee ID Number.

In a related table, you may have accounts to which each

employee is assigned. Because the field Employee ID Number
appears in both tables, these tables can be related. However,
in the second table with the accounts, each employee may be
assigned multiple accounts. Therefore, this is a one to many
relationship from the Employee Table to the Accounts Table.
These tables are related because the Employee ID Number field
appaers in both tables, which makes this a Relational Database.
The Employee ID Number in the Accounts Table is called a
Foreign Key because it is not a unique identifier and, therefore,
cannot be used as a Primary Key.

Page 116 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Lesson 4: Data Modeling

Preparing the Structuring the Data

Tables First, you need to determine if the information can and should
be related. When you have data in two or more Tables, it may
be more efficient to combine the information from the Tables to
draw a conclusion or extract more accurate data. If this is the
case, don't waste time trying to physically combine the data on
one spreadsheet, use Excel's Data Modelling tools to extract the
information as needed.

Convert the Data to a Table

›› Select any cell in the dataset.
›› On the Home Tab, in the Style Group click the Format
as Table button.
›› Choose any of the available formatting options.
›› In the Format as Table dialog, check to ensure all the
connected data are included in the Where is your
table: field
›› Click the [OK] button.
›› Repeat these steps for each dataset to be used in the
Data Model.

Rename the Table

Once you have created the tables, you should rename the tables
By default
tables are to make the process much simpler. While this is not necessary, it
named is extremely helpful.
numerically as they ›› On the Table Tools Design Tab, click into the Table
are created. Name: field in the Properties Group.

›› Type a new name for your Table.

Table name ›› Tap the [Enter] key to apply the name.
can not include
blank spaces or ›› If the [Enter] key is not used and you click away
special characters. from the field the name is not applied.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 117

 Action 5.1 - Creating and Naming the Tables

Instructions: Results/ Comments:

1. Open the [Link] file. It is located in the lessons folder.

2. Save the file as [Link]. [F12].

3. Activate the Classes worksheet.

4. Click into any cell containing a value.

5. On the Home Tab , in the Styles Group, The table styles are displayed.
click the [Format as Table] button.

6. Choose the first table option from the The Format As Table dialog opens.
menu.

7. Check the Where is the data for your All the connected data should be listed as
Table: field to see that it reads; the data to be converted into a table.
=$A$1:$G$980

8. Check the My table has headers This will set the row 1 as the header row of
checkbox. the table.

9. Click the [OK] button. The formatting is applied to the data range
and the Table Tools Design Tab is added to
the ribbon.

10. Click the Table Tools Design Tab. The table tools are displayed.

11. In the Properties Group, click into the You must tap the [Enter] key after entering
Table Name: field and type in: a name in order for it to be applied. If you
< Classes > then tap the [Enter] key to forget. the name will not be applied.
apply the name to the table.

12. Format the data on each of the other Repeat steps 4 through 11 on each
sheets as tables and name each table with worksheet. When naming tables you can
the same name as the worksheet. not have blank spaces, so add underscores
or camel case the names.

13. Save the file and leave it open. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 118
 Lesson 4: Data Modeling

Creating Find the Related Data

Relationships Look at the data in the Tables you have created and determine
which Fields can be used to connect one table to another. These
matching fields are what are used to create the relationships.

As mentioned earlier, if the two tables both have a column with

Relationships the same type of data, that column can be used to connect the
only exist tables. An Emp_ID column in the employee data table uniquely
between tables identifies each employee and that data can connect to the Emp_
of data. If the data
is not formatted as ID column in the sales data tables.
a table, apply table
formatting to the
data.. Creating Relationships
›› Choose any table.
›› On the Data Tab,click the [Relationships] button in the
Data Tools Group.
›› The Manage Relationships dialog opens.

›› Since there are no existing relationships, the dialog

is empty.
›› Click the [New] button to open the Create Relationship
dialog.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 119

Lesson 4: Data Modeling

Creating ›› In the Create Relationship dialog,

Relationships, ›› The Table: field drop-down, allows you to choose

continued from any existing tables. This would be the many
side of a one-to-many relationship. This table has
many items that relate back to a single item in the
other table.
›› The Column (Foreign): field drop-down, allows
you to choose which column or field of data in the
selected table will be used to connect to another
table. This column may contain duplicate values.
›› The Related Table: field drop-down, allows you to
choose what table will be connected to the selected
table in the Table: field. This is the one side in a
one-to-many relationship.
›› The Related Column (Primary): field drop-down,
allows you to select which column will connect to
the Column (Foreign) field. This column should
contain unique values only.
›› Click the [OK] button to establish the relationship.
›› The relationship is now displayed in the Manage
Relationships dialog.
›› Continue creating all the necessary relationships.
›› Click the [Close] button once all relationships have
been made.

Page 120 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Action 5.2 - Creating Relationships

Instructions: Results/ Comments:

1. Click the [Relationships] button in the The Manage Relationships dialog opens.
Data Tools Group on the Data Tab.

2. Click the [New] button. The Create Relationship dialog opens.

3. In the Table: field choose Companies from In this case you are setting Sales_Reps
the drop-down, then from the Column column in the Companies table as the
(Foreign): field drop-down choose Sales_ data that will connect to another table.
Reps. This is the many side of the one-to-many
relationship

4. In the Related Table: field choose Sales_ You are now setting Emp-ID as the column
Reps from the drop-down, then from the as the unique primary key in the Sales_
Related Column (Primary): field drop- Reps table, this will be the one side of the
down choose Emp-ID. one-to-many relationship between the
Sales_Reps and Companies tables.

5. Click the[OK] button. The relationship is added in the Manage

Relationships dialog.

6. Click the [New] button. The Create Relationship dialog opens.

7. In the Table: field choose Clients from The many side in the relationship to the
the drop-down, then from the Column companies table, many clients work for the
(Foreign): field drop-down choose same company.
Company_ID.

8. In the Related Table: field choose The one side in the relationship.
Companies from the drop-down, then
from the Related Column (Primary):
field drop-down choose Company_ID.

9. Click the[OK] button. The relationship is added in the Manage

Relationships dialog.

10. Click the [New] button. The Create Relationship dialog opens.

11. In the Table: field choose Classes from

the drop-down, then from the Column
(Foreign): field drop-down choose
Client_ID.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 121
 Action 5.2 - Creating Relationships, continued

Instructions: Results/ Comments:

12. In the Related Table: field choose Clients
from the drop-down, then from the
Related Column (Primary): field drop-
down choose Client_ID.

13. Click the[OK] button. The relationship is added in the Manage

Relationships dialog.

14. Click the [New] button. The Create Relationship dialog opens

15. In the Table: field choose Classes from

the drop-down, then from the Column
(Foreign): field drop-down choose
Course_ID.

16. In the Related Table: field choose Courses

from the drop-down, then from the
Related Column (Primary): field drop-
down choose Course_ID.

17. Click the[OK] button. The relationship is added in the Manage

Relationships dialog.

18. Once all the relationships have been The Manage Relationships dialog is closed.
created, click the [Close] button.

19. Save the file and leave it open. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 122
 Lesson 4: Data Modeling

Managing the Once relationships are established you may need or want to
manage the data model. When managing a data model,Excel
Data Model will open the Power Pivot for Excel-(File name) window from
within this environment you are able to see, edit , and create new
relations.

This window To Open Excel's Power Pivot window

will only
show existing ›› On the Data Tab, click the [Manage Data Model]
relations not just raw button in the Data Tools Group.
data tables.

Tables do
not have to be
on separate
- OR -
worksheets to build
data models. ›› On the Power Pivot Tab, click the [Manage] button in
the Data Model Group.

›› The Power Pivot for Excel opens.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 123

Lesson 4: Data Modeling

Managing the Power Pivot Views

Data Model, The initial view is of the tabular data, where any related tables
are displayed in a similar fashion as worksheets in a workbook.
continued
You can change to a diagram view, which displays the tables as
small boxes with the fields and lines to show the connections
from one table to another.

The Data View

Table names
can be changed
by right clicking Using the tools available on the Home Tab you are able to apply
the table tab and formatting, sort and filter the data, add new columns to a table,
choosing Rename or
by double clicking the add in formulas, and get external data from outside of Excel.
tab name. Remember
to tap the [Enter] key
when done to apply
the change.

The Design and Advanced Tabs offer other tools for functions,
calculations, freezing, examining and creating relations,
properties, as well as more features.

Page 124 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Lesson 4: Data Modeling

Managing the The Diagram View

Data Model, While the ribbon tabs are available and offer the same
continued functionality is in the Data View, they would be much more
difficult to see the changes being made in the Diagram View.

›› In the View Group, click the [Diagram View] button.

›› A diagram of the data relations replaces the Data

View.

›› In this view you are able to see all the data tables in the
file as well as the connections between the tables.
›› To move the tables, click and drag the Title Bar of
the table.
›› As you move the tables around the connection
lines are maintained, so you are able to see the
connections
›› To resize the table, use the double headed arrow
cursor that appears as you hover over the table
border.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 125

Lesson 4: Data Modeling

Managing the Adding a New Connection

Data Model, You are able to create the connections from within the Diagram
View.
continued
›› Select the first table of the connection.
›› Click the field that can be used to relate to another
table.
›› Drag the field over the related field of the other table
field.

›› As you drag the connection, a line appears that

shows which two field are being used for the
connection.
›› When the connection is made the connection line is
displayed between the tables.

Page 126 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Action 5.3 - Managing the Data Model

Instructions: Results/ Comments:

1. Click the [Manage data Model] button in The Power Pivot for Excel-ExcelClassAnalysis.
the Data Tools Group on the Data Tab. xlsx window opens. This window has
a ribbon with tabs like the regular Excel
window.

2. Click the [Diagram View] button in the The diagram view of the existing named
View Group on the Home Tab. tables and the connections added is
displayed.

3. Arrange the tables in so you can see Click and drag the Title Bar of the table
them all and the connection lines. to reposition it. You may also need to
zoom out to see all the tables. If so, use the
Zoom Slider in the lower right corner of
the window.

4. Resize each table to see all the fields. Hover over the bottom of the table, when
the doulbe-headed arrow cursor appears,
click and drag to resize the table.

5. Notice all the tables are shown with their

relations.

6. Close the Power Pivot for Excel- Click the close button in the upper right
[Link] window and save corner of the window and [Ctrl+S].
the file

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 127
Lesson 4: Data Modeling

Creating Once the Data Model is complete, it is time to put it to use.

The best way to visualize the data is by using a PivotTable or
PivotTables PivotChart. This is done from within Excel or the Power Pivot for
Excel window.

Creating PivotTables

In the Power Pivot for Excel window

›› Click the [PivotTable] button on the Home Tab.
›› Clicking the drop-down arrow of the button offers
more choices.

›› Creating multiple PivotTables and Charts from this

view allows each PivotTable and Chart to work
indepentantly from the other.
›› The Create PivotTable dialog opens.

It is easier to
visualize the
Pivot Table data
in it's own worksheet
than in an existing ›› In this dialog, you are able to set where the new
worksheet.
PivotTable will be placed within the workbook.
›› Choose where the new PivotTable will be placed and
click the [OK] button.
›› A blank PivotTable is added to a New Worksheet or on
the existing worksheet you chose.

Page 128 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Lesson 4: Data Modeling

Creating In Excel

PivotTables, If the Power Pivot for Excel window was closed and you are back
in an Excel window, it is still possible to create a PivotTable from
continued
the established Data Model.

›› Select any table of data included within the Data

Model.
›› Click the [PivotTable] button in the Tables Group on
the Insert Tab.
›› The Create PivotTables dialog opens.

›› In the dialog, you are able to determine source of

the PivotTable data, where the PivotTable will be
added, and whether or not to add this to the Data
Model.
›› If the Add this data to the Data Model checkbox is
checked: all the related tables in the Data Model are
displayed in the Pivottable Fields pane.
›› If the Add this data to the Data Model checkbox
is not checked: only the selected table fields are
displayed in the PivotTable Fields pane. Although,
you are still able to access the other tables from
within the PivotTable Fields pane.
›› Click the [OK] button when finished defining the
PivotTable attributes.
›› A blank PivotTable is added to a New Worksheet or on
the existing worksheet you chose.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 129

Lesson 4: Data Modeling

Working with a Once the PivotTable is created, it will be empty of content until
fields are added. The blank table is located on the worksheet and
PivotTable the PivotTable Field pane is displayed on the right side of the
Excel window.

Blank PivotTable

PivotTable Field pane

If all the Tables are not displayed in the PivotTable Field pane,
click the [All] button at the top of the pane.

Should the Add this data to the Data Model checkbox not have
been checked, a [More Tables] button is shown below the list of
fields in the current table. Clicking that button will open a dialog
asking if you want to create a new PivotTable. You must click the
[Yes] button to continue.

Page 130 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Lesson 4: Data Modeling

Working with a Adding Data to a PivotTable

PivotTable,
Once all the tables are listed in the PivotTable Fields pane, you
can expand any given table by clicking the [Expand/Collapse]
continued buttons to the left of the table name.
[Collapse] Button

[Expand] Button

›› Expand the table fields.

›› Select the field to include in the PivotTable and drag it
into the appropriate PivotTable area at the bottom of
the PivotTable Filed pane.

›› Filters area: allows you to filter, based on one or

more fields that would isolate the focus of the
PivotTable.
›› Columns area: set the fields to use as column
headings.
›› Rows area: set the fields to use as the rows of data
in the PivotTable. Typically this area has at least
one field, although it’s possible to have no fields.
›› Values area: is used to calculate and/or count data
that you want to measure.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 131

Lesson 4: Data Modeling

Working with a The power of a PivotTable is that you can rearrange the fields
to see your data from different perspectives. With the Filter,
PivotTable, Columns, and Rows areas field, it is easy to filter the data to look
continued at specific subsets that would otherwise be difficult to see within
the raw data sets.

After dragging a field to an area, it's data is added to the

PivotTable.

Page 132 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Action 5.4 - Creating the PivotTable

Instructions: Results/ Comments:

1. The [Link] file should If not, reopen it.
still be open.

2. On the Data Tab, in the Data Tools The Power Pivot for Excel window opens.
Group, click the [Data Model] button.

3. In the Power Pivot for Excel window, on The Create PivotTable dialog opens.
the Home Tab, click the [PivotTable]
button.

4. In the Create PivotTable dialog, choose New Worksheet is the default choice, so
New Worksheet option and click the [OK] simply ensure that is the active option.
button.

5. Examine the PivotTable Field pane. All the tables in the data model should be
listed in the upper field of the PivotTable
Filed pane.

6. Click the [Expand] arrow button to the The list of fields ( these are the columns in
left of the Sales_Reps table. that table) in this table are displayed.

7. Drag the Last Name field down into The Last Name field is added to the Filters
the Filters field at the bottom of the field. You will be able to filter the data
PivotTable Field pane. based on individual sales reps.

8. Click the [Collapse] arrow button for the The table's fields are collapsed. Since this
Sales_Reps table. is the only field needed in the PivotTable
from the Sales_Reps table, there is no need
to leave the table field list expanded.

9. Click the [Expand] arrow button to the The list of fields in this table are displayed.
left of the Classes table.

10. Drag the Date field down into the Rows The dates are added in the Rows field and
field. displayed in the PivotTable. Every date is
listed.

11. Right click any date in the worksheet and The Grouping dialog opens. You can
choose Group from the menu. choose how to group the dates by a single
or multiple choice.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 133
 Action 5.4 - Creating the PivotTable, continued

Instructions: Results/ Comments:

12. Select Months from the list and click the The dates are now grouped by months
[OK] button. that can be expanded as needed to see a
more detailed view of the data. A new
Date(Month) filed is added to the field list
and the Rows field.

13. Right click any Month on the worksheet All the dates within the months are shown.
and from the Expand/Collapse options
choose Expand Entire Field.

14. In the Rows field of the PivotTable field, The individual dates are removed from
drag the Dates field out. the PivotTable while the Months are still
displayed.

15. Drag the Name field down below the Now all the Names of the classes taught
Date(Month) field in the Rows field. that month are displayed. If not then
repeat step 13 to display the data.

16. Drag the Version field down below the The Version of the class is added to the
Name field in the Rows field. PivotTable.

17. Drag the Client_ID field down into the A count of the clients who took that given
Values field. class is added in the second column of the
PivotTable.

18. Collapse the Classes table field list. In the PivotTable field pane click the
[Collapse] button beside the Classes table.

19. Save the file. [Ctrl+S].

20. Expand the Courses table. The list of fields in the Course table is
displayed.

21. Drag the Price field to the Values field. The Price field is added as a new column
in the PivotTable. The data is not coming
in correctly, you will need to add a new
calculated column in the Data Model.

22. Drag the Price field out of the Values The data is removed from the PivotTable.
field.

23. Save the file. [Ctrl+S].

Excel 2019: Data Analysis, Rel. 1.0, /6/2020
Lesson 4: Data Modeling, Page 134
 Lesson 4: Data Modeling

Adding a In a regular PivotTable, you are able to add calculated field or

items. When working in a Power Pivot PivotTable this feature
Calculated is not available. In the exercise you just completed, the price
Column column of data did not have a robust enough connection to allow
you to see a total for each class taught based on the number of
students. While the tables do have a connection, the information
does not come across as needed. In cases such as these, it may be
necessary to add a calculated column into the Data Model itself
Columns to combine the data from two tables.
added in the
data model Since the connection exists between the tables in the model, Excel
do not show in the is able to use functions to generate desired data from the separate
normal view of the
table.
table on worksheets.
Inserting a Function column
In this example: the price for each class in the Courses Table
needs to be added to the Classes Table. The Related function
will enter the price based on the connection of the Course_ID
fields.

›› Open the Power Pivot for Excel window by click the

[Data Model] button on the Data Tab.
›› In the Data View, select the table that requires the new
column of data.
›› Activate the Design Tab.
›› Click into the empty cell below the Add Column
header in the table.
›› Click the [Insert Function] button in the Calculations
Group.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020 Page 135

Lesson 4: Data Modeling

Adding a ›› The Insert Function dialog opens.

Calculated
Column,
continued

›› Use the Select a category: field drop-down to

narrow the list of functions listed in the Select a
function: field.
›› Select All from the Select a category: field
›› Select Related in the Select a function: field.
›› Click the [OK] button.
›› The Insert Function dialog closes and the function is
added in the formula Bar.

›› Select the table that contains the data to find.

›› In this case, select the Courses table.
›› Click the Column header containing the required data.
›› In this case, Price is the necessary column.
›› Excel will compare the data in the related column
(Course_ID) to determine the correct price to
attribute to the records in the Classes table.
›› Tap the [Enter] key to apply the function.
›› Close the Power Pivot for Excel window.
›› The new column can be used within the PivotTable.

Page 136 Excel 2019: Data Analysis, Rel. 1.0, /6/2020

 Action 5.5 - Adding a Calculated Column to a Table

Instructions: Results/ Comments:

1. The [Link] file should If not, repoen it.
still be open.

2. On the Data Tab, in the Data Tools The Power Pivot for Excel window opens to
Group, click the [Data Model] button. the Data View.

3. Select the Classes Table tab. The table tabs are listed at the lower left of
the window.

4. Click into the blank cell below the Add This will add a new column as you enter
Column header. data or in this case a calculated column.

5. Activate the Design Tab. Click the Design Tab in the ribbon.

6. Click the [Insert Function] button in the The Insert Function dialog opens.
Calculations Group.

7. Leave the Select a category: field set to All available functions are listed in the
All. Select a Function: field.

8. From the Select a Function: field list The Insert Function dialog closes and the
choose Related and click [OK]. function is added in the Formula Bar.

9. Select the Course Tab, then select the Price Since these tables have a connection based
column header and tap the [Enter] key. on the Course_ID, the Classes table will
now compare the Course_ID columns and
return the price of each class. The ata can
now be used to see how much revenue was
earned from each class in the PivotTable.

10. Double click the column header and type To apply a name to the column.
in;
< Cost_Per >
and tap the [Enter] key.

11. Close the Power Pivot for Excel window. Click the [Close] button in the upper right
corner of the window. The Power Pivot for
Excel window closes and you are back in
the PivotTable worksheet.

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 137
 Action 5.5 - Adding a Calculated Column to a Table, continued

Instructions: Results/ Comments:

12. Expand the Classes table in the PivotTable Click the [Expand] button beside the
Field pane list. Classes table. All the fields in the table
are displayed, including the newly added
column.

13. Drag the new Cost_Per field into the The price data is calculated and displayed
Values field. in the PivotTable.

14. Save and close the file. [Ctrl+S] and [Ctrl+W].

Excel 2019: Data Analysis, Rel. 1.0, /6/2020

Lesson 4: Data Modeling, Page 138
Excel 2016: Data Analysis
Rel. 1.0, 5/6/2020

Appendix A: Excel 2013

Adding the Power Pivot
Add-in
Lesson Overview

You will cover the following concepts in this

chapter:
›› Adding the Add-In
Lesson Notes
 Appendix A: Adding the Power Pivot Add-in

Adding the Add- Accessing the Power Pivot Add-ins

In If you are running Excel 2013 and the Power Pivot Tab is not
displayed in the ribbon, you will need to add it to the program.
This can be done from either the Excel Options Window or from
the Developer Tab.

From the Options Window

›› Click the File Tab and then the Options link.

›› When in the Options window, choose the Add-Ins

category from the list on the left side of the screen.

›› At the bottom of the screen, click the Manage: field

drop-down and choose COM Add-ins from the list.

›› Click the [Go] button to open the COM Add-ins dialog.

Excel 2016: Data Analysis, Rel. 1.0, 5/6/2020 Page 141

Appendix A: Adding the Power Pivot Add-in

Adding the Add- ›› Check the Microsoft Power Pivot for Excel checkbox and
click the [OK] button.
In,
continued

›› The Power Pivot Tab is added to the ribbon.

From the Developer Tab

The Developer Tab has to have been activated in the program in
order to use this method.

›› Activate the Developer Tab.

›› In the Add-ins Group, click the [COM Add-ins]
button.

›› The COM Add-ins dialog opens.

›› Check the Microsoft Power Pivot for Excel checkbox and

click the [OK] button.
›› The Power Pivot Tab is added to the ribbon.

Page 142 Excel 2016: Data Analysis, Rel. 1.0, 5/6/2020

 Action 1-1: Adding the Power Pivot Tab to Excel, method 1

Instructions: Results/ Comments:

1. Click the File Tab. The backstage is displayed. [Alt-F-T]

2. From the list of categories on the left of The Options Window opens.
the backstage, choose Options.

3. In the Options Window, choose the Add- The Excel Add-ins controls are displayed
ins category. in the Options Window.

4. Click the drop-down arrow of the This field allows you to choose which set
Manage: field and choose COM Add-ins. of Add-ins to search through.

5. Click the [GO] button. The COM Add-ins dialog opens, showing a
list of checkbox options.

6. Check the Microsoft Power Pivot for Excel The Power Pivot Tab is now available on
checkbox and click the [OK] button. the ribbon.

Excel 2016: Data Analysis, Rel. 1.0, 5/6/2020

Appendix A: Adding the Power Pivot Add-in, Page 143
 Action 1-1: Adding the Power Pivot Tab to Excel, method 2

Instructions: Results/ Comments:

1. Activate the Developer Tab If the tab is not displayed on the ribbon,
you will need to turn it on from within the
Options Window/ Customize Ribbon.

2. In the Add-ins Group, click the [COM The COM Add-ins dialog opens.
Add-ins] button.

3. Check the Microsoft Power Pivot for Excel The Power Pivot Tab is now available on
checkbox and click the [OK] button. the ribbon.

Excel 2016: Data Analysis, Rel. 1.0, 5/6/2020

Appendix A: Adding the Power Pivot Add-in, Page 144
 TCW Book
Codes
Microsoft Office excel
Excel Level 1 L-1
Associate Exam MO-200
Excel Level 2 L-2
Excel Level 3 L-3 Import data into workbooks
Excel Formulas FM Import data from .txt file DA
Excel Data Analysis DA Import data from .csv files DA
Excel Charts CH
Excel PivotTables PT
Navigate within workbooks
Excel Data Analysis with
PowerPivot PPT Search for data within a workbook L-1
Navigate to named cells, ranges, or workbook
L-2
elements
Insert and remove hyperlinks L-3

Format worksheets and workbooks

Modify page setup L-1
Adjust row height and column width L-1
Customize headers and footers L-1

Customize options and views

Customize the Quick Access toolbar L-1
Display and modify workbook content in
L-2
different views
Freeze worksheet rows and columns L-2
Change window views L-2
Modify basic workbook properties L-2
Display formulas L-1

Configure content for collaboration

Set a print area L-1
Save workbooks in alternative file formats L-1
Configure print settings L-1
Inspect workbooks for issues L-1
 TCW Book Manipulate data in worksheets
Codes Paste data by using special paste options L-1
Fill cells by using Auto Fill L-1
Excel Level 1 L-1
Excel Level 2 L-2 Insert and delete multiple columns or rows L-1
Excel Level 3 L-3 Insert and delete cells L-1
Excel Formulas FM
Excel Data Analysis DA Format cells and ranges
Excel Charts CH
Merge and unmerge cells L-1
Excel PivotTables PT
Excel Data Analysis with Modify cell alignment, orientation, and
L-1
PowerPivot PPT indentation
Format cells by using Format Painter L-1
Wrap text within cells L-1
Apply number formats L-1
Apply cell formats from the Format Cells dialog
L-1
box
Apply cell styles L-1
Clear cell formatting L-1

Define and reference named ranges

Define a named range L-2 / FM
Name a table DA

Summarize data visually

Insert Sparklines L-2
Apply built-in conditional formatting L-2
Remove conditional formatting L-2

Create and format tables

Create Excel tables from cell ranges L-2
Apply table styles L-2
Convert tables to cell ranges L-2

Modify tables
Add or remove table rows and columns L-2
Configure table style options L-2
Insert and configure total rows L-2
 TCW Book Filter and sort table data
Codes Filter records L-2
Sort data by multiple columns L-2
Excel Level 1 L-1
Excel Level 2 L-2
Excel Level 3 L-3 Insert references
Excel Formulas FM Insert relative, absolute, and mixed references L-1
Excel Data Analysis DA Reference named ranges and named tables in
Excel Charts CH L-2
formulas
Excel PivotTables PT
Excel Data Analysis with
PowerPivot PPT
Calculate and transform datas
Perform calculations by using the AVERAGE(),
L-1
MAX(), MIN(), and SUM() functions
Count cells by using the COUNT(), COUNTA(),
DA
and COUNTBLANK() functions
Perform conditional operations by using the
FM
IF() function

Format and modify text

Format text by using RIGHT(), LEFT(), and
DA
MID() functions
Format text by using UPPER(), LOWER(), and
DA
LEN() functions
Format text by using the CONCAT() and
DA
TEXTJOIN() functions

Create charts
Create charts L-2 / CH
Create chart sheets L-2 / CH

Modify charts
Add data series to charts L-2 / CH
Switch between rows and columns in source
L-2 / CH
data
Add and modify chart elements L-2 / CH
 TCW Book
Codes
Microsoft Office excel
Excel Level 1 L-1
Expert Exam MO-201
Excel Level 2 L-2
Excel Level 3 L-3 Manage workbooks
Excel Formulas FM Copy macros between workbooks L-3
Excel Data Analysis DA Reference data in other workbooks L-3
Excel Charts CH Enable macros in a workbook L-3
Excel PivotTables PT Manage workbook versions L-2
Excel Data Analysis with
PowerPivot PPT
Prepare workbooks for collaboration
Restrict editing L-2
Protect worksheets and cell ranges L-2
Protect workbook structure L-2
Configure formula calculation options FM
Manage comments L-2

Use and configure language options

Configure editing and display languages L-1
Use language-specific features L-1

Fill cells based on existing data

Fill cells by using Flash Fill L-1
Fill cells by using advanced Fill Series
L-2
options

Format and validate data

Create custom number formats L-1
Configure data validation L-3 / FM
Group and ungroup data L-3
Calculate data by inserting subtotals and
L-3
totals
Remove duplicate records DA
 TCW Book Apply advanced conditional formatting and filtering
Codes Create custom conditional formatting rules L-2
Create conditional formatting rules that use
Excel Level 1 L-1 L-2
formulas
Excel Level 2 L-2
Excel Level 3 L-3 Manage conditional formatting rules L-2
Excel Formulas FM
Excel Data Analysis DA Perform logical operations in formulas
Excel Charts CH Perform logical operations by using
Excel PivotTables PT nested functions including the IF(), IFS(), FM
Excel Data Analysis with SWITCH(),
PowerPivot PPT
SUMIF(), AVERAGEIF(), COUNTIF(),
SUMIFS(), AVERAGEIFS(), COUNTIFS(), FM
MAXIFS(),
MINIFS(), AND(), OR(), and NOT()
FM
functions

Look up data by using functions

Look up data by using the VLOOKUP(),
HLOOKUP(), MATCH(), and INDEX() FM
functions

Use advanced date and time functions

Reference date and time by using the
FM
NOW() and TODAY() functions
Calculate dates by using the WEEKDAY()
FM
and WORKDAY() functions

Perform data analysis

Summarize data from multiple ranges by
L-3
using the Consolidate feature
Perform what-if analysis by using Goal Seek
L-3
and Scenario Manager
Forecast data by using the AND(), IF(), and
FM
NPER() functions
Calculate financial data by using the PMT()
FM
function
 TCW Book Troubleshoot formulas
Codes Trace precedence and dependence FM
Monitor cells and formulas by using the
Excel Level 1 L-1 FM
Watch Window
Excel Level 2 L-2
Excel Level 3 L-3 Validate formulas by using error checking
FM
Excel Formulas FM rules
Excel Data Analysis DA Evaluate formulas FM
Excel Charts CH
Excel PivotTables PT Create and modify simple macros
Excel Data Analysis with
Record simple macros L-3
PowerPivot PPT
Name simple macros L-3
Edit simple macros L-3

Create and modify advanced charts

Create and modify dual axis charts CH
Create and modify charts including Box &
Whisker, Combo, Funnel, Histogram, Map, CH
Sunburst, and Waterfall charts

Create and modify PivotTables

Create PivotTables PT
Modify field selections and options PT
Create slicers PT
Group PivotTable data PT
Add calculated fields PT
Format data PT

Create and modify PivotCharts

Create PivotCharts PT
Manipulate options in existing PivotCharts PT
Apply styles to PivotCharts PT
Drill down into PivotChart details PPT