Module 3:

Pivots, Charts,
and Macros
Chapter 7:
Tools for Model
Display
Using Excel for Business and Financial Modelling,
3rd Edition by Danielle Stein Fairhurst, Wiley
Basic Formatting

● A model that is well formatted will be more readable,

user-friendly, and easy to navigate.
Custom Formatting

● Customizing the way data is displayed in cells without changing

the underlying data
Custom Formatting
Practical Exercise 7-1: Changing the Date Format

● Change the format of the date today to 18

November 2021.

5
Custom Formatting
Practical Exercise 7-2: Custom Currency Symbols

● Go to a blank sheet, and type 582504 into a cell. If you have not
defined any formatting on that cell yet, Excel will assign a
General format to it.
● Change the number to currency, preceded by your country’s
monetary symbol, by using the Currency category in the Format
Cells dialog box.
● Change the currency symbol to that of the Japanese Yen, or
some other commonly used currency.
● Change the currency symbol to that of a currency we’ve made
up—Elbonian Spree (EL).
6
Custom Formatting
Excel Number Formats

● Custom number formats control how numbers look in Excel.

The key benefit to number formats is that they change how a
number looks without changing any data.
● Excel custom number formats have a specific structure. Each
number format can have up to four sections, separated with
semi-colons as follows:
Custom Formatting
Excel Number Formats

● Four elements of number formats have :

1. Positive number
2. Negative number
3. Zero
4. Text
● Sample: $*#,##0;[Red]$*-#,##0; $*”4”;”*”
Custom Formatting
Excel Number Formats

● Although a number format can include up to four sections, only

one section is required.
● By default:
○ the first section applies to positive numbers
○ the second section applies to negative numbers
○ the third section applies to zero values, and
○ the fourth section applies to text.

9
Custom Formatting
Excel Number Formats

● When only one format is provided, Excel will use that format for
all values.
● If you provide a number format with just two sections, the first
section is used for positive numbers and zeros, and the second
section is used for negative numbers.
● To skip a section, include a semi-colon in the proper location,
but don't specify a format code.

10
Custom Formatting
Placeholders

Character Purpose

0 Display insignificant zeros

# Display significant digits

? Display aligned decimals

. Decimal point

, Thousands separator

* Repeat following character

_ Add space

@ Placeholder for text

Custom Formatting
Practical Exercise 7-3: Custom Formatting

1. Highlight the data and go to the Custom Formatting dialog box.

2. Change the number format to #,##0 and add a comma to the
end of the format type
3. Use the ROUND function in Column C, to round the numbers to
the nearest thousand.

12
Conditional Formatting

● A tool that allows you to

apply formats to a cell or
range of cells, and have that
formatting change,
depending on the value of
the cell or the value of a
formula
Conditional Formatting

● To Apply Conditional Formatting

○ Select the desired cell or cells in the spreadsheet. Let’s

say, for example, you wanted to highlight with a red font
any cell value that is between 1 and 10.

○ On the Home tab, in the Styles group, select Conditional

Formatting. Select Highlight Cells Rule and then Between.
Type in 1 and 10 and define the desired font color.

14
Conditional Formatting

● To Remove Conditional Formatting

○ On the Home tab, in the Styles group, select Conditional

Formatting. Select Manage Rules, and you can delete your
chosen rules one by one.

○ Alternatively, you can clear rules from selected cells or

from the entire sheet by clicking on Clear Rules.

15
Conditional Formatting
Data Bars

● Data bars are dynamic bar charts

that you can apply to any numerical
data in Excel. They graphically show
the relative size of each value

16
Conditional Formatting
Practical Exercise 7-4: Data Bars

● Go to a table of numeric data, and highlight the numbers in the

following three steps:

○ On the Home tab, in the Styles group, select Conditional

Formatting, and then Data Bars.

○ Choose your desired bar.

○ Practice changing the underlying numbers, and see the

bars change.

17
Conditional Formatting
Icon Sets and Color Scales

● Icon Sets allow you to conditionally display a

small icon that represents changes in data.

18
Conditional Formatting
Practical Exercise 7-5: Icon Sets

● Apply Icon Set formatting to the Net Income Ratio presented in

