ARSH

Mastering
Excel
in
3 Levels

#translating_finance_for_businesses
 ARSH

Many still use Excel just for data entry, even

doing everything manually; that sadly makes
them slow & inefficient.

What they overlook is that it is a powerhouse

capable of automating tasks efficiently—if only
one knows how to harness its full potential.

1 Here’s a structured beginner-to-expert guide

on how you can truly master Excel and leverage
its advanced capabilities:

Level 1: The Foundation—Essential Shortcuts

& Navigation Hacks
Level 2: Data Analysis—Pivot Table, Lookup
functions, Power Query & Formatting.
Level 3: Mapping Data—Charts & Maps
Bonus: Excel Automation *in next post

#translating_finance_for_businesses
 LEVEL 1 THE FOUNDATION

Essential Shortcuts
& Navigation Hacks
Why search for the option in the tab when you
can automate those! You just need to learn
these shortcuts to save your tons of time:
CTRL + SHIFT + L → Apply/Remove Filters
CTRL + T → Convert data into a Table
2
ALT + = → Auto-sum selected cells/above cell
F12 → Save as
CTRL + 1 → Opens the Format Cells window
Alt + H + O + I → Auto Adjust Column
Alt + H + O + A → Auto Adjust Row
CTRL + ` → Toggles formula view mode
CTRL + ; → Inserts the current date
CTRL + SHIFT + : → Inserts the current time
Hit “Alt” and excel opens a whole new
world of formulae > Just follow the letters

Whenever giving a cell as reference, press F4; it will ensure that your
referenced cell stays fixed
 LEVEL 1 THE FOUNDATION

Essential Shortcuts
& Navigation Hacks
CTRL + 5 → Applies/removes strikethrough
CTRL + D → Fills data from the cell above
CTRL + ' → Copies value from the cell above
CTRL + R → Fills data from the left
3 CTRL + SPACE → Selects the entire column
SHIFT + SPACE → Selects the entire row
Alt + D + L → Insert Data Validation and Drop
down list
PROPER(), UPPER(), LOWER() → Changes text
cases

Example:
If you import a list of customer names with
inconsistent formatting, instead of doing every
formatting manually, just use this
=PROPER(A2) → Converts "siNgH" into "Singh".

Hit Ctrl + E, and see Excel Flash Fill in the rest intelligently (If you type a
pattern)
 LEVEL 2 DATA ANALYSIS

Pivot Tables

Pivot Table allows you to summarize large

datasets with just a few clicks. You can quickly
group, filter, and analyze data without writing
formulas.

4 How to use it?

1. Select Your Data—(Ensure your dataset has
headers (e.g., Date, Product, Sales, Region))
2. Insert a Pivot Table
Go to Insert > Pivot Table
3. Drag & Drop Fields
Rows: Product or others
Values: Sum of Sales or other desired result
Columns: Region or others

If new data is added, right-click the Pivot Table → Refresh and to get get
interactive buttons for filtering go to Insert > Slicer
 LEVEL 2 DATA ANALYSIS

Power Query

Tired of manually copying, pasting, and cleaning

data! Use Power Query and automate this boring
process
1. Clean & Transform Data Without Formulas
2. Merge Data from Different Files or Sheets
5
3. Refresh Data Automatically with One Click

How to use it?

Go to Data > Get Data > From Table/Range
Apply Data Transformations:
Home > Split Column
Transform > Data Type
Load Data Back to Excel:
Click Close & Load, and the cleaned data
appears in Excel!

Avoid loading unnecessary data! Instead of importing entire tables, use

Power Query’s "Transform Data" feature to filter, merge, and shape data
before loading it
 LEVEL 2 DATA ANALYSIS

Conditional
Formatting
Instead of manually scanning numbers, use
Conditional Formatting to highlight trends,
outliers, and duplicates automatically.

How to use it?

6 Select Your Data
Go to Home > Conditional Formatting
Choose the Rule Type:
Select "Use a formula to determine which
cells to format."
Enter Your Custom Formula:
Eg: Highlight sales above ₹50,000 → =First
Selected Cell>50000
Set Formatting:
Click Format, choose the font, fill color, or
border you prefer, and click OK.
Apply and Save

Spot trends, outliers, or errors at a glance. If you have thousands of rows of

sales data, it can instantly highlight underperforming regions, allowing you
to take action quickly
 LEVEL 2 DATA ANALYSIS

Advanced Lookup
Function - XLOOKUP
Analysts still use VLOOKUP() HLOOKUP() INDEX-
MATCH or FILTER, but the modern Excel function
XLOOKUP() is alone way ahead and effective
than these.

7 Why XLOOKUP is a Game-Changer?

Works in Any Direction—Unlike VLOOKUP
(vertical) and HLOOKUP (horizontal), XLOOKUP
works both left-to-right, right-to-left, top-to-
bottom, and sideways.
No Need for the Index Numbers—Unlike
VLOOKUP and HLOOKUP, which require
column/row index numbers, XLOOKUP directly
references the return range.
Built-in Error Handling – Unlike others it
returns a custom message (like: Not Found)
instead of showing formula error.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],

[match_mode], [search_mode]), where the first three arguments are
required and the rest are optional.
 LEVEL 3 DATA VISUALISATION

Beyond Charts -
Creating the Maps
Did you know Excel can create dynamic,
interactive maps that go far beyond just plotting
dots on a world map? With 3D Maps (Power
Map) and other techniques, you can visualize
sales, demographics, logistics, and financial
8
trends with real impact.

Step-by-Step:
Creating an Advanced 3D Map
1..Prepare Your Data: Create a table with
location-based data, ensuring separate
columns for:
Country (e.g., India, USA)
State/Province (e.g., Maharashtra)
City/District (e.g., Mumbai, Los Angeles)
Metrics to Visualize (e.g., Revenue,
Population, Sales Volume)

#translating_finance_for_businesses
 LEVEL 3 DATA VISUALISATION

Beyond Charts -
Creating the Maps
2..Open 3D Maps (Power Map)
Select your data
Go to Insert > 3D Map > Open 3D Maps
Click New Tour to start designing the
visualization
9
3..Layer & Customize the Map
Set Location Fields: Drag & drop Country,
State, or City into the "Location" field
Choose Data Type:
Bubble Map → Perfect for comparing
numbers (e.g., sales per city)
Heat Map → Shows intensity variations
(e.g., population density)
Region Map → Colors entire areas (e.g.,
sales by state)

#translating_finance_for_businesses
 LEVEL 3 DATA VISUALISATION

What If Your Excel Doesn’t Include Maps

It could be due to:
Version limitation,
Disabled add-ins, or
Software updates.
Here’s how to resolve the issue:
Verify Your Excel Version—3D Maps is
10 available in Excel 2016 and later.
Enable the Add-in—Go to File > Options >
Add-ins, select COM Add-ins, and enable
Microsoft Power Map for Excel, if available.
Use Power Map Add-in (Excel 2013 Users)
— Download and install the Power Map
add-in from Microsoft’s official site.

Stay Tuned
to Learn

Excel Automation

#translating_finance_for_businesses
Follow & Share

ARSH

for more such

post

#translating_finance_for_businesses