www.fmworldcup.

com

The Ultimate
EXCEL
Handbook

Follow Financial Modeling World Cup on LinkedIn

 Table of Contents
1. Functions
i. Top 10 Functions

ii. Text Splitting Functions

2. Conditional Formatting
3. Data Table
4. Pivot Table
5. Data Validation
6. Group Data
7. Power Query
8. Excel Shortcuts
9. Getting Started with Python in EXCEL
10. How ChatGPT can Simplify Excel Workflow?
11. History of Microsoft Excel

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

()
I M
R
=L =T
ET
()

( )
DA
M B
=L A
=MAX
()

Functions
i.Top 10 Functions

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

()
I M
R
=L =T
ET
()

( )
DA
M B
=L A
=MAX
()

Functions
ii.Text Splitting Functions

Follow Financial Modeling World Cup on LinkedIn

 TEXT TO COLUMNS

Allows to split text in Excel based on delimiters like

commas or spaces.

Simply select the cell or range of cells, go to "Data =>

Text to Columns", and insert the required settings.

Alternatively, use the shortcut "Alt + A + E".

Remember, that this function replaces the text in

the cell you chose to operate with.
Excel

Alt
+ A + E

Alt + A + E

Alt + A + E
 LEFT/RIGHT/MID
These functions are a simple yet effective way to extract
a specific number of characters from a cell, starting from
either the left, right, or middle side.
It's useful for shortening text, such as creating a country
code or taking the first letter of a name.

The syntax is as follows: "=LEFT (cell to extract from,

number of characters to extract)".
For MID need to mention from which character to start
and how many to retrieve.
Excel

RIGHT(text, [num_chars])

RIGHT(text, [num_chars])
 TEXTAFTER/TEXTBEFORE

A function is a useful tool for retrieving text that comes

after or before a specific delimiter, such as a space,
comma, symbol, word, or phrase.

The syntax is "=TEXTAFTER (cell to extract from, “delimiter”,

serial number of delimiters to start with)".

Function TEXTBEFORE works in a similar but opposite way.

Excel

=TEXTAFTER(text,delimiter,[instance_num],

=TEXTAFTER(text,delimiter,[instance_num],
[match_mode], [match_end], [if_not_found])

[match_mode], [match_end], [if_not_found])

 TEXTSPLIT
A function that returns text divided into rows or
columns based on some specific delimiters.

Syntaxis is “=TEXTSPLIT (cell you are working

with, “delimiter to divide into columns”,
”delimiter to divide into rows”).
Excel

=TEXTSPLIT(text,col_delimiter,[row_delimiter],
=TEXTSPLIT(text,col_delimiter,[row_delimiter],
[ignore_empty], [match_mode], [pad_with])

[ignore_empty], [match_mode], [pad_with])

 FLASH FILL

A function that retrieves information from nearby

cells based on your example.

It uses some sort of AI to understand which

pattern you follow and will fill other required
cells in a similar way.

Use the shortcut “Ctrl + E”!

Ctrl + E
Excel

Ctrl + E

Ctrl + E
www.fmworldcup.com

<=
=>

Conditional
Formatting

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

Data Table

Follow Financial Modeling World Cup on LinkedIn

Financial Modeling in Excel FINANCIAL MODELING
WORLD CUP

5 Excel features you should know

Data Table
A great tool for your what-if analysis. A range of cells in which you can
change values in some of the cells and come up with different answers
to a problem.

How to create it?

Instructions Example
1. Write down input data Interest rate, number of periods and starting
amount if you want to see how your savings
amount would differ under different scenarios

2. Calculate the value you Savings amount from the initial input data
want to find out

3. Write down additional Changes in interest rate, starting amount

input data you want to test

4. Go to Data -> What-If

Analysis -> Data Table

5. Put in row and column Row input cell - interest rate from initial input
input cell that corressponds data;
to the layout you have Column input cell - starting amount from initial
created in step #3 and input data
press OK