Exercise 7-5

19
Conditional Formatting
Practical Exercise 7-6: Color Scales

● Apply Color Scales formatting to the Investment values

presented in Exercise 7-6

20
Conditional Formatting
Edit Formatting Rule to Hide Icons

21
Conditional Formatting
Mixed Formatting

22
Sparklines

● Excel also has a useful micro charting feature termed the

“sparkline”, which is a great way of displaying data trends in a
small space.
● These lines are not as descriptive as regular graphs and charts,
but they are very effective in displaying a quick view about the
trends in the data or metrics.
Sparklines
Practical Exercise 7-7: How to Create a Sparkline

1. Select the data series for which the sparkline needs to be

created
2. Under the Insert tab in the Sparklines group, select Line.
3. In the Create Sparklines dialog box, the data range is
automatically selected, so enter the cell or cell range in which
you want the sparklines to be placed and click OK.
4. The sparklines are drawn in the selected cells.
Sparklines
Practical Exercise 7-8: How to Edit Sparklines

● Select the sparklines and click on Edit Data in the Sparkline

ribbon under the Design tab
● This will open the Edit Sparklines dialog box, where you can
change the data range, the sparkline location or change to
columns
● To highlight important data points, such as high point or low
point
● Select from a series of checkboxes in the Show group, under
the Design tab.
Customizing the Display Settings
Useful Display Settings
● Display settings can be changed by clicking on the File tab in the
top left-hand corner, and then selecting Options.
○ Hide row and column headings by unselecting Show Row and
Column Headers.
○ Remove sheet tabs by unselecting the Show Sheet Tabs box.
○ Disable the scroll bars by unselecting the Show Vertical and
Horizontal Scroll Bars.
○ Hide the formula bar by unselecting Show Formula Bar.
● In Excel for Mac, these options can be found under Excel, then
Preferences and under View, in the Window Options section.
Customizing the Display Settings
Practical Exercise 7-10: Restrict the Work Area

You can also make the work area of the sheet much smaller so that
users cannot enter data in cells you don’t intend them to

1. Highlight the first column you don’t want to see (for example,
column I) and press Control+Shift+Right Arrow. Now right-click
and select Hide.
2. Highlight the first row you don’t want to see (for example, row
9) and press Control+Shift+Down Arrow. Now right-click and
select Hide.

Go to Home tab, then under "Format" choose hide

Customizing the Display Settings
Restricting Incorrect Data Entry with Data Validations

● Using data validation will allow you to control the data that is
typed into your model and avoid errors.
● Instead of restricting where data can be entered, Data
Validation can also restrict what can be entered into the cell.

Data validation can be found under the "Data" tab

Customizing the Display Settings
Practical Exercise 7-11: How to Apply Data Validations

Restrict a cell’s entries so that it will only allow values between 1

and 100.
1. Select your input cell(s) (either select a single cell or highlight
whole range).
2. On the Data tab, from the Data Tools group, click on the Data
Validation button.
3. Choose Whole Number under the Allow drop-down.
4. Leave the Between option as it is, and enter the values 1 and
100 under the Minimum and Maximum value fields; click OK.
29
Customizing the Display Settings
Error Alert Tab

● In the Data Validation dialog box, you can also enter your
own, customized error message on the Error Alert tab
● This lets you display a message to the user when the
value entered into a cell is invalid.
● The message will be activated when the user presses
Enter or when the try to select a different cell.
Customizing the Display Settings
Practical Exercise 7-12: Error Alert Tab

Add an error alert message that will pop up when entering

incorrect data

31
Validations as Drop Down List
Practical Exercise 7-13: Using Validations to Create a Drop-Down List

1. Create a list of valid regions:

○ United Kingdom

○ Germany

○ India

○ Australasia

○ France

○ United States
2. Select the cell that should contain the drop-down list. 32
Validations as Drop Down List
Practical Exercise 7-13: Using Validations to Create a Drop-Down List

3. Bring up the Data Validation box:

○ On the Data tab from the Data Tools group, click on the
Data Validation button.
4. In the Allow section, change Any Value to List
5. In the Source field, enter the range that contains the data you
want to appear in the drop-down list. Leave “In-cell dropdown”
selected.

