Excel Tutorial

Home || Basics || Advanced || VBA || Automation || Dashboard || Example


Excel Advanced

Dynamic Named Range
Lookup & Reference
Filter & Advanced Filter
Conditional Formatting
Data Validation
Statistical & Financial Functions
Formula Errors
Array Formulas
Charts
Pivot Tables
What-If Analysis
Solver
Analysis ToolPak

Dynamic Named Range

A dynamic named range expands automatically when you add a value to the range.
1. For example, select the range A1:A4 and name it Prices.
2. Calculate the sum.

3. When you add a value to the range, Excel does not update the sum.

To expand the named range automatically when you add a value to the range, execute the following the following steps.
4. On the Formulas tab, click Name Manager.

5. Click Edit.

6. Click in the "Refers to" box and enter the formula =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Explanation: The Offset function takes 5 arguments. Reference: $A$1, rows to offset: 0, columns to offset: 0, height: COUNTA($A:$A), width: 1. COUNTA($A:$A) counts the number of values in column A that are not empty. When you add a value to the range, COUNTA($A:$A) increases. As a result, the named range expands.
7. Click OK and Close.
8. Now, when you add a value to the range, Excel updates the sum automatically.


 


Lookup & Reference Functions
Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.

VLookup
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.
1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.
2. Drag the VLOOKUP function in cell B2 down to cell B11.

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc.

HLookup
In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

Match
The MATCH function returns the position of a value in a given range.

Note: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found.

Index
The INDEX function returns a specific value in a two-dimensional or one-dimensional range.

Note: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

Note: 97 found at position 3 in the range E4:E7.

Choose
The CHOOSE function returns a value from a list of values, based on a position number.

Note: Boat found at position 3.

 


Filter
Filter your Excel data if you only want to display records that meet certain criteria.
1. Click any single cell inside a data set.
2. On the Data tab, click Filter.

Arrows in the column headers appear.

3. Click the arrow next to Country.
4. Click on Select All to clear all the check boxes, and click the check box next to USA.

5. Click OK.
Result. Excel only displays the sales in the USA.

6. Click the arrow next to Quarter.
7. Click on Select All to clear all the check boxes, and click the check box next to Qtr 4.

8. Click OK.
Result. Excel only displays the sales in the USA in Qtr 4.

9. To remove the filter, on the Data tab, click Clear. To remove the filter and the arrows, click Filter.

 

Advanced Filter
When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be sure there's at least one blank row between your Criteria range and data set.

And Criteria
To display the sales in the USA and in Qtr 4, execute the following steps.
1. Enter the criteria shown below on the worksheet.

2. Click any single cell inside the data set.
3. On the Data tab, in the Sort & Filter group, click Advanced.

4. Click in the Criteria range box and select the range A1:D2 (blue).
5. Click OK.

Notice the options to copy your filtered data set to another location and display unique records only (if your data set contains duplicates).
Result.

No rocket science so far. We can achieve the same result with the normal filter. We need the Advanced Filter for Or criteria.

Or Criteria
To display the sales in the USA in Qtr 4 or in the UK in Qtr 1, execute the following steps.
6. Enter the criteria shown below on the worksheet.
7. On the Data tab, click Advanced, and adjust the Criteria range to range A1:D3 (blue).
8. Click OK.

Result.

Formula as Criteria
To display the sales in the USA in Qtr 4 greater than $10.000 or in the UK in Qtr 1, execute the following steps.
9. Enter the criteria (+formula) shown below on the worksheet.
10. On the Data tab, click Advanced, and adjust the Criteria range to range A1:E3 (blue).
11. Click OK.

Result.

Note: always place a formula in a new column. Do not use a column label or use a column label that is not in your data set. Create a relative reference to the first cell in the column (B6). The formula must evaluate to TRUE or FALSE.

 


Conditional Formatting
Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

Highlight Cells Rules
To highlight cells that are greater than a value, execute the following steps.
1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...

3. Enter the value 80 and select a formatting style.