By changing the
initial input data,
your data table
will update too!
www.fmworldcup.com

Pivot Table

Follow Financial Modeling World Cup on LinkedIn

Financial Modeling in Excel FINANCIAL MODELING
WORLD CUP

5 Excel features you should know

Pivot Table
A PivotTable is a powerful tool to calculate, summarize, and analyze data
that lets you see comparisons, patterns, and trends in your data.

How to create it?

Instructions Example
1. Have an Excel table with Store order history with customed ID, product ID,
data product price and product category

2. Go to Insert -> PivotTable Choose the table from step 1

and choose a table or a
range you want to analyze

3. Choose fields to Look at the total sales and count of products

bought by product categories. Choose columns,
summarize the data by
Rows, Filters, Values – everything is customizable
and you can play with the report!

4. Go to PivotTable Analyze Add a slicer, insert timeline, add subtotals and

or Design tabs to other things
customize the Pivot Table
www.fmworldcup.com

Data
Validation

Follow Financial Modeling World Cup on LinkedIn

Financial Modeling in Excel FINANCIAL MODELING
WORLD CUP

5 Excel features you should know

Data Validation
Use data validation to restrict the type of data or the values that users
enter into a cell. One of the most common data validation uses is to
create a drop-down list.

How to create it?

Instructions Example
1. Select the cell you want On your input data Excel sheet, create a cell
to create a drop-down list in where users will be able to choose between
different store locations

2. Select Data -> Data

Validation

3. Choose what will the users Offer to choose from a list of store locations such
be able to choose (numbers, as “USA, Spain, UK, Australia, Japan, Germany”
dates, time, custom text, etc.)

4. Create Input Message so “Select Store Location”

that users know what they
are choosing

5. Link other data in your Link profit and loss statements to geographical
model to this dropdown list, location of the stores from the dropdown by using
so that values update “IF” statements
automatically
www.fmworldcup.com

Group Data

Follow Financial Modeling World Cup on LinkedIn

Financial Modeling in Excel FINANCIAL MODELING
WORLD CUP

5 Excel features you should know

Group Data
If you have a list of data you want to group and summarize, you can
create an outline of up to eight levels. Very important for financial
models to switch between different levels of data complexity. Group data
instead of hiding rows/columns!

How to do it (right)?
Instructions Example
1. Select rows/columns to Level 1 – for top level management, Level 3 or 4 –
group for accountant in-depth data review

2. Go to Data -> Group ->

Group

3. Group again, if you want

to go into more detail

4. Press “-” to collapse the

groups
www.fmworldcup.com

Power Query

Follow Financial Modeling World Cup on LinkedIn

Financial Modeling in Excel FINANCIAL MODELING
WORLD CUP

5 Excel features you should know

Power Query
Power Query (known as Get & Transform in Excel) is a great tool for minimizing
repetitive daily tasks. You can import or connect to external data and then shape this
data. For example, remove a column, change a data type, or merge tables in ways
that meet your needs. Then, you can load your query into Excel to create charts and
reports.

How to create it?

Instructions Example
1. Connect to Data Pull in data from a different Excel file that
Go to Data -> Get Data contains participant names and stage points

2. Transform Data Clean Data - remove unneeded columns, assign

Do all kinds of changes to data types, rename columns for better
your data while the original understanding, etc.
dataset stays the same

3. Combine Data Pull in another data source on the background of

Add other datasets and the participants - country, company, age group,
make connections between etc. Append Queries.
them to get more insights
4. Load Data Load the appended query into the Excel file. After
Load the transformed and each stage, add information on the points and
combined data to your refresh dataset.
worksheet and enjoy the
clean dataset
 Already know this? SIGN UP for:

Microsoft Excel World Championship

October 7 - December 9, 2023

at HyperX Arena, Las Vegas

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

www.fmworldcup.com

Alt F11

