pps Activity #5 – Excel Tables

MIS-3210Unit 2, Activity #5
2-Dimensional Data and the use
of “Tables” in Excel
Related materials:
Activity #5-Tables-INSTRUCTIONS (PDF)
Detailed set of step-by-step instructions to complete Activity #5.
In this activity, we will develop a
greater understanding of the role of
2-Dimensional (2D) data structures in
Excel for conducting data analysis.
Note the structure of repeating
rows of structured, similar data
with identifying column headings.
Examples of 2D
data structures
Disney Stock Performance – 2017-2022
Date
11/1/2017
12/1/2017
1/1/2018
2/1/2018
3/1/2018
4/1/2018
5/1/2018
6/1/2018
7/1/2018
8/1/2018
9/1/2018
10/1/2018
11/1/2018
12/1/2018
1/1/2019
2/1/2019
3/1/2019
4/1/2019
5/1/2019
6/1/2019
7/1/2019
8/1/2019
9/1/2019
10/1/2019
Open
98.13
104.58
108.95
108.62
103.2
100.18
100.18
99.69
104.15
113.68
111.8
117.28
115.2
116.65
108.1
111.97
113.45
111.59
137.49
132.02
140.45
143.34
136.37
130.8
High
106.26
112.67
113.19
110.65
105.94
102.71
105.49
109.49
114.68
117.9
117.46
119.69
120.2
116.92
113.18
115.8
115.48
142.37
138.17
143.51
147.15
144.53
140.08
133.68
Low
97.9
103.87
108.47
100.26
98.15
97.7
97.68
99.2
103.5
111.44
108.23
110.32
111.52
100.35
105.94
109.15
107.32
111.38
130.55
131.49
139.22
131.02
128.92
127.54
Close
104.82
107.51
108.67
103.16
100.44
100.33
99.47
104.81
113.56
112.02
116.94
114.83
115.49
109.65
111.52
112.84
111.03
136.97
132.04
139.64
143.01
137.26
130.32
129.92
Adj Close
101.124489
103.719643
105.682114
100.323608
97.678406
97.571419
96.735069
101.928238
110.437653
109.815689
114.638885
112.570404
113.217415
107.492332
110.173538
111.477608
109.689461
135.316269
130.445786
137.954041
141.283356
136.442535
129.543869
129.146255
Volume
172385900
204095500
161521100
187750900
154204900
159399600
195097400
205417800
153023200
135849100
143771100
178593100
179965100
163486600
154319500
162350800
333771400
355199600
208497300
195325700
162259500
214762100
161822400
164542300
What we learn about in this activity
• Recognizing the role of 2D data
for most data analytics
• Cleansing and scrubbing data
prior to analysis
• Working with “ranges” of Excel
data (selecting, naming, etc.)
• Basic data manipulation (sorting,
filtering, search/replace)
• Adjusting layout and order of
Excel “range” data (sort, freezing
headings, etc.)
• Understanding the difference
between an Excel “Range” and
an Excel “Table”
• “Table” skills including:
• Converting a “range” to a “table”
(and reverting)
• Styling/designing table layout
• Adding a “total row” and inserting
summary calculations
• Filtering, sorting, and the use of
“Slicers” in Excel Tables
Completing Activity#5
• Download and open the starter workbook (Act#5-Tables-Starter-file.xlsx)
and the detailed instructions (Activity#5-Tables-INSTRUCTIONS.pdf)
• Rename the starter workbook to reflect your name or initials.
• Using the detailed instructions (PDF), complete the described steps.
(Note that some steps require you to compute specific answers or
calculations as a by-product of completing the described analysis.)
• The following slides provide additional guidance on some of the steps
described in the detailed instructions.
• When you are done, upload the completed workbook to the Activity #5
Assignment folder in Canvas.
Part A – Range Data
An Excel “range” of 2D data — [A5:K62]
Column Headings
A5 is
upper left
of this
range
“end” of range – K62
(bottom right corner of range)
Categories that can be
used for “grouping”
Values that can be
used for “totaling”
or “counting”
Task#1 – Update title area with your name and
the current date.
Initial steps completed:
Added your name and the date
using =TODAY() function
Creating a “named” range:
1. Select the range (A5:K62)
2. In the “name box” (above
column A), enter the unique
name you want to use to
reference this cell range
3. Press the key to save
this named range
Selected range of A5:K62
Task#2 – Identify and select the “range” of customer data on
this worksheet. Then, give that range a name.
Task 3: Using Find-and-Replace to
clean up (scrub) the data.
Launch from
“Home” tab
Sorting a ‘range’ (using the ‘data’ tab)
1. Navigate to the “Data”
tab on the menu bar
2. Select the upper left
corner of the range to
sort (e.g., A5)
3. Click on the “Sort” icon
to open the sort dialog
box
4. Select the columns and
style of sort to apply to
this range of data and
click “OK” to execute
the sort
Task#4 – Using the “Data” tab to sort a range dataset
on one or more columns.
Filtering & Sorting Actions to Complete
• Using the “Data” menu and the “Sort” icon, sort the range (A5:K62)
by LastName and within that by FirstName
• Use the Search/Replace tool (or keyboard shortcut) to find any occurrences of
the first name “Bill” and change them to “William”
• Use the Search tool (or keyboard shortcut) to find and count the number of
occurrences of “0909” in the SSN column. (Note the number found.)
Sorting & filtering data in a ‘range’ (using the ‘data’ tab and ‘filter’ icon)
1. Same general technique
used in sorting but click on
the “filter” icon instead
2. After turning on “filter”
use the down arrows for
each column heading to
sort or filter values in that
column.
Filtering & Sorting Actions (cont.)
• Using the “Data” menu and the “Filter” icon on the 2D range of
A5:K62, do the following:
• In the LastName column filter to see rows only for “Hoffman”
• In the FirstName column filter on “William” (Note the number of rows found.)
• Filter the LastName column on “Schmidt”, “Smith”, and “Smyth” and then sort
the filtered list by “Balance” ordered largest to smallest
• Use the column filter technique to determine how many customer records
show “JX” or “KL” as the Prod-Cat. Note the count of rows with a match.
• Use the column filter technique to display all Balances greater than
“$100,000”. Note the count of rows that exceed $100,000.
• Turn off “Filter” of the column headings by unclicking the “Filter” icon
at the top.
• You have now finished the Part A activities involving a “range” of
structured Excel data.
Part B – Excel “Tables”
Task 5: Creating an Excel “Table” structure on an
existing worksheet
• Select “MYTABLE” in the name box (or select
the 2D range of A5:K62)
• Choose “Insert” on the top menu bar and the
click “Table”
• On the “Create Table” dialog box, make sure
that the range of A5:K62 is referenced (as
absolute) and the “has headers” check box is
selected.
• Click “OK” to create a table of that set of data
columns and rows.
Customize the table by turning
on the “Total Row” option and
selecting a different “style” on
the “Table Design” submenu
Use the “View” menu bar option to “Freeze” the column headings (row 5) to
keep them locked while scrolling down through the table.
Click on A6 before
“Freezing” the pane
Add a computation of
the Total “Balance” at
the end of the table.
(Presumes you have
checked the “Total
Row” checkbox on the
Table Design menu.
Task 6: Filtering and sorting data in an
Excel Table
Create an updated subtotal of
Balances filtered to only include
those of $100,000 or greater.
New filtered balance total and “count”:
Additional computations using table filtering
and totaling:
Filter for “HighBal” of “N” and note the updated Total
Balance and Count.
Filter for Prod-Cat of “KL” and “JX”, then sort the filtered
list by Prod-Cat. Note the updated Total Balance and
Count.
Results for HighBal of “N”
Results for Prod-Cat of “KS” and “JX”
Task 7: Using “Slicers” to filter tables in Excel
• An additional method of filtering table data separate from
the data itself.
• A general feature of Excel that we will see again in Pivot
Tables and Charts/Graphs.
• Accessed through the “Table Design” menu bar item.
Task 8: Converting a “Table” back to a “Range” and finishing up Activity #5
Finishing up:
Remove all slicer filters and make a copy of this worksheet. (You will now
have two identical worksheets.) Rename this copy of the worksheet to
“Act#5-Table”. It will be kept intact and represent your work on Excel
Tables.
We go back to the original worksheet tab (CustomerData) and convert the
table back to a 2D range.
Click on the “CustomerData” worksheet tab and make sure the active
(“clicked”) cell is A5 in the upper left corner of the 2D range.
Select the “Table Design” submenu and click on “Convert to Range”. You
will be prompted to allow Excel to convert the table to a normal range.
Click on “yes”. (You can also right-click inside the table and use
Table/Convert to Range in the pop-up menu.)
Activity #5:
Wrapping up and submitting your work
• Ensure your final workbook has two tabs: (a) One that is titled
“Customer Data” and contains only a standard 2D range of data and
(b) a second tab titled “Act#5-Table” and contains a “Table” version of
the customer dataset with a total line and a table style applied to it.
• Upload your completed Excel workbook to the Activity#5 assignment
folder in Canvas. (Ensure you have also retained a backup copy of the
completed workbook.)
Customer List by Maturity and Balance
Submitted by:
[YOUR NAME]
Run Date:
LastName
Jones
Smith
Hyad
McCullough
Hu
Nyed
Kuchipudi
Schafer
Smyth
Hughes
Gregory
Adams
Cryoge
Walls
Schmidt
Wagner
Weeks
Cunningham
Robles
Salinas
Avila
Vasquez
Gamble
Washington
Jefferson
Hoffman
Williams
Clarke
Rubins
Saul
Walker
Jones
Granger
Hang
Nasmith
Kitchens
Schaeffer
Smith
Highsmith
Gregson
Adams
Clarkson
FirstName
Albert
Trina
John
Elaine
Xioweh
Caleb
Kari
Janet
Robert
Karen
William
Steven
Alexandre
Al
John
Beth
Ursula
Clare
Karen
Raul
Hershel
Ellen
Maricia
George
Emily
Alexandre
Greta
Sam
Karl
Paul
Sarah
Ellen
Mark
Chen
Carl
Clem
Mindy
Randy
Karen
Sandy
Bill
Kelly
SSN
123-77-9876
999-98-1234
678-33-6512
766-56-2389
143-97-1289
245-76-0909
856-96-4785
744-85-9612
766-56-2111
433-91-1271
450-71-0909
852-96-4785
854-89-2536
744-85-9600
854-89-0101
433-91-9898
624-78-5232
854-89-2556
625-78-8521
724-81-9612
433-92-8763
521-77-8952
258-96-7421
259-85-3617
433-91-1088
764-99-9011
624-78-5876
877-89-2556
625-78-8888
724-77-9612
429-92-8763
521-77-8999
258-96-7421
143-97-8765
245-76-0876
856-96-8723
888-85-9612
766-56-8743
987-91-1271
876-71-0909
852-87-4785
854-89-7654
Gender InvProduct
M
KA-4276
F
KL-9211
M
AR-4200
F
AR-3900
NB
JX-9921
M
JX-8200
M
KL-4010
NB
JX-8944
M
AR-3900
F
JX-8200
M
KL-9211
M
JX-8200
M
AR-3900
M
KL-4010
M
KL-9211
F
KL-4010
F
KL-9211
F
KL-9211
F
KL-4010
M
KL-4010
M
JX-8200
F
KA-4276
F
KL-9211
M
KA-4276
F
JX-8200
M
KL-9987
F
KL-9211
M
KL-9211
M
KL-4010
M
KL-4010
F
JX-8200
F
KA-4276
F
KL-9211
F
AR-3900
M
JX-8200
M
KL-4010
F
JX-8200
M
AR-3900
F
JX-8200
F
KL-9211
M
TY-2000
F
TY-2000
Prod-Cat
KA
KL
AR
AR
JX
JX
KL
JX
AR
JX
KL
JX
AR
KL
KL
KL
KL
KL
KL
KL
JX
KA
KL
KA
JX
KL
KL
KL
KL
KL
JX
KA
KL
AR
JX
KL
JX
AR
JX
KL
TY
TY
Balance
$ 25,240.00
$ 13,250.00
$ 130,100.00
$
5,222.00
$ 15,030.00
$ 28,900.00
$ 35,000.00
$ 13,005.00
$
8,500.00
$ 75,250.00
$ 245,120.00
$ 19,000.00
$ 15,250.00
$
1,800.00
$ 320,100.00
$ 410,000.00
$ 19,500.00
$
9,100.00
$ 17,400.00
$
1,200.00
$ 32,500.00
$ 41,050.00
$ 36,000.00
$ 42,000.00
$ 35,800.00
$ 110,500.00
$ 25,000.00
$ 16,000.00
$ 101,000.00
$ 87,000.00
$ 45,000.00
$ 50,000.00
$ 36,000.00
$ 98,000.00
$ 32,100.00
$ 45,000.00
$ 14,500.00
$ 24,800.00
$
5,200.00
$ 142,000.00
$ 78,500.00
$
4,250.00
Wilson
Schmidt
Klemmons
Witston
Cary
Gloeckner
Jones
Allison
Guidry
Waxler
Scheidt
Wilson
Musk
Morant
Bane
Brian
Margie
Seigfried
Madeline
Emily
Tobias
Tommy
Aubrey
Francis
Ben
Ronnie
Kemmons
Elon
Jamie
Desmond
876-99-0872
854-89-7109
433-71-9898
624-51-5232
854-89-2521
672-99-7811
876-71-8723
852-87-8100
842-89-7654
876-87-0910
854-89-8505
841-71-9898
624-51-8411
912-89-2521
820-99-7811
M
F
M
F
F
M
F
F
F
M
F
M
M
M
M
KL-4010
TY-2000
JX-8200
KL-9211
TY-2000
JX-8200
KL-9211
TY-2000
KL-9211
KL-4010
TY-2000
JX-8200
KL-9211
TY-2000
JX-8200
KL
TY
JX
KL
TY
JX
KL
TY
KL
KL
TY
JX
KL
TY
JX
$ 85,200.00
$ 320,100.00
$ 95,400.00
$ 168,000.00
$ 119,000.00
$ 65,000.00
$ 14,200.00
$ 48,000.00
$ 42,500.00
$
8,500.00
$ 32,100.00
$ 245,600.00
$ 568,000.00
$ 231,000.00
$ 165,000.00
HighBal
N
N
Y
N
N
N
N
N
N
N
Y
N
N
N
Y
Y
N
N
N
N
N
N
N
N
N
Y
N
N
Y
N
N
N
N
N
N
N
N
N
N
Y
N
N
Maturity Date Mat Month
4/2/2022
Apr
4/1/2022
Apr
6/12/2022
Jun
9/1/2023
Sep
8/14/2023
Aug
7/14/2022
Jul
8/25/2022
Aug
7/15/2022
Jul
3/8/2023
Mar
4/3/2022
Apr
9/1/2023
Sep
7/14/2022
Jul
4/17/2022
Apr
3/8/2023
Mar
4/6/2022
Apr
7/14/2022
Jul
5/25/2022
May
4/18/2022
Apr
3/8/2023
Mar
6/12/2022
Jun
12/13/2022
Dec
7/14/2022
Jul
1/5/2023
Jan
8/6/2024
Aug
4/18/2022
Apr
6/12/2022
Jun
5/25/2022
May
4/18/2022
Apr
5/1/2023
May
6/12/2023
Jun
1/13/2022
Jan
8/15/2023
Aug
1/5/2023
Jan
5/1/2022
May
7/14/2022
Jul
9/2/2024
Sep
7/15/2022
Jul
3/8/2023
Mar
5/1/2023
May
6/15/2022
Jun
7/14/2022
Jul
9/15/2022
Sep
Mat Yr
2022
2022
2022
2023
2023
2022
2022
2022
2023
2022
2023
2022
2022
2023
2022
2022
2022
2022
2023
2022
2022
2022
2023
2024
2022
2022
2022
2022
2023
2023
2022
2023
2023
2022
2022
2024
2022
2023
2023
2022
2022
2022
N
Y
N
Y
Y
N
N
N
N
N
N
Y
Y
Y
Y
10/1/2022
4/6/2022
7/14/2022
5/25/2022
11/15/2023
7/8/2022
6/15/2022
9/14/2022
9/15/2022
8/15/2022
5/5/2023
7/14/2022
9/8/2024
4/18/2022
8/5/2023
Oct
Apr
Jul
May
Nov
Jul
Jun
Sep
Sep
Aug
May
Jul
Sep
Apr
Aug
2022
2022
2022
2022
2023
2022
2022
2022
2022
2022
2023
2022
2024
2022
2023
Updated: 2/6/2023
MIS-3210
Activity #5 – Excel Tables (Guided Instructions)
(Related support materials: Activity#5-Excel-Tables-Instructions-PPT)
This Activity leads you through simple data manipulation of tabular data.
There are two parts to this activity. Please ensure that you get familiar with
both parts.
• Download the working file (Act#5-Tables-Starter-file.xlsx)
• Add your name to the cell adjacent to Submitted by.
Part A: Working with Range Data
• Study and “know” the data.
○ Note the column headings
○ Note the things that can be “categorized” or “grouped”
• Run date functions Current date/ time, NOW() and TODAY()
○ Shortcut for Current Date Ctrl + ;
○ Shortcut for Current Time Ctrl + Shift + ;
○ This will insert the current date or time into the cell and the
value will remain there.
But what if you want the current date and time updated every time
you open the worksheet. For this you should use either Today() or
Now()
Things to know;
• TODAY() stores just the date, NOW() stores date and time
• Excel stores dates as numbers so you can actually do
mathematical functions with it
1
Updated: 2/6/2023
• Explore the difference between the two functions
 In cell B3, type in =Today ()
 Convert into general format – note the number
 Then replace cell with =Now()
 Convert into general format – note the number
 Use =Now() in cell B3
 Convert into general format – note the number
It has decimals and is different from the number you
observed with Today()!!
The 5 digit decimal place is the time part!
Since Excel stores date/time as numbers you can do Date math
such as =TODAY()-5 or =NOW()-7 without any errors.
TRY THIS: Subtracting hours – To subtract 1 hour from now()
try this:
In the next cell =B3-1/24
if your time in in cell B3 was 1pm it will now by 12pm.
Use format dropdown to select the correct time display.
○ Finally leave the cell with the function =TODAY() with format
MM/DD/YYYY
• Useful Shortcut for Selecting a range
○ To select your data (A5:K62), place your cursor on first cell with
tabular data – cell A5)
○ Use Shortcuts to move to right-end of data Ctrl-Shift-Right,
and bottom-right of data – Ctrl-Shift-Down
• Naming a cell or range
2
Updated: 2/6/2023
○ To easily select your relevant range at any time you want, you
can “Name” the range. You can also use this “Name” in
function.
○ Select the range A5 to K62 and name the range in the top left
corner as “MYTABLE”
3
Updated: 2/6/2023
Part I – Simple Data manipulation
• Summary
○ Using Sort
○ Using Filter
○ Using Ctrl-F – Search and Replace
○ Using Filters – display and number filters option
Detailed instructions for Part I
• Using Tabular data features (Filter, Sort)
– Highlight the row with the headers by placing your cursor on left of
row 5 and clicking.
– Select Data>Filter (You will see dropdowns appear on all
columns)
– Using Sort on Last Name, First Name
o Highlight the whole tabular range
o Data – Sort
o Make sure you check “My data has headers”
o Sort by “Last Name”
o Add level and select “First Name”
o Select OK
4
Updated: 2/6/2023
• Using Search / Replace/ Find All
o Ctrl-F – Search for “Bill”, Replace with William (make
there are no more occurrences)
o Use Find all
 Search for “0909” and select Find-all
 How many occurrences of “0909” were there in total
(Answer: 3)
• Using Filter, Clear Filter
o Filter on “hoffman” using filter to see only hoffman’s
record
o Clear filter on Last Name
o Filter on “william” using filter to see only william’s record
o Filter again on “schmidt, smith, and smyth” (should find
more) and then sort on “balance” (largest to smallest).
o Clear all filter on First Name
o Filter to display
 Product Categories of “JX” and “KL” using column
 Note the result count at bottom left
 How many records were found with “JX”? (15)
 How many records were found with “KL”? (25)
 Both? (40)
o Filter to display all balances >100,000
 Use filter dropdown on Balance and select number
filters to display greater than >100,000
 Note the result. How many rows were displayed?
(Answer: 14)
• Undo filter by toggling the filter icon off
5
Updated: 2/6/2023
Part B: Excel Tables for summary calculations, sorting, and filtering
To make managing and analyzing a group of related data easier, you can
turn a range of cells into an Excel table (previously known as an Excel list).
Note that Excel has a separate feature called Data Tables which is different and can be
found under the What-If analysis. For now, let’s focus on simple Excel tables.
• Summary
○ Using a Named Range
○ Creating an Excel Table
○ Styling an Excel Table
○ Adding a Total Row
○ Using Freeze Headings
○ Using Tables for calculations
○ Using Table Slicers
○ Worksheet Copy
Detailed instructions for Part II
• Select “MYTABLE” (your named range) by selecting from the
dropdown in left cell of worksheet
• Select “Insert” on the top menu bar. On the “Insert” ribbon click on
the “Table” icon in the upper left. This will convert the “MyTable”
range to an Excel “table”.
6
Updated: 2/6/2023
• Note the new “Table Design” option that appears on top right of
ribbon
• Use the “Table Design” tool to apply a different table layout (perhaps
orange)
• Use the “Style Options” to
○ Turn off banded rows
○ Add a “Total Row” (Q: How many rows? 57)
• Freeze headings so you can scroll without losing the headings
o Place your cursor in the first column “A” under the headings
[Cell A6]
o Select View>Freeze Panes>Freeze Panes
(Now you can scroll without losing context of the column!)
• Compute the following:
○ Using the dropdown in the Total Row, Add a Total for “Balance”
(format as $ or expand the column if you see ####)
($4,717,767.00)
○ Using Filter>Number Filter – Apply a filter to only display those
with a balance of $100,000 or more. Observe how the total in
the Total Row has changed. What is that total? ($3,275,520.00)
○ Select Count to display how many? [14]
○ Clear Balance filter
○ Apply the filter for HighBal =“Y” then change the filter to “N”.
What is the balance for “N” and how many records are there?
[$1,442,247, 43]
○ Now Clear the “HighBal” filter
○ Filter for Prod-Cat of “KL” and “JX”
○ Sort by Prod-Cat
■ How many? (ans: 40)
■ Total Balance? (ans: $3,444,655.00) [Clear Filter]
7
Updated: 2/6/2023
○ Click on “Table Design” tool
■ Select “Insert Slicer” for “Mat Month” and “Mat Yr”
■ Select Mat-Yr = 2022
■ How many? (Answer:38)
■ Balance? (Answer: $3,113,595)
■ Select Mat-Month of May
■ How many? (Answer: 4)
■ Balance? (Answer: $310,500)
• Remove filters on Slicers
• Make a Worksheet Copy of this tabular version to save your work
○ Right-click on worksheet Tab and select copy
○ Rename tab to “Act#5-Table” and color it yellow
Convert back to a cell range (from a “Table”)
• On the original Worksheet tab “Customer Data”, remove slicers by
right-clicking on Mat-Month and Mat-Yr slicers and removing them
from the worksheet.
• Convert your table back to a range by right-clicking on it and selecting
Table>“Convert to Range”. (As an alternative, you can select the
Table Design submenu and click on “Convert to Range”. You will be
prompted to allow Excel to convert the table to a normal range. Click
on “yes”. To remove the leftover table styling you can use
Home>Cell styles>Normal.
• Ensure your final workbook has two tabs: (a) One that is titled
“Customer Data” and contains only a standard 2D range of data and
(b) a second tab titled “Act#5-Table” and contains a “Table” version of
the customer dataset with a total line and a table style applied to it.
• At this point, you are done!
Save your updated version of the Excel workbook and upload to
Assignment#5 Folder in Canvas.
8
Updated: 2/6/2023
9