4. Click OK.
Result. Excel highlights the cells that are greater than 80.

5. Change the value of cell A1 to 81.
Result. Excel changes the format of cell A1 automatically.

Note: you can also highlight cells that are less than a value, between a low and high value, etc.

Clear Rules
To clear a conditional formatting rule, execute the following steps.
1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.

Top/Bottom Rules
To highlight cells that are above the average of the cells, execute the following steps.
1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Top/Bottom Rules, Above Average...

3. Select a formatting style.

4. Click OK.
Result. Excel calculates the average (42.5) and formats the cells that are above this average.

Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!

 


Data Validation
Use data validation in Excel to make sure that users enter certain values into a cell.

Data Validation Example
In this example, we restrict users to enter a whole number between 0 and 10.

Create Data Validation Rule
To create the data validation rule, execute the following steps.
1. Select cell C2.
2. On the Data tab, click Data Validation.

On the Settings tab:
3. In the Allow list, click Whole number.
4. In the Data list, click between.
5. Enter the Minimum and Maximum values.

Input Message
Input messages appear when the user selects the cell and tell the user what to enter.
On the Input Message tab:
1. Check 'Show input message when cell is selected'.
2. Enter a title.
3. Enter an input message.

Error Alert
If users ignore the input message and enter a number that is not valid, you can show them an error alert.
On the Error Alert tab:
1. Check 'Show error alert after invalid data is entered'.
2. Enter a title.
3. Enter an error message.

4. Click OK.

Data Validation Result
1. Select cell C2.

2. Try to enter a number higher than 10.
Result:

Note: to remove data validation from a cell, select the cell, on the Data tab, click Data Validation, and then click Clear All. You can use Excel's Go To Special feature to quickly select all cells with data validation.

Drop-down List
Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values.
1. On the second sheet, type the items you want to appear in the drop-down list.

2. On the first sheet, select cell B1.

3. On the Data tab, click Data Validation.

The 'Data Validation' dialog box appears.
4. In the Allow box, click List.
5. Click in the Source box and select the range A1:A3 on Sheet2.

6. Click OK.
Result:

Tip: If you don't want users to access the items on Sheet2, you can hide Sheet2. To achieve this, right click on the sheet tab of Sheet2 and click on Hide.
7. You can also type the items directly into the text box, without using Sheet2. This gives the exact same result.

Did you find this information helpful? Show your appreciation, vote for us.

Prevent Duplicate Entries
This example teaches you how to use data validation to prevent users from entering duplicate values.
1. Select the range A2:A20.

2. On the Data tab, click Data Validation.

3. In the Allow list, click Custom.
4. In the Formula box, enter the formula shown below and click OK.

Explanation: The COUNTIF function takes two arguments. =COUNTIF($A$2:$A$20,A2) counts the number of values in the range A2:A20 that are equal to the value in cell A2. This value may only occur once (=1) since we don't want duplicate entries. Because we selected the range A2:A20 before we clicked on Data Validation,Excel automatically copies the formula to the other cells. Notice how we created an absolute reference($A$2:$A$20) to fix this reference.
5. To check this, select cell A3 and click Data Validation.

As you can see, this function counts the number of values in the range A2:A20 that are equal to the value in cell A3. Again, this value may only occur once (=1) since we don't want duplicate entries.
6. Enter a duplicate invoice number.
Result. Excel shows an error alert. You've already entered that invoice number.

Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.

 


Statistical & Financial Functions
Below you can find an overview of some very useful statistical functions in Excel.

Average
To calculate the average of a range of cells, use the AVERAGE function.

Averageif
To average cells based on one criteria, use the AVERAGEIF function. For example, to calculate the average excluding zeros.

Note: <> means not equal to. The AVERAGEIF function is similar to the SUMIF function.

Median
To find the median (or middle number), use the MEDIAN function.

Check:

Mode
To find the most frequently occurring number, use the MODE function.

Standard Deviation
To calculate the standard deviation, use the STEDV function.