C
Ctrl

Excel
Shortcuts

Follow Financial Modeling World Cup on LinkedIn

 GENERAL

Alt F1 Create embedded chart

F11 Create chart in new worksheet

Ctrl Shift F4 Find previous match

Shift F4 Find next match

Ctrl Alt V Display the Paste Special dialog box

F4 Repeat last action

Ctrl W Close current workbook

Alt F4 Close Excel

Ctrl N Create new workbook

Ctrl O Open workbook

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 NAVIGATION

Alt PgDn Move one screen right

Alt PgUp Move one screen left

PgUp Move one screen up

PgDn Move one screen down

Ctrl Move to right edge of data region

Ctrl Move to left edge of data region

Home Move to beginning of row

Ctrl End Move to last cell in worksheet

Ctrl Home Move to first cell in worksheet

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 FORMATTING

Alt H A C Align center

Alt H A L Align left
Alt H A R Align right
Alt H F G Increase font size one step
Alt H F K Decrease font size one step
Ctrl Shift $ Currency Format
Ctrl Shift % Percentage Format
Ctrl Shift ^ Scientific Number Format
Ctrl Shift # Date Format
Ctrl Shift @ Time Format
Ctrl Shift ! Number Format
Ctrl Shift _ Remove borders
Ctrl ;
Enter Current Date
Ctrl : Enter Current Time

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 WORKBOOK

Shift F11 Insert new worksheet

Ctrl PgDn Go to next worksheet

Ctrl PgUp Go to previous worksheet

F6 Move to next pane

Shift F6 Move to previous pane

Ctrl Tab Go to next workbook

Ctrl Shift Tab Go to previous workbook

Ctrl F9 Minimize current workbook window

Ctrl F10 Maximize current workbook window

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 SHORTCUT ALPHABET

Ctrl A Select All

Ctrl B
Bold
Ctrl C Copy
Ctrl D Fill Down
Ctrl E Flash Fill
Ctrl F Find
Ctrl G Go to
Ctrl H Find & Replace
Ctrl I Italic
Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6
 Ctrl J Justify
Ctrl K Hyperlink
Ctrl L Convert Data to Table Format
Ctrl M Indent the Selected Cells
Ctrl N New Workbook
Ctrl O
Open Workbook
Ctrl P Print
Ctrl Q Quit Excel
Ctrl R Fill Right

Ctrl S Save Workbook

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 Ctrl T Create a Table
Ctrl U Underline
Ctrl V Paste
Ctrl W Close Workbook
Ctrl X Cut
Ctrl Y Redo
Ctrl Z Undo

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 CTRL + 0-9

Ctrl 0 Hide the selected columns

Ctrl 1 Open the Format Cells dialog

Apply or remove Bold formatting to font

Ctrl 2 (CTRL+B)
Apply or remove Italics formatting to font
Ctrl 3 (CTRL+I)
Apply or remove Underline formatting to font
Ctrl 4 (CTRL+U)

Ctrl 5 Apply or remove strikethrough formatting to font

Switch between hiding objects, displaying objects,

Ctrl 6 and displaying placeholders for objects

Ctrl 7 None

Ctrl 8 Display or hide the outline symbols

Ctrl 9 Hide the selected rows

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 CTRL + SHIFT

Ctrl Ctrl+Shift, then scroll your mouse

Shift wheel up to go left, down to go right

Insert argument names and parentheses

Ctrl Shift A when the insertion point is to the right
of a function name in a formula

Ctrl Shift F or P Format fonts in the Format Cells dialog

Ctrl Shift G Open the Workbook Statistics dialog

Ctrl Shift L Apply / Remove Auto-filter

Select all cells containing Notes

Ctrl Shift O (earlier called comments)

Ctrl Shift U Apply / Remove Auto-filter

Insert a threaded comment /

Ctrl Shift F2 Open and reply to a threaded comment

Ctrl Shift F3 Open create name from

