Week 3: Organizing and
Visualizing Data
Exercise 1: Creating and modifying a table
2. First, use your cursor to select the A4:G14 cell range:
3. Next, click Insert → Table:
4. In the Create Table dialog box, you will see that the range you previously selected is listed inside the “Where is the data
for your table?” text box:
5. Ensure that the “My table has headers” checkbox is checked and click OK:
6. You will see that the previously selected range has now been converted into a table:
7. Now, you need to apply a new table style. With any of the cells inside the table selected, open the Table Design
contextual tab. Within the Table Styles group, click the More arrow:
8. A variety of different quick styles to choose from is now displayed. For this example, click Table Style Medium 8:
9. The new style is now applied to the current table:
10. Save the current workbook as Activity 3-1 Complete and then close Microsoft 365 Excel to complete this exercise.
Exercise 2: Modifying tables
2. To begin your analysis, you want to add a total row to your table. Click anywhere within the table then click Table
Design → Total Row:
3. A Total Row is added to your table, with a total amount in the Weekly Goal column:
4. First, you want to add a count for the number of sales representatives. Select cell C16, then click the total row drop-down
arrow and select Count:
5. You see that the count of sales representatives is 11, but you know that there are only 10. You realize there must be
duplicate data:
6. To remove the duplicate, click Table Design → Remove Duplicates:
7. The Remove Duplicates dialog box opens. Leave all of the columns selected and click OK:
8. An information window appears, indicating that one duplicate has been removed and there 10 unique values remaining.
Click OK:
9. With the duplicate now removed you can now add a total to the Weekly Sales column. Select Cell E15, click the
dropdown arrow, and select Sum:
10. You can now add a column to your table by selecting cell G4 and typing “% of Total Sales”, then pressing Enter:
(Adjust your column width as required to view the text in the header.)
11. In cell G5, type the formula “=E5/$E$15” and press Enter, then set the format of the column to percentage, and center
the content:
12. You can now add sum of the % of Total Sales column by selecting cell G15, clicking the drop-down arrow, then
selecting Sum:
13. You can now filter the table to show only the sales representatives who reached their weekly goal by clicking the header
row drop-down arrow of the Weekly Sales column, then selecting Number Filters, then Greater Than:
14. In the Custom AutoFilter dialog box, type “15000” next to the “is greater than” selection, then click OK:
15. You will now see the three sales representatives who reached their weekly goal, but the % of Total Sales column is
showing percentages only for the total sales of the three. You want to see the percentages based on the total. To do this,
select cell E15, then select the Subtotal formula in the formula bar and type “=SUM([[Weekly Sales]])” to replace it:
16. Press Enter to apply the formula. You will see that the value in the Total Row of the Weekly Sales column is now the
sum of all of the sales, not just the ones that are visible. You will also see that the values in the % of Total Sales column are
now displaying correctly:
17. Save the current workbook as Activity 3-2 Complete and then close Microsoft 365 Excel to complete this exercise.
Subtotal function arguments
SUBTOTAL Function Arguments
In the SUBTOTAL Function, =SUBTOTAL(function_num, ref1, [ref2], […]) the
function_num argument allows you to choose the type of calculation that will be performed.
The following table lists the number that you can use at this position in the formula, to
indicate what type of results you want.
You will notice that there are two numbers listed for each function. If you choose the value
from the left hand column, then the SUBTOTAL function will ignore any values that are
hidden.
If you choose the value from the middle column, then SUBTOTAL will work similar to
SUM in that it will include all values even if they are in hidden columns. These argument
numbers are only really relevant in situations where SUBTOTAL is working horizontally,
and you have hidden columns in your data. If you have hidden rows using the Filter arrows,
the SUBTOTAL calculation will ignore hidden values even if you choose a number from the
middle column.
Function Argument Including Function Argument Ignoring
Function
Hidden Values Hidden Values
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
List of database functions This table lists the available database functions, giving you the
flexibility to perform many common operations on a specified subset of your data.
Calculates the average of values in a field of a list or database, that satisfy
DAVERAGE
specified conditions
Returns the number of cells containing numbers in a field of a list or database
DCOUNT
that satisfy specified conditions
Returns the number of non-blank cells in a field of a list or database, that
DCOUNTA
satisfy specified conditions
Returns a single value from a field of a list or database, that satisfies specified
DGET
conditions
Returns the maximum value from a field of a list or database, that satisfy
DMAX
specified conditions
Returns the minimum value from a field of a list or database, that satisfy
DMIN
specified conditions
Calculates the product of values in a field of a list or database, that satisfy
DPRODUCT
specified conditions
Calculates the standard deviation (based on a sample of a population) of
DSTDEV
values in a field of a list or database, that satisfy specified conditions
Calculates the standard deviation (based on an entire population) of values in
DSTDEVP
a field of a list or database, that satisfy specified conditions
Calculates the sum of values in a field of a list or database, that satisfy
DSUM
specified conditions
Calculates the variance (based on a sample of a population) of values in a
DVAR
field of a list or database, that satisfy specified conditions
Calculates the variance (based on an entire population) of values in a field of a
DVARP
list or database, that satisfy specified conditions
Exercise 3: Using table references
2. First select the table, then click Table Design, and type “Daily_Orders” in the Table
Name field:
3. Press Enter to update the table name, then select cell D4 and type “=DCOUNT(“ in the
Formula Bar:
4. Now begin typing the table name, “Daily_Orders” to display table name suggestions:
5. With the Daily_Orders table highlighted, press Tab to accept the suggestion:
6. Next, type an open square bracket ([) to show suggestions for table elements. Use the
arrow key to select “#All” from the list, then press Tab to accept:
7. You can now type a closed square bracket (]) and a comma (,) to complete the database
argument:
8. To define the field argument, click the table header “Order Number.” Excel enters the
structured reference automatically:
9. To define the criteria argument, and complete the formula, first type a comma (,) to move
to the next argument, then select the range A3:B4:
10. Press the F4 key to make this an absolute reference, then press Enter:
11. You will see that the formula returns the value 99, as there are 99 rows in the table, and
no criteria have been entered:
12. Now type “Online” in cell A4. You will see that 50 of the order types were Online:
13. Type the SKU number “0406” in cell B4. While you would expect the count value to
change, it has not:
14. This is because the heading in cell B3, “SKU No.”, does not match the column name in
your table. Change the text in cell B3 to “SKU” to match the column name. Now you will see
that the count value has changed:
15. You can now select cell E4 and follow the same steps, but this time use the DSUM
function and use the table header “Order Price” for the field argument:
16. You will now see the sum of orders where SKU 0406 had an order type of Online:
17. Save the current workbook as Activity 3-3 Complete and then close Microsoft 365 Excel
to complete this exercise.
Exercise 4: Formatting worksheet tabs
2. Ensure that Sheet1 is displayed. Click Home → Format → Rename Sheet:
3. Type “Product Usage” into the active text field and press Enter:
4. Next, click Home → Format → Tab Color → Blue:
5. Now you need to format the second sheet in this workbook. Right-click Sheet2 and click
Rename:
6. Type “Product Inventory” into the active text field and press Enter:
7. Next, right-click on the Product Inventory tab and click Tab Color → Red:
8. Both tabs are now correctly labeled and formatted with contrasting colors:
9. Save your workbook as Activity 5-1 Complete. Close Microsoft 365 Excel to complete this
activity.
Exercise 5: Managing worksheets
2. First you need to insert a new worksheet into the current workbook, so click Home →
Insert → Insert Sheet:
3. The new sheet will be inserted to the left of all the existing ones:
4. Move the new worksheet to the right of all the existing ones by clicking and dragging it in
that direction until the small black indicator arrow is in position:
5. When you release your mouse button, the selected worksheet will be dropped into position:
6. Hide the Product Inventory worksheet by right-clicking on it and clicking Hide:
7. It turns out that you need to see this worksheet after all, so click Home → Format →
Hide & Unhide → Unhide Sheet:
8. The Unhide dialog box will now be displayed. Ensure that the Product Inventory
worksheet is selected and then click OK:
9. The selected worksheet will be visible once again:
10. Save your workbook as Activity 5-2 Complete. Close Microsoft 365 Excel to complete
this activity.
Exercise 6: Managing the view of
worksheets and workbooks
2. Make sure Managing the view of worksheets and workbooks A is the active workbook,
and Product Usage is the active worksheet. Click cell B2 then click View → Freeze Panes
→ Freeze Panes:
3. Now use the Scroll bar down and right buttons to scroll your data. Note that the column
and row headers are frozen:
4. Make the Managing the view of worksheets and workbooks B workbook active by
clicking View → Switch Windows → Managing the view of worksheets and workbooks
B:
5. Click cell D27 then click View → Split:
6. Click and drag the Scroll bars to scroll the data in each quadrant:
7. Now click and drag the center of the dividers one column to the left:
8. You can now click View → Split, to remove the dividers:
9. Return to the Managing the view of worksheets and workbooks A workbook by clicking
View → Switch Windows → Managing the view of worksheets and workbooks A:
10. Now add a new window by clicking View → New Window:
11. In the new window labelled Managing the view of worksheets and workbooks A – 2,
click on cell F2 and type “Total” and then press Enter:
12. Now click View → Switch Windows Managing the view of worksheets and
workbooks A – 1:
13. Note that the change you made in the one window is also reflected in the other window:
14. Save your workbooks as Completed_Managing the view of worksheets and
workbooks A and Completed_Managing the view of worksheets and workbooks B. Close
Microsoft 365 Excel to complete this activity.
Exercise 7: Creating charts
2. First, you need to select the dataset with which you would like to work. Use your cursor to
select cells C4:C14 and E4:E14. Remember to hold the Ctrl key down when selecting
non-adjacent cell ranges:
3. Next, click Insert → Insert Column or Bar Chart → Clustered Column:
4. The new chart now appears on the current worksheet, overlapping some of the data:
5. Click and drag this chart to a location on the worksheet where it is not
overlapping anything:
6. Examine the new chart. You will quickly see that Thompson made the most sales in week
1, with Harrison as the next runner up. McCain clearly has some work to do:
7. Save the current workbook as Activity 4-1 Complete and then close Microsoft 365 Excel to
complete this exercise.
Exercise 8: Modifying and formatting charts
2. Click to select the chart on the current worksheet. You will see the Chart Design
and Format contextual tabs appear on the ribbon:
3. First, you should add a title to this chart. Click Chart Design → Add Chart
Element → Chart Title → Above Chart:
4. The chart title is now displayed above the data series on the chart:
5. Next, you should add gridlines to make this chart a little easier to read. Click to select
the chart and then click the Chart Elements button that appears near the top right-
hand corner:
6. From the menu that appears, check the Gridlines check box:
7. Major gridlines are now displayed on the chart:
8. Finally, you might as well remove the legend as it is fairly self-evident what this data
represents. Click Chart Design → Legend → None:
9. Now you need to adjust the color of this chart. Click the Chart Design contextual tab.
Within the Chart Styles gallery, click any of the chart styles presented:
10. The new style is now applied to the selected chart:
11. Finally, with the chart selected, click and drag the handle of the highlighted
data selection, at the bottom right of cell E13, and drag it down one row:
12. A column representing the weekly sales of George Jackson will be added to the chart:
13. Save your current workbook as Activity 4-2 Complete and close Microsoft 365 Excel to
complete this exercise.
Exercise 9: Create a trendline
2. Click to select the large chart that appears on Sheet1 of the current workbook:
3. Click Chart Design → Add Chart Element → Trendline → More
Trendline Options:
4. The Format Trendline task pane now appears on the right side of the Excel window. Click
the Moving Average radio button:
5. Ensure that the Period setting is set to “2” and that the Trendline Name radio button is set
to Automatic:
6. Close the Format Trendline task pane by clicking the Close button (x) in its upper right-
hand corner:
7. Examine the graph and you will see that the trendline that has been added better illustrates
the fluctuation in this data over time:
8. Save the current workbook as Activity 4-3 Complete and then close Microsoft 365 Excel to
complete this exercise.
Summary of DAX categories of functions
Introduction to DAX
When you create a Pivot Table report using PowerPivot, you are usually joining several tables together to
analyze the data in all the tables and generate results from all the information. When you link tables
together in Pivots, you create what is called a Data Model and when you are working in PowerPivot you
are working directly in this Data Model. Creating formulas in the Data Model, requires you to use a
specialized language and syntax. The language you use to create formulas in a Data Model Is DAX. A
significant difference between a DAX Function and an Excel function is that DAX Functions will always
refer to a complete row, column, or table. On the other hand, Excel Functions refer to a single cell or
range of cells. This allows for DAX Functions to return an entire table of results. In Pivots, you can use
advanced formulas called Measures. They can only be created after a PivotTable or PivotChart has been
added to a workbook. They will provide you with results dynamically and are dependent upon any filters
that have been applied to the PivotTable or PivotChart. For example, if you wanted to find the number of
products that a single warehouse has that are worth over $500, you could use a measure to find that
information. Measures are often created using DAX. Variables can also be used in a formula created with
DAX which increases the flexibility of the formula. By declaring a Variable first, the Variable name can
be inserted in more complex expressions to make them more readable. When a Variable is being defined,
the definition begins with an equals sign followed by “var”. The Variable name is then inserted followed
by an expression. The syntax is as follows: VAR <variable name> = <expression>. The results of the
expression are stored as a Named Variable. Once the Named Variable is declared, it can be used in many
other expressions. While there are lots of DAX Functions that you can choose from, each function will be
classified under one of the following categories:
Data and Time functions: Functions of this type are used to manipulate date and time values. As
such, they are like the data and time functions that can be used in Excel.
Filter functions: These functions are used to manipulate data and filter it dynamically.
Information functions: This type of function is used to scan the values inside a cell range and
match them against an expected data type.
Logical functions: Typically, these functions are used to validate expressions and values, and
then work with other data that is based upon the evaluation.
Math and Trigonometric functions: Functions of this type are used to perform mathematical
calculations.
Statistical functions: These functions are used to generate statistical data such as minimum and
maximum values, as well as averages.
Time Intelligence functions: This type of function is used to manipulate data using time periods.
It can be used to compare data of one time-period against another.
Aggregate Functions are also more powerful in DAX. The standard Aggregate Functions in Excel can
be used in Excel spreadsheets, Pivot Tables, and PowerPivot Tables. DAX Aggregate Functions
address many of the shortcomings of standard functions by providing a means to aggregate data across
columns and tables instead of just cells and groups of cells. Below you will see the types of Aggregate
Functions that are available and what they do:
AVERAGE This function returns an average of all the numerical data in a column
AVERAGE This function returns an average of all the numerical data in a column, but it can
A also work with non-numerical data as well.
This function returns a count of the number of cells in a column that contain
COUNT
numerical data.
This function returns a count of the number of cells in a column that contain any
COUNTA
data (numerical or otherwise).
MIN This function returns the smallest number value that was found in a column.
MINX Returns the smallest value from a set of expressions evaluated over a table.
MAX This function returns the largest number value that was found in a column.
MAXX Returns the largest value from a set of expressions evaluated over a table.
SUM This function will add and display the total of all numbers in a column.
In addition to these functions, there are additional functions that are available in the DAX language only.
These functions include the following. AVERAGEX Averages a set of expressions evaluated over a table.
COUNTAX Counts a set of expressions evaluated over a table. COUNTX Counts the total number of rows
in a table. COUNTROWS Counts the number of rows returned from a nested table function, such as filter
function. SUMX Returns the sum of a set of expressions evaluated over a table.
Exercise 10: Creating PivotTables
2. Use your cursor to select cells A4:E40:
3. Next, click Insert → PivotTable:
4. The Create PivotTable dialog box is now displayed. The data range that you previously
selected is shown within the Table/Range text box:
5. You want this new PivotTable to be inserted into the current worksheet, so click the
Existing Worksheet radio button:
6. Inside the Location text box, click the range picker button:
7. Use your cursor to select cell H4:
8. Press Enter to apply the new location. Back at the Create PivotTable dialog box, click
OK to apply the new settings:
9. The PivotTable is now added to the current worksheet in the location that you previously
set:
10. In the Pivot Table Fields task pane, click the Last Name, Daily Sales and
Bonus field checkboxes:
11. You will see that the daily sales and bonuses have been summarized by Sales
Representative:
12. To improve the number formatting, click the Sum of Daily Sales drop-down arrow
in the Values area and select Value Field Settings:
13. Now click Number Format:
14. In the Format Cells dialog box, click Currency, then click OK:
15. Click OK in the Value Field Settings dialog box:
16. Repeat the steps for the Bonus column. Once complete, the daily sales and bonus
values should be formatted correctly:
17. Save the current workbook as Activity 5-1 Complete and then close Microsoft 365 Excel
to complete this exercise.
Exercise 11: Filtering data using slicers
2. Click inside the PivotTable to display the PivotTable Tools contextual tabs. Next, click
PivotTable Analyze → Insert Slicer:
3. The Insert Slicer dialog box is now displayed:
4. Check the Last Name checkbox and then click OK:
5. A slicer for the Last Name field now appears on your worksheet:
6. For this exercise you want to filter out everyone except for Jerry Harrison and Leah
Thompson. While holding down the Ctrl key, click the Bressan, Prestwick,
and Williamson buttons:
7. The entries that you clicked on in the slicer are now filtered out of the PivotTable:
8. Save the current workbook as Activity 5-2 Complete and then close Microsoft 365 Excel to
complete this exercise.