Min
To find the minimum value, use the MIN function.

Max
To find the maximum value, use the MAX function.

Large
To find the third largest number, use the following LARGE function.

Check:

Small
To find the second smallest number, use the following SMALL function.

Check:

 

Financial Functions
To illustrate Excel's most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that's what you hope to achieve when you pay off a loan).
We make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods). If we make annual payments on the same loan, we use 6% for Rate and 20 for Nper.

Pmt
Select cell A2 and insert the PMT function.

Note: The last two arguments are optional. For loans the Fv can be omitted (the future value of a loan equals 0, however, it's included here for clarification). If Type is omitted, it is assumed that payments are due at the end of the period.
Result. The monthly payment equals $1,074.65.

Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)? We pay off a loan of $150,000 (positive, we received that amount) and we make monthly payments of $1,074.65 (negative, we pay).

Rate
If Rate is the only unknown variable, we can use the RATE function to calculate the interest rate.

Nper
Or the NPER function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, it takes 240 months to pay off this loan.

We already knew this, but we can change the monthly payment now to see how this affects the total number of periods.

Conclusion: if we make monthly payments of $2,074.65, it takes less than 90 months to pay off this loan.

Pv
Or the PV (Present Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, how much can we borrow? You already know the answer.


Fv
And we finish this chapter with the FV (Future Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, do we pay off this loan? Yes.

But, if we make monthly payments of only $1,000.00, we still have debt after 20 years.

 


Formula Errors
This chapter teaches you how to deal with some common formula errors in Excel.

##### error
When your cell contains this error code, the column isn't wide enough to display the value.

1. Click on the right border of the column A header and increase the column width.

Tip: double click the right border of the column A header to automatically fit the widest cell in column A.

#NAME? error
The #NAME? error occurs when Excel does not recognize text in a formula.

1. Simply correct SU to SUM.

#VALUE! error
Excel displays the #VALUE! error when a formula has the wrong type of argument.

1a. Change the value of cell A3 to a number.
1b. Use a function to ignore cells that contain text.

#DIV/0! error
Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

1a. Change the value of cell A2 to a value that is not equal to 0.
1b. Prevent the error from being displayed by using the logical function IF.

Explanation: if cell A2 equals 0, an empty string is displayed. If not, the result of the formula A1/A2 is displayed.

#REF! error
Excel displays the #REF! error when a formula refers to a cell that is not valid.
1. Cell C1 references cell A1 and cell B1.

2. Delete column B. To achieve this, right click the column B header and click Delete.

3. Select cell B1. The reference to cell B1 is not valid anymore.

4. To fix this error, you can either delete +#REF! in the formula of cell B1 or you can undo your action by clicking Undo in the Quick Access Toolbar (or press CTRL + z).

 


Array Formulas
This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.

Without Array Formula
Without using an array formula, we would execute the following steps to find the greatest progress.
1. First, we would calculate the progress of each student.

2. Next, we would use the MAX function to find the greatest progress.

With Array Formula
We don't need to store the range in column D. Excel can store this range in its memory. A range stored in Excel's memory is called an array constant.
1. We already know that we can find the progress of the first student by using the formula below.

2. To find the greatest progress (don't be overwhelmed), we add the MAX function, replace C2 with C2:C6 and B2 with B2:B6.

3. Finish by pressing CTRL + SHIFT + ENTER.

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.
Explanation: The range (array constant) is stored in Excel's memory, not in an range. The array constant looks as follows:
{19;33;63;48;13}
This array constant is used as an argument for the MAX function, giving a result of 63.

F9 Key
When working with array formulas, you can have a look at these array constants yourself.
1. Select C2:C6-B2:B6 in the formula.

2. Press F9.

That looks good. Elements in a vertical array constant are separated by semicolons. Elements in a horizontal array constant are separated by commas.



Charts
A simple chart in Excel can say more than a sheet full of numbers. As you'll see, creating charts is very easy.

Create a Chart
To create a line chart, execute the following steps.
1. Select the range A1:D7.
2. On the Insert tab, in the Charts group, choose Line, and select Line with Markers.

Result:

Change Chart Type
You can easily change to a different type of chart at any time.
1. Select the chart.
2. On the Insert tab, in the Charts group, choose Column, and select Clustered Column.

Result:

Switch Row/Column
If you want the animals, displayed on the vertical axis, to be displayed on the horizontal axis instead, execute the following steps.
1. Select the chart. The Chart Tools contextual tab activates.
2. On the Design tab, click Switch Row/Column.

Result:

Chart Title
To add a chart title, execute the following steps.
1. Select the chart. The Chart Tools contextual tab activates.
2. On the Layout tab, click Chart Title, Above Chart.

3. Enter a title. For example, Population.
Result:

Legend Position
By default, the legend appears to the right of the chart. To move the legend to the bottom of the chart, execute the following steps.
1. Select the chart. The Chart Tools contextual tab activates.
2. On the Layout tab, click Legend, Show Legend at Bottom.

Result:

Data Labels
You can use data labels to focus your readers' attention on a single data series or data point.
1. Select the chart. The Chart Tools contextual tab activates.
2. Click an orange bar to select the Jun data series. Click again on an orange bar to select a single data point.
3. On the Layout tab, click Data Labels, Outside End.

Result:

 


Pivot Tables
Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.
Our data set consists of 214 rows and 6 fields. Order ID, Product, Category, Amount, Date and Country.

Insert a Pivot Table
To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.
3. Click OK.

Drag fields
The PivotTable field list appears. To get the total amount exported of each product, drag the following fields to the different areas.
1. Product Field to the Row Labels area.
2. Amount Field to the Values area.
3. Country Field to the Report Filter area.

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot tables can be!

Sort
To get Banana at the top of the list, sort the pivot table.
1. Click any cell inside the Total column.
2. The PivotTable Tools contextual tab activates. On the Options tab, click the Sort Largest to Smallest button (ZA).

Result.

Filter
Because we added the Country field to the Report Filter area, we can filter this pivot table by Country. For example, which products do we export the most to France?
1. Click the filter drop-down and select France.
Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Product) to only show the totals of specific products.

Change Summary Calculation
By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.
1. Click any cell inside the Total column.
2. Right click and click on Value Field Settings...

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK.
Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table
If you drag a field to the Row Labels area and Column Labels area, you can create a two-dimensional pivot table. For example, to get the total amount exported to each country, of each product, drag the following fields to the different areas.
1. Country Field to the Row Labels area.
2. Product Field to the Column Labels area.
3. Amount Field to the Values area.
4. Category Field to the Report Filter area.

Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.

 


Tables
Tables allow you to analyze your data in Excel quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table.

Insert a Table
To insert a table, execute the following steps.
1. Click any single cell inside the data set.

2. On the Insert tab, click Table.

3. Excel automatically selects the data for you. Check 'My table has headers' and click on OK.

Result. Excel creates a nicely formatted table for you. This may still seem like a normal data range to you but many powerful features are now just a click of a button away.

Note: the Table Tools contextual tab (with the underlying Design tab selected) is the starting point for working with tables. If at any time you lose this tab, simply click any cell within the table and it will activate again. Choose a table style you like. Hover over a table style and Excel gives you a life preview.

Sort a Table
To sort by Last Name first and Sales second, first sort by Sales, next sort by Last Name (the exact opposite).
1. Click the arrow next to Sales and click Sort Smallest to Largest.
2. Click the arrow next to Last Name and click Sort A to Z.
Result.

Filter a Table
To filter a table, execute the following steps.
1. Click the arrow next to Country and only check USA.
Result.

Total Row
To display a total row at the end of the table, execute the following steps.
1. On the Design tab, in the Table Style Options group, check Total Row.

2. Click any cell in the last row to calculate the Total (Average, Count, Max, Min, Sum etc.) of a column. For example, calculate the sum of the Sales column.

Note: in the formula bar see how Excel uses the SUBTOTAL function to calculate the sum. 109 is the argument for Sum if you use the SUBTOTAL function. Excel uses this function (and not the standard SUM function) to correctly calculate table totals of filtered tables.

 


What-If Analysis
What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily.
Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20.

If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.
Create Different Scenarios
But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You can use the Scenario Manager to create these scenarios.
Note: You can simply type in a different percentage into cell C4 to see the corresponding result of a scenario in cell D10. However, what-if analysis enables you to easily compare the results of different scenarios. Read on.
1. On the Data tab, click What-If Analysis and select Scenario Manager from the list.

The Scenario Manager dialog box appears.
2. Add a scenario by clicking on Add.

3. Type a name (60% highest), select cell C4 (% sold for the highest price) for the Changing cells and click on OK.

4. Enter the corresponding value 0.6 and click on OK again.

5. Next, add 4 other scenarios (70%, 80%, 90% and 100%).
Finally, your Scenario Manager should be consistent with the picture below:

Note: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet.

Scenario Summary
To easily compare the results of these scenarios, execute the following steps.
1. Click the Summary button in the Scenario Manager.
2. Next, select cell D10 (total profit) for the result cell and click on OK.

Result:

Conclusion: if you sell 70% for the highest price, you obtain a total profit of $4100, if you sell 80% for the highest price, you obtain a total profit of $4400, etc. That's how easy what-if analysis in Excel can be.

Goal Seek
What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly $4700? You can use Excel's Goal Seek feature to find the answer.
1. On the Data tab, click What-If Analysis, Goal Seek.

The Goal Seek dialog box appears.
2. Select cell D10.
3. Click in the 'To value' box and type 4700.
4. Click in the 'By changing cell' box and select cell C4.
5. Click OK.

Result. You need to sell 90% of the books for the highest price to obtain a total profit of exactly $4700.

 


Solver
Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

Load the Solver Add-in
To load the solver add-in, execute the following steps.
1. On the green File tab, click Options.
2. Under Add-ins, select Solver Add-in and click on the Go button.

3. Check Solver Add-in and click OK.

4. You can find the Solver on the Data tab.

Formulate the Model
The model we are going to solve looks as follows in Excel.

1. To formulate this linear programming model, answer the following three questions.
a. What are the decisions to be made? For this problem, we need Excel to find out how much to order of each product (bicycles, mopeds and child seats).
b. What are the constraints on these decisions? The constrains here are that the amount of capital and storage used by the products cannot exceed the limited amount of capital and storage (resources) available. For example, each bicycle uses 300 units of capital and 0.5 unit of storage.
c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.
2. To make the model easier to understand, name the following ranges.
Range Name Cells
UnitProfit C4:E4
OrderSize C12:E12
ResourcesUsed G7:G8
ResourcesAvailable I7:I8
TotalProfit I12

3. Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Total Profit equals the sumproduct of UnitProfit and OrderSize.

Trial and Error
With this formulation, it becomes easy to analyze any trial solution.
For example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. This solution has a total profit of 19000.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model
To find the optimal solution, execute the following steps.
1. On the Data tab, click Solver.

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.
2. Enter TotalProfit for the Objective.
3. Click Max.
4. Enter OrderSize for the Changing Variable Cells.
5. Click Add to enter the following constraint.

6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.
7. Finally, click Solve.
Result:

The optimal solution:

Conclusion: it is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available.

 


Analysis ToolPak
The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis.

To load the Analysis ToolPak add-in, execute the following steps.
1. Click on the green File tab. The File tab in Excel 2010 replaces the Office Button (or File Menu) in previous versions of Excel.
2. Click on Options.

3. Under Add-ins, select Analysis ToolPak and click on the Go button.

4. Check Analysis ToolPak and click on OK.

5. On the Data tab, you can now click on Data Analysis.

The following dialog box below appears.
6. For example, select Histogram and click OK to create a Histogram in Excel.




Home || Contact Us

www.000webhost.com