selection dialog box

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

Ctrl Shift F6 Switches between all Excel windows

Ctrl Shift F10 Open the context menu

Ctrl Shift F12 Opens print worksheet dialog box

Ctrl Shift + Open the Insert dialog to

insert blank cells

Ctrl Shift : Enter the current time

Ctrl Shift _ Remove the outline border from

the selected cells

Ctrl Shift ~ Apply the General number format

Insert Rows / Columns when row

Ctrl Shift = column is selected or display insert
menu when cell is selected
Apply the Number format with two
Ctrl Shift 1 decimal places, thousands separator,
and minus sign (-) for negative values

Ctrl 2 Apply the Time format with

Shift the hour and minute, and AM or PM

Ctrl Shift 3 Apply the Date format with

the day, month, and year

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

 Apply the Currency format with two
Ctrl Shift 4 decimal places (negative numbers
in parentheses)

Ctrl Shift 5 Apply the Percentage format with

no decimal places

Ctrl Shift 6 Apply the Scientific number format

with two decimal places

Ctrl Shift 7 Apply an outline border to

the selected cells

Ctrl Shift 8 Select the current region

around the active cell

Ctrl Shift 9 Unhide Rows

Select the entire worksheet (equivalent

Ctrl Shift Space to CTRL+A) Select all objects on a
worksheet when an object is selected

Ctrl Shift PgDn Select the current and next sheet

in a workbook

Ctrl Shift PgUp Select the current and previous sheet

in a workbook

Ctrl Shift Home Extend the selection of cells to the

beginning of the worksheet

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6

Ctrl Shift End Extend the selection of cells
to the last used cell on the worksheet

Extends the selection of cells to

the last nonblank cell in the same
column or row as the active cell, or
Ctrl Shift if the next cell is blank, extends the
selection to the next nonblank cell

Switches to the previous tab in a dialog,

Ctrl Shift Tab or (if no dialog is open) switches
between all Excel windows

Ctrl Shift Enter Confirm a formula as Array formula

Copy the value from the cell above the

Ctrl Shift ” active cell into the cell or the formula bar

Sign Up by October 6

Microsoft Excel World Championship

October 7 - December 9, 2023
at HyperX Arena, Las Vegas
www.fmworldcup.com

Getting Started with

Python in EXCEL

Follow Financial Modeling World Cup on LinkedIn

 Python availability in Excel introduces a fresh realm of
possibilities for data analysis that was once primarily
accessible to data scientists and developers. Now,
within the comfort of your well-known spreadsheet
environment, you have the ability to tap into the
capabilities of Python.

But how to start using Python in Excel and what

functionalities does it offer?

Just type =PY()

Could it be simpler? Write =PY() and get started! After the
formula the Excel cell will change and you can enter your
Python code and see the visualizations or calculations
right there in your Excel spreadsheet!
www.fmworldcup.com

You can also find Python under the formulas section in Excel!

Follow Financial Modeling World Cup on LinkedIn

 Ok, but what can I actually create with Python in Excel?

1.Creating a Data Frame

Python code runs in a controlled cloud container with
limitations on its capabilities.

Python code lacks internet access and the ability to reach

files on your local computer. The Excel workbook defines
the boundaries of Python's reach.

With these constraints in mind, it's not surprising that

creating a pandas DataFrame, for instance, often serves
as the initial step when incorporating Python within Excel.

Constructing a pandas DataFrame from an Excel table is

a clear process. The newly introduced PY() function
www.fmworldcup.com

facilitates the construction of Python formulas

containing the necessary Python code.

Follow Financial Modeling World Cup on LinkedIn

 2.Writing the Code

So, here’s how the code works:

1 The xl() function is employed to extract data

from Excel, generating a pandas DataFrame.

The initial parameter indicates the data's

2 location, which in this instance refers to
the Excel table within the range A2:D98.

The subsequent parameter informs the xl()

