5 Excel features every Data Analyst should know PDF 22
pdf
keyboard_arrow_up
School
Grand Canyon University *
*We aren’t endorsed by this school
Course
AUDITING
Subject
Information Systems
Date
Nov 24, 2024
Type
Pages
6
Uploaded by BaronDeer3087
5 EXCEL
FEATURES
EVERY
DATA
ANALYST
SHOULD KNOW
Financial Modeling in Excel
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
2. Calculate the value you
want to find out
3. Write down additional
input data you want to test
Savings amount from the initial input data
Changes in interest rate, starting amount
4. Go to Data -> What-If
Analysis -> Data Table
5. Put in row and column
input cell that corressponds
to the layout you have
created in step #3 and
press OK
By changing the
initial input data,
your data table
will update too!
Row input cell - interest rate from initial input
data;
Column input cell - starting amount from initial
input data
Interest rate, number of periods and starting
amount if you want to see how your savings
amount would differ under different scenarios
FINANCIAL
MODELING
WORLD CUP
Financial Modeling in Excel
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
data
2. Go to Insert -> PivotTable
and choose a table or a
range you want to analyze
3. Choose fields to
summarize the data by
4. Go to PivotTable Analyze
or Design tabs to
customize the Pivot Table
Choose the table from step 1
Look at the total sales and count of products
bought by product categories. Choose columns,
Rows, Filters, Values – everything is customizable
and you can play with the report!
Add a slicer, insert timeline, add subtotals and
other things
Store order history with customed ID, product ID,
product price and product category
FINANCIAL
MODELING
WORLD CUP
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Financial Modeling in Excel
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
to create a drop-down list in
2. Select Data -> Data
Validation
3. Choose what will the users
be able to choose (numbers,
dates, time, custom text, etc.)
Offer to choose from a list of store locations such
as
“USA, Spain, UK, Australia, Japan, Germany”
“Select Store Location”
Link profit and loss statements to geographical
location of the stores from the dropdown by using
“IF” statements
5. Link other data in your
model to this dropdown list,
so
that
values
update
automatically
4. Create Input Message so
that users know what they
are choosing
On your input data Excel sheet, create a cell
where users will be able to choose between
different store locations
FINANCIAL
MODELING
WORLD CUP
Financial Modeling in Excel
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
Go to Data -> Get Data
2. Transform Data
Do all kinds of changes to
your data while the original
dataset stays the same
3. Combine Data
Add other datasets and
make connections between
them to get more insights
Clean Data - remove unneeded columns, assign
data types, rename columns for better
understanding, etc.
Pull in another data source on the background of
the participants - country, company, age group,
etc. Append Queries.
Load the appended query into the Excel file. After
each stage, add information on the points and
refresh dataset.
4. Load Data
Load the transformed and
combined
data
to
your
worksheet and enjoy the
clean dataset
Pull in data from a different Excel file that
contains participant names and stage points
FINANCIAL
MODELING
WORLD CUP
Financial Modeling in Excel
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
group
2. Go to Data -> Group ->
Group
3. Group again, if you want
to go into more detail
4. Press “-” to collapse the
groups
Level 1 – for top level management, Level 3 or 4 –
for accountant in-depth data review
FINANCIAL
MODELING
WORLD CUP
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help