Lesson 7 - EXCELlent formula fun

Advanced Applications - Excel

Before we continue with Advanced Excel, does anyone need me to go through the Basics of Excel?

Have you used Excel before?

Do you understand Cells and Cell ranges?

Do you know how to enter a basic formula or Function?

Do you understand Relative and Absolute cell addresses in formulas?

Can you apply styling to make it look pretty?

If you can answer YES to the above questions, then you are all set for advanced Excel (we will take it slowly)

Excel Charts

Let's start our Advanced Excel lesson with Charts!

This is a simple spreadsheet that compares the library statistics from the door counter for branches of South East TAFE libraries.

Start by making it look a bit prettier

Heading

• I made the font larger
• Added some shading
• Did a merge and centre

Table

• Added borders
• Made the column headings bold
• Centred the headings

Create the Chart

Start by selecting the area of the graph that you want to chart A3:M8

Go to the Insert Ribbon and select the Bar/Column option and choose a 3D Clustered Column chart

Add the Chart Title - Library Statistics for 2019

What happens if you click on the Switch Row/Column button?

Class Exercise 1

Using the same spreadsheet, try creating a line graph

Using the same spreadsheet, try creating a graph that displays 3 of the branches as columns and 2 of the branches as lines (Combo Chart)

---

Advanced Formulas

All of the sample files are available on the Blog Links section

Logical Functions

Nesting Ifs

 Have a look at the following spreadsheet (2019LogicalFunctionIf.xlsx)

This is what the IF function looks like and the components required.

The logical_test is a logical statement that must equate to true or false. You can use comparison operators less than (<), greater than (>), equal to (=), not equal to (<>) within your logical statement. You can refer to values within cells or you can include actual values in your statement.

In the exercise spreadsheet, the first example is a simple If function with a calculation. In this case, our logical_test will be whether the savings are less that $4000. If the amount is less than $4000, the condition will be true and the rate would be 2%. If the amount is not less than $4000, then condition is false (the amount must be $4000 or higher), which will display the rate of 5%.

=IF(A8<4000,2%,5%)


N.B. You can do the same function by placing the interest rates in cells on your spreadsheet and using a cell reference for the interest rates.

We can also NEST an IF Function. Nesting means that for the true or false activity, there is another IF function. The second example uses a nested IF function.

Class Exercise 2

Complete EXAMPLE 1 and EXAMPLE 2 in the 2019LogicalFunctionsIf spreadsheet.

Lookup Functions

vlookup

The lookup function we will use is the VLOOKUP function. The VLOOKUP function is designed to allow you to make a choice from a list of items. The format of this function is:

VLOOKUP(lookup_value, Table_Array, Col_Index_Nbr)


Use the spreadsheet 2019LOOKUPFunctions.xlsx

The formula would be: =VLOOKUP(C4,Pay_Table1,2). Pay_Table1 is a named range of A24:B27

NOTE: your table array must be in numeric/alphabetical order or this will NOT work.

You can copy the formula down Column D to complete the spreadsheet

Have fun nesting one or more of these functions together.

Class exercise 3

Complete the Weekly Pays spreadsheet using the vlookup function

Statistical IF functions

The functions Sum, Average and Count can be used to provide statistical information about a range of data.

These functions also have a counterpart that allows you to incorporate conditions. Use the spreadsheet 2019StatisticalIf.xlsx

Count how many have a branch of Cranbourne: =COUNTIF(B4:B29,"Cranbourne")

This is what the SUMIF function looks like

Class Exercise 4

Using 2019StatisticalIf.xlsx spreadsheet perform the following:

• Sum the Q1 values that are over $5000
• Sum the values in Q1 that are not Frankston.
• Count how many are Dandenong
• Count how many reps earned more than $5000 in Qtr 1
• What is the total sales for Cranbourne

Multiple Conditions

Use the SUMIFS, COUNTIFS, AVERAGEIFS to have multiple conditions that are to be tested.

Count how many Reps in Cranbourne that earned over $5000 in Qtr 1

Class Exercise 5

• Count of Reps in Dandenong who earned a total of more than $20000
• Count of Reps who earned more than $5000 in ALL Quarters
• How many of the above were Dandenong?

Conditional Formatting

Data Bars

In later versions of Excel (2007+), under conditional formatting, you can create an inline chart. I personally think these are cool!! For those visual people, you can see the data as a number as well as  represented as a chart bar.

Use the same spreadsheet - 2019StatisticalIf

Highlight the values in Qtr 1.
Under conditional Formatting and Data Bars. Hover over one of the buttons and see what it does.

I know, right!! AWESOME isn't it. Just a little thing, but it can mean a lot more than just the dollar figures when it comes to comparing - how easy is it now to see where the larger amounts are???

Class Exercise 6

Create Data Bars for the Total column in the 2018StatisticalIF spreadsheet

Color Scales

Another way to be more visual with your data is to use Color Scales. Rather than creating a bar, your cells are coloured based on the values, making it easier to find the lower amount and/or the high amounts.

For the default color scale, Excel calculates the median or middle value.  That cell is coloured yellow. The cell that holds the minimum value is coloured red and the cell that holds the maximum value is coloured green. All other cells are coloured proportionally around these.


If you want to set your own scale and/or rules for formatting, you can.

Class Exercise 7

Create a colour scale for the total column in the 2019StatisticalIf spreadsheet with the midpoint being 25000

Icon Sets

These work in a similar way to the color scales except that icons are used rather than the cells being coloured.

If you just want to see the icons and not the values, you can check the box Show Icons Only.

With a 3 icon set, Excel, by default will take the maximum and minimum values and create 3 percentiles for the amounts in between. Again, you can change the rules to suit yourself.

Highlight Cells

Use the Highlight cells to apply conditional colour coding on your spreadsheet.

Class exercise 8

Try out a few other combinations!