3
www.fmworldcup.com

function about the presence of headers

(column names) within the data.

You can assign a variable name to your

4 DataFrame like you were using named ranges
or LET() function in regular Excel. You can
subsequently access the DataFrame in
subsequent Python formulas using the
same variable name.

Follow Financial Modeling World Cup on LinkedIn

 Press Ctrl+Enter to execute the Python formula. It
might take a moment for the code to work. If your
code is error-free, here's what will appear:

Hover the mouse on the card icon to see a preview of the

data within the DataFrame:
www.fmworldcup.com

Source: Anaconda.com

The preview shows the first 5 and last 5 rows of the data.

Follow Financial Modeling World Cup on LinkedIn

 3.Advanced Data Visualizations

You can also use the powerful visualization libraries

of Python, including Matplotlib and Seaborn, directly
within your Excel workbook to achieve thorough and
insightful data portrayal.

The new Image in Cell feature, specifically, the Create

Reference option allows to create an image/shape over
the cells that can be resized.

This has the capacity to lead to powerful interactive

dashboards, offering a realm of opportunities alongside
Python's data refinement capabilities.
www.fmworldcup.com

Source: Anaconda.com

Follow Financial Modeling World Cup on LinkedIn

 4.In-Depth Statistical Analysis

Enhance your analysis by harnessing the capabilities

of Python's libraries like pandas and statsmodels.
Conduct extensive statistical operations seamlessly
within your Excel cells. You don't need advanced data
science expertise— advanced analytics have become
achievable for all. To get descriptive statistics table
like the one below, use a formula name.
describe().
www.fmworldcup.com

Source: Anaconda.com

Follow Financial Modeling World Cup on LinkedIn

 5.K-Means Clustering

You can also easily make clusters with Python in

Excel which makes the work for data analysts a lot
easier. This previously wasn't possible by default.
www.fmworldcup.com

Source: Mr. Excel

Follow Financial Modeling World Cup on LinkedIn

 6.Create Pivot Tables
With Python integration, you can also create an Excel-like Pivot
Table in a DataFrame. Compared to the basic Excel Pivot
Tables, these ones are easier to use since they do automatic
recalculation without refreshing. As well as you can use Date
Grouping for some more possibilities within your Pivot Table.

Source: Mr. Excel

7.Bring In External Data

www.fmworldcup.com

External data can be pulled into Python in Excel workflows

using Excel's connectors and Power Query.

Consider that the Python integration is available in

preview form only on Windows for Microsoft 365
Insider beta channel members, and only the "Office
365" cloud version receives Python integration.

Follow Financial Modeling World Cup on LinkedIn

www.fmworldcup.com

How ChatGPT Can

Simplify Excel Workflow?

Follow Financial Modeling World Cup on LinkedIn

HOW CHATGPT CAN SIMPLIFY
OUR EXCEL WORKFLOW

Let’s analyze how ChatGPT can create an Excel macro