33
Form Controls can be found in "Developer Tab"

● Form controls are objects such as drop-down boxes and option

buttons that sit over the top of Excel sheets like charts do

Accessing Form Controls

● You will need to have the Developer tab showing in the ribbon.
If you cannot see it in the ribbon, you’ll need to change your
options.
Form Controls
To display the Developer tab in the ribbon:

1. Click on the File menu in the top left-hand corner of your screen.

2. Scroll down to Options at the very bottom and select it.

3. Select Customize Ribbon.

4. In the box to your right, tick the Developer box.

In Excel for Mac:

1. Click Excel in the upper left-hand corner.

2. Click Preferences.

3. Select Ribbon & Toolbar.

Form Controls
Checkboxes

● Checkboxes are very handy tools that can help users

choose their desired options from a list.

How to Create Checkboxes:

1. Go to the Developer tab on the Excel toolbar and click on
Insert
2. From the Form Controls menu, select the checkbox
3. Draw the checkbox anywhere in your sheet, and you
should see the box with a name Checkbox 1.
Form Controls
Checkboxes

● To link the checkmark, open the Format Control associated with

the checkbox.
● Right-click on the checkbox and select Format Control
● In the Format Control dialog, you can select the default value of
the checkbox (unchecked, checked, or mixed) and the cell to
which you want to link the checkbox status.
● You can add multiple checkboxes corresponding to the items in
the list.

37
Form Controls
Practical Exercise 7-14: Creating Checkboxes
● Create a checkboxes for each of the given
regions in the list
● Based on the Boolean status, design the
financial model to pick the respective region
● Calculate for the total values for regions within
Europe

38
Form Controls
Option Button

● Allows users to choose one item from a list

● Also called a Radio button
● offers you the ability to create this logic to restrict the number
of options users can choose in the financial model

How to Create Option Buttons:

1. Go to the Developer tab on the Excel toolbar and click Insert.
2. From the Form Controls menu select the Option button
3. Draw the Options button in front of the item in the list
39
Form Controls
Option Button

● You can edit the properties of the button using Format Control.
● In the Format Control dialog box, you can select the default
value of the Option button (unchecked or checked) and the cell
to which you want to link the Option button status
● You can add multiple Option buttons corresponding to the
items in the list. Based on the item number selected, you can
design your financial model to carry out the necessary analysis.

40
Form Controls
Practical Exercise 7-15: Creating Option Buttons

● Create Option buttons corresponding to the items in the list

● Create a formula linked to the output cell.
● Using INDEX or CHOOSE function, return the value of the result
if Germany is selected

41
Chapter 8:
Tools for Financial
Modelling
Using Excel for Business and Financial Modelling,
3rd Edition by Danielle Stein Fairhurst, Wiley
Hiding Sections of a Model
How to Hide a Column or Row

1. Select the column or row by clicking on the heading.

2. Right-click and select Hide.

43
Hiding Sections of a Model
How to Hide a Sheet

● If you have information on a model you do not want users to

see at all (e.g., salary information on budget models), you can
hide the sheet with sensitive information, and then protect the
workbook.

1. Select the sheet(s) you want to hide or unhide.

2. Right-click on the sheet tab and select Hide/Unhide.

44
Hiding Sections of a Model
How to Hide a Sheet

● A way to hide a sheet even more securely is to

change the properties of the sheet in the Visual
Basic code to Very Hidden.

1. Right-click on the tab name of your worksheet, and

select View Code
2. This will bring up your Visual Basic Editor.
3. Click on the sheet that you want to hide in the top
left, and change the properties of that sheet in the
bottom left from Visible to Very Hidden
45
Hiding Sections of a Model
Practical Exercise 8-3: Hiding Rows and Sheets

1. Hide rows 11-16

2. Set visibility of sheet Ex 8-1 to Very Hidden

46
Grouping

● Using the grouping tool can make hidden data more obvious
and avoid its being overlooked.

● Go to the Data tab and select Group/Ungroup from the Outline

section

47
Grouping
Practical Exercise 8-2: Grouping

● Group rows 3-8 and 11-16

48