In a recent video, Excel MVP Kevin Stratvert uses ChatGPT to create an Excel
macro that functions as an invoicing app, using customer email addresses and the
( . .( . ( (, ( ) . .(
) ( ( ) () ( ( ( ( ( , . . ()
HOW CHATGPT CAN SIMPLIFY
OUR EXCEL WORKFLOW

Simplify nested IF formulas with ChatGPT

You can also simplify work with nested IF formulas by using

ChatGPT with Excel. Simply by typing in the description of
the problem, ChatGPT will break it down into steps
and provide the Excel formula necessary.
HOW CHATGPT CAN SIMPLIFY
OUR EXCEL WORKFLOW

Can ChatGPT write Excel Power Query codes?

Yes, it can. All you need to do is type in a command, for example: “Write an Excel
Power Query M code to append sheet1 from workbook1 and sheet2 from workbook2
and remove duplicates from column B and ChatGPT will come back with an example
of a Power Query M code that can be used. Also, ChatGPT gives a detailed
explanation about the Power Query M code. Then, just copy and paste
the code and you’re good to go.
HOW CHATGPT CAN SIMPLIFY
OUR EXCEL WORKFLOW

Analyzing .csv with ChatGPT

Let’s say you need to find some very specific information that’s located
in an Excel sheet. This is another case when using ChatGPT could save you
some time. Export your Excel sheet as a CSV file. Copy and paste some
of the data into ChatGPT (unfortunately it’s not possible to copy and paste the
whole thing as there’s still some content limitations to what ChatGPT can digest).
ChatGPT will explain the data and now you can start asking questions to
help locate specific information that you need. ChatGPT can successfully
locate and show you the information you are looking for, possibly saving you
time and a headache doing the same with Excel.

Even though ChatGPT is super powerful and useful – the data or solutions
it provides can sometimes be inaccurate. We advise you to always
double-check and see if the answers provided are correct
(and don’t rely on ChatGPT for everything just yet!
www.fmworldcup.com

History of
Microsoft Excel

Follow Financial Modeling World Cup on LinkedIn

HISTORY OF MICROSOFT EXCEL

1982
Microsoft launched Multiplan, a spreadsheet program, a competitor to
Lotus 1-2-3.

1985
Birthday of Microsoft Excel on Mac

1987
Excel 2.0: Birthday of Microsoft Excel on Windows

1990
Excel 3.0 Included toolbars, drawing capabilities, outlining, add-in support,
3D charts, and many more new features

1992
Excel 4.0 Introduced auto-fill. Also, an easter egg in Excel 4.0 reveals a hidden
animation of a dancing set of numbers 1 through 3, representing Lotus 1-2-3,
which is then crushed by an Excel logo.

1993
Excel 5.0: Introduction of Visual Basic for Applications (VBA), taking Excel to
unprecedented levels of functionality

1997
Excel 97: Introduced the Office Assistant (Clippy) and included
improvements to VBA.

www.fmworldcup.com
HISTORY OF MICROSOFT EXCEL

1999
Excel 2000: Introduced the Clipboard feature, allowing users to interact
with and use multiple objects simultaneously.

2002
Excel XP (Part of office XP): Featured a new task pane, improved data
validation, and added the AutoRecover function for better data
protection.

2003
Excel 2003: Introduction of the List feature (precursor to Tables), which
enabled easier data management, and improvements to data analysis tools.

2007
Excel 2007: Introduced the Ribbon interface, named variables, and Open
Office XML file formats.

2010
Excel 2010: Introduced Power Query (add-in), enabling users to work with
larger datasets, as well as Excel Web App and mobile version for Windows 7.

2013
Excel 2013: New start screen, 50 new functions, Flash Fill tool,
recommended charts, and three new chart tools; design, layout,
and format.

www.fmworldcup.com
HISTORY OF MICROSOFT EXCEL

2016
Excel 2016: Added the Ink Replay button, Shared with Me feature, and
integration with Power BI for advanced analytics.

2017
Office 365 including Excel 365 version introduced which had all updates
automatically incorporated once they are released.

2018
The release of Excel 2019 introduced new functions like CONCAT and
TEXTJOIN, improved the Power Query Editor with M Intellisense, and
added new data visualization options such as funnel charts and
map charts. Introduction of the Dynamic Arrays for Office 365 users.

2021
Excel 2021, new functions introduced: LAMBDA & LET to define
your own formulas.

2022
Release of additional dynamic array function kit (MAP, LAMBDA, SCAN, etc.)

2023
IMAGE function released, waiting: Microsoft Copilot for Excel (aka Clippy AI?)

www.fmworldcup.com
 Already know this? SIGN UP for:

Microsoft Excel World Championship

October 7 - December 9, 2023

at HyperX Arena, Las Vegas

Sign up for MICROSOFT EXCEL WORLD CHAMPIONSHIP by October 6