How about a fun Excel challenge? I have data in below format in the table named trips

I want to know which employees visited both USA & UK?

How would you solve this problem? Post your solutions in the comments.

Need sample data & my solution? Click here to download the file.

Want more challenges & home-work problems? See this page.

The post Who went to both USA & UK? [Excel Challenge] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Recently, I interviewed a few people for Power BI roles and here are some questions I asked in the initial rounds to assess their skill level. I’ve included sample answers below so you know how to approach such questions.

Prefer video? See this...

I made a video with 10 questions and my (elaborate) answers to them. Watch to learn how to answer such interview questions.  See it below or on my YouTube channel.

1. What are the main differences between Power BI Desktop and Power BI Service?

Answer cues:

• Power BI Desktop is the main tool for creating or authoring reports.
• We use PBI desktop to clean up data, create data model, set up measures, build and refine charts and construct the reports.
• We then use Power BI Service to share the reports with the audience.
• Desktop = creation tool, Service = consumption tool

2. Explain the difference between a measure and a calculated column?

• Measures:
  • calculate things on top of tables.
  • calculated in the run-time based on the evaluation context.
  • are usually aggregates.
  • example: Shipment Count = COUNTROWS(Shipments)
• Calculated columns:
  • are part of the table.
  • are calculated at the time of creation based on the row-context.
  • have one value per row.
  • example: Discount Rate Row-wise calculation of discount rate based on order quantity
• Both measures and calculated columns use the same DAX language.

3. Describe the steps you would take to connect multiple data sources in a Power BI report?

• We can use Power Query to connect more than one source of data.
• We can also use PQ to merge / append / combine the data as needed.
• We can selectively load the data to Power BI and keep the rest in PQ level but customizing the load behaviour.

4. What do 1, * and arrow () mean in the data model diagram?

• In a typical star-schema, we will have one to many relationship between dimension and fact table.
• The one side is denoted with a 1 and many side is denoted with a *
• The arrow indicates the direction of filter propagation.
• Power BI allows us to customize the filter direction (uni or bi-directional) and also have many-to-many relationships.

5. What is DAX and give me an example of DAX you've recently used?

• DAX stands for Data Analysis eXpressions. It is the main language of Power Pivot.
• Give examples based on your experiences and tell why / how they helped you solve problems.

6. How do you optimize the performance of a slow Power BI report?

• Answer questions like this based on your experience. If you have never optimized something, be honest and say that. Then give theoretical answers. 
• Key optimization startegies:
  • Reducing the data (filtering at PQ, Source level)
  • Removing unwanted visuals, interactions and bookmarks
  • Using performance profiler (Optimize ribbon in Power BI) to measure the performance of a page and identifying the problem areas.
  • Setting up aggregate tables, pre-calculated views.
  • [NEW] Using visual calculations instead of measures.
  • Upgrading the Power BI on-prem servers, database servers (for direct query)
  • Running time-consuming operations (such as PQ transformations) at low usage times (midnights, evenings)

7. How do you publish a Power BI Report to the service?

1. Save and test the report. Make sure all calculations are correct and visuals represent the truth.
2. Test any connections, refresh processes too.
3. Publish the report (Publish button) and set the correct workspace.
4. Test the report on the service view and make sure right people have access to the report.
5. Optional, Send an email or share the report with the audience.

8. Explain RLS (Row Level Security) in Power BI?

• Row Level Security allows us to provide access to the relevant data to right people.
• For example, we can use RLS to show only USA data to the USA regional manager.
• We can use “roles” option in Power BI to set and test the roles. 

9. What is a Slowly Changing Dimension (SCD) and how do you handle it in Power BI?

• SCD (Slowly Changing Dimension) is a dimension (or aspect of a dimension) that changes slowly over time.
• Give examples from your industry or previous work.
• Example: In our product table, we have a feature called cocoa percentage. For certain products this is changed once in a while based on customer feedback. This is an example of SCD.
• We can either replace the old values with new ones or create effective date based records. 

10. How would you handle missing data?

• We can use either Power Query or Power Pivot to handle missing values in our data. 
• Power Query shows missing values in a column thru Column Quality feature. We can use this to identify and deal with missing values.
• The key strategies for dealing with missing data are:
  • Removing missing values
  • Going back to source and fixing the problem
  • Replacing missing values with an approximation (imputation)
  • Ignoring missing values

Need more help? Watch the video

I made a video with 10 questions and my (elaborate) answers to them. Watch to learn how to answer such interview questions.  See it below or on my YouTube channel.

Learn more: Power BI Weekend (2023)

My annual Power BI event – Power BI Weekend is happening this year on November 18 & 19. In this 4 hour event (2 hours on Saturday – Nov 18 & 2 more on Sunday – Nov 19), you will learn the Power BI Essentials to say yes to your next challenge.

The topics covered are,

• What is Power BI? How to use it?
• Power BI vs Power Query vs. Power Pivot
• Understanding Power BI Visual interactions & customizing them
• Data cleanup and transformations with PQ
• Setting up a star schema data model
• Creating and using DAX measures
• Interactive storytelling in Power BI
• Saving & publishing your work
• Resources to learn more
• Q&A with you

Tickets are on sale now. Book yours before they sell-out.

Power BI Weekend - Click here

The post Top 10 Power BI Interview Questions & Answers appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

On the left side, we have a veteran warrior with 37 years of data battle scars and  redundant six pack.  They call him SQL.

On the right side, there is a young challenger with transformative powers and “never say undo” attitude. He goes by the moniker Power Query.

Who is going to win this battle?!?

I have been using SQL for 25 years and Power Query since it came out in early 2012. And in this article, let me share my views on how SQL compares with Power Query. If you prefer to listen, check out the podcast episode – SQL vs. Power Query. 

Listen: SQL vs. Power Query Podcast Episode

SQL vs. Power Query - The Comparison

SQL

Power Query

What can you do? 

All CRUD operations (Create, Read, Update, Delete)

Only Read the data

What kind of data?

Usually single source from a database or warehouse
(ex: SQL Server)

Can access data from anywhere and combine data from multiple sources too.

How do you use it?

You need to “WRITE” queries to use SQL. 

You “BUILD” Power Queries using the UI buttons and menu options.

Where can you use it?

Works almost universally. You can use SQL with most database systems and programming languages. 

Only with Microsoft stack of products, primarily with Power BI, Excel and Fabric.

Who can use it?

By default, you need permissions / special software to use SQL.

Almost anyone can use Power Query as it comes packaged with Excel and Power BI.

How fast is it?

Built for performance and scalability. You can use SQL to access data quite efficiently.

Can become slow and tedious as your data grows. 

Resources for Learning SQL

• SQL Basics
• 50 SQL examples for Excel folks

Resources for Learning Power Query

• What is Power Query and how to use it?
• Learn Power Query in 15 minutes
• My Power Query Essentials Course

What do you think?

Have you used both or either of these technologies? What do you think? Leave a comment with your thoughts.

The post SQL vs. Power Query – The Ultimate Comparison appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Do you know that we can easily calculate the critical path for a project using Excel formulas?

For a long time, it has been tricky to calculate the Critical Path using Excel formulas. But thanks to the arrival of new Dynamic Array functionality in Excel, we can now calculate critical path. In this article let me describe the approach with an example.

Put on your hardhats, this one is going to blow your minds.

What is Critical Path Method (CPM)?

Critical Path Method gives us a framework to analyze and optimize a project plan. Let’s say you have a project with 6 activities, as depicted below. Then we can find a critical path that determines the total duration of the project using Critical Path Method.

The critical path is the longest sequence of tasks that must be completed to execute a project. The tasks on the critical path are called critical activities because if they’re delayed, the whole project completion will be delayed.

ProjectManager.com

How do you calculate the Critical Path?

To calculate the critical path, you need below details about the project plan:

• Complete list of all planned activities
• Estimated duration for all activities (t)
• Dependencies for each activity 

In many real-world scenarios, accurately listing all three of them is impossible. And that is why CPM technique is often criticized.

Once you have all of them, we need to apply critical path algorithm to calculate below 5 values.

• Earliest Starting time (ES): This is maximum Earliest Finish (EF) of all predecessors of an activity. 
• Earliest Finish time (EF): This is ES + duration of the activity (t)
• Latest Finish time (LF): This is the minimum Latest Start time (LS) of all successors of an activity. 
• Latest Starting time (LS): This is LF – duration of the activity (t)
• Float or Slack (F): This is the gap between Latest Start (LS) and Earliest Start (ES). For activities on critical path this value would be 0.

 

Learn more about Critical Path Method:

• ABCs of Critical Path Method (hbr)
• Critical Path Method (ProjectManager)

Critical Path Calculations with an Example

Let’s go back to our 6 activity project. We can assign durations for each activity like this:

We can use below notation when capturing this data in Excel table.

Calculating ES, EF, LS, LF & Float with Formulas

Formulas need Excel 365! These formulas work only in Excel 365. × Dismiss this alert.

Adjacent to the input data table, add 6 columns for all our calculations. Our table should look like this:

Now, lets understand the formulas for Successors, Early Start (ES), Early Finish (EF), Latest Start (LS) and Latest Finish (LF).

Early Start Formula (ES)

Early Start is the earliest an activity can begin. An activity can only start when all of its predecessors have finished. So this is same as the maximum of Early Finish (EF) for all the predecessors. If an activity has no predecessors, then it can start right away.

As we have the list of predecessors in the cell [@Predecessors], we can loop thru them and find the maximum finish time for them.

Here is the formula I used.

				
					=IF([@Predecessors]="",0, MAX(CHOOSEROWS([EF],TEXTSPLIT([@Predecessors],",")+0)))
				
			

For activities without predecessors, we set the value of ES as 0.

For all other activities, we split the [@Predecessors] by comma (using TEXTSPLIT) and convert these text values to numbers (by adding a 0 to them). We then pick the maximum of all these activity’s earliest finish time [EF] using MAX & CHOOSEROWS functions.

Early Finish Formula (EF)

This is easy. We just add duration to early start (ES).

				
					=[@ES]+[@[Estimated Duration]]
				
			

Did you notice the circular nature of these formulas?

Even though ES formula depends on EF and EF formula depends on ES (head hurts, innit?), you need not worry. Excel will calculate both of these values fine as long as there are no loops  in your project data (ie. Activity 1 depends on 2 and 2 depends on 1)

Successors Formula

Before we calculate the Latest Start (LS) and Latest Finish (LF) times, it is a good idea to calculate the list of successors for each activity. 

I used this formula for that:

				
					=TEXTJOIN(",",TRUE,FILTER([ID],IFERROR(BYROW([Predecessors],LAMBDA(a, OR(TEXTSPLIT(a, ",")+0=[@ID]))),FALSE),""))
				
			

How it works?

For each activity, the list of successors is defined as all the activities that begin immediately after that activity.

So for example, going back to our image of project plan (see below),

the list of successors for activity 1 is {2,3}

To obtain this list for a given activity x:

• We need to filter all the activities
• where x is one of the predecessors

 We can use a cocktail of FILTER(), BYROW(), LAMBDA() and TEXTSPLIT() for this.

Here is the basic approach:

1. We filter the [ID] column
2. by checking for each row (hence BYROW)
3. if the [Predecessor]s has the [@ID]
4. To perform the check, we first split the predecessors using TEXTSPLIT
5. and then compare if any of them equal to [@ID]
6. At the end of this BYROW looping, we end up with either TRUE or FALSE values against each [ID]
7. After filter fetches all the successors, we just apply TEXTJOIN to combine them to a list. For ex: 2,3

 

Latest Finish Formula (LF)

Latest Finish (LF) is defined as the latest an activity can finish without derailing the project.

For the activities without any successors, this is same as EF.

For all other activities, we look for the minimum (earliest) LS value of all it’s successors.

Here is the formula:

				
					=IF([@Successors]="",[@EF],MIN(CHOOSEROWS([LS],TEXTSPLIT([@Successors],",")+0)))
				
			

How this formula works?

If an activity has no successors (ie it is last activity in the project diagram) we set LF as EF.

For all other activities, we split the [@Succssors] by comma (using TEXTSPLIT) and convert these text values to numbers (by adding a 0 to them). We then pick the minimum of all these activity’s Latest Start time [LS] using MIN & CHOOSEROWS functions.

Latest Start Formula (LS)

This is Latest Finish (LF) minus Duration (T)

				
					=[@LF]-[@[Estimated Duration]]
				
			

Float (or Slack)

Now that we have all the calculations done, we can figure out the float (or slack) for each activity. This is the difference between Latest Start (LS) and Earliest Start (ES) for an activity. 

				
					=[@LS]-[@ES]
				
			

Findout out which activities are on Critical Path

Any activity with ZERO (0) float is on the critical path. It means, there is no wiggle room for that activity. 

We can use Excel’s conditional formatting feature to visually identify all such activities.

1. Select the table and add a new conditional formatting rule (formula based)
2. Use the rule float_column=0 and set the necessary formatting. (see my rule in the below screenshot).

Here is my final project plan table with critical path activities identified.

Critical Path Analysis with Excel - FREE Template

Please download my FREE Critical Path Analysis Template and use your own data to calculate the critical path automatically.

 

Critical Path Calculations in Excel - Watch the Video

Still confused about these calculations? I made a video explaining the CPM concept & Excel formulas. Check it out below or on my YouTube Channel.

More on Project Management using Excel

If you are a project manager, you are going to love my site. I have articles & templates on all aspects of Project Management. Check them out below:

• Interactive Gantt Chart Template with Excel
• Quick & easy Gantt Chart in Excel
• Project Timeline Chart with Excel
• Project Management Dashboard with Excel
• Project Portfolio Dashboard with Excel
• Complete Project Management Template Pack (Excel)

The post Calculating Critical Path using Excel Formulas [Project Management] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Recently I had to create a Pivot report from monthly data. But there is a twist. The data is spread across multiple sheets, one for each month. Let me explain how I built the pivot for that scenario.

Step 1: Load Monthly Data File to Power Query

We can use Power Query and automatically combine all the individual sheets to one big table. For this, you need a monthly workbook that has one tab (sheet) per month. If you need sample data, check out this file.

• Create a “NEW” Excel workbook. We will use this file to combine all the monthly worksheets.
• Go to Data Ribbon and click on Get Data > From File > From Excel

• Select your monthly workbook with all the individual sheets
• In the navigator screen, select any one sheet and click on “Transform” to go to Power Query Editor.

Step 2: Combine & Clean-up Data

Once you are in Power Query Editor (PQE), we can quickly combine all monthly sheets and clean-up the data.

• Using the “Query Settings” panel on the right, delete all the steps except “Source” step. You can use the mark next to the step to delete the step.

• In the query view, expand the “Data” column so we can get all the monthly data in one big table.

But the data is not clean...!

When you “Expand” the data column, we get all the data, but a ton of problems too. Here is a summary of the key issues.

1. Incorrect headers
2. Repeated header rows (one per month)
3. Incorrect data types (dates, numbers treated as Alpha-numeric by Power Query)
4. Unwanted columns
5. Null values or blank rows
6. Something else?!?

Fortunately, Power Query can solve all these problems quite easily.

1. Set first row as headers using Home Ribbon > First Row as Headers option
2. To fix nulls & repeated header rows, use filter on a column like “Sales Person” and filter away the null & title words. See this demo:

 

3. Right click on the columns and use “Change Type” to set the correct data type.
4. Using Home ribbon > Choose Columns select the columns you want.

Step 3: Load the Data to Excel

Once all the clean-up is done, rename your query to something like “Combined data” and use the Close & Load button in Home ribbon to load this data to Excel.

Pro Tip

Use the "Load to" options to load the data just to your data model instead of Excel grid. This is helpful if you just want to make pivots and don't need to "see" the data.

Step 4: Create Pivot Table(s)

Now that all the data is in one place, you can create a pivot report easily in Excel. Just select the “combined data” table and use Insert > Pivot Table to add the reports you want.

Step 5: How to update when you have "NEW" data?

Come next month, you may have another sheet in the “monthly file”. Just save the file and open the consolidated workbook (with all your pivots) and use the “Data” ribbon > Refresh All button to refresh the queries. You may need to press this twice to update the pivots too.

VIDEO: How to create a pivot table from multiple sheets of data?

Check out this video tutorial to understand how to make a pivot table from multiple sheets of data. (see it on my channel)

Sample File

Download the sample data file (monthly data) to practice this concept on your computer.

What to do when you have an error?

While this trick works most of the time, you can still get some errors. Use below tips to debug / fix the problems.

• Save & Close the Monthly file: The refresh / update process may throw errors if your file is not closed. So, save and close the workbook.
• Monthly tabs should be consistently formatted: The headers and number of columns in your monthly file should match across tabs. If you have different headers or number of columns, then the combined file may have errors or incorrect values. 
• Missing Data: If you notice that some data (a month for ex.) is missing in your consolidated file or pivots, try refreshing the file a few times. If that doesn’t work, go to “Query” ribbon and “Edit” the query. Once you are in PQ Editor, backtrack and locate the step that might be causing the problem.
• Any other problem? Leave a comment with the issue you faced so I can offer some guidance.

Learn More

You can use Pivot Tables + Power Query to do so many awesome things in Excel. Check out below tutorials to master the concepts.

• Introduction to Pivot Tables in Excel
• I don’t use Pivot Tables anymore, I use this other thing instead…
• How to use Power Query (with 4 real-world examples)
• Combine data with Power Query when the headers don’t match

Complete Excel + Pivot Tables + Power Query course from Chandoo

If you want to learn all the necessary Excel functionalities and how to apply them for work in one place, check out my Excel School program. 

Trusted by more than 20,000 people around the world, Excel School is the #1 course to help you slay Excel for good. 

Click here to know more and secure your spot.

The post How to make a pivot table when you have data in multiple sheets [Tutorial] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Recently Microsoft announced Python support for Excel. This is a BIG news for everyone using Excel to analyze data or find insights. In this article, let me give you a proper introduction to the Python in Excel feature and how to use it. 

If you prefer video, check out my Excel for Python is here video.

What is Python for Excel feature?

You can now write Python code natively in Excel cells and return the output as either Python objects or Excel values. For example, you want to perform quick statistical analysis of your sales data in the range A1:D10. You can use the below Python code to do this now.

=XL(“A1:D10”, headers=True).describe()

How do I enable Python for Excel?

This “preview” feature is only available with Excel 365 beta users as of September 2023.

If you have Excel 365, you can go to File > Account to enable “insider” program. More details on eligibility and instructions are here – https://insider.microsoft365.com/en-us/join/windows

After you’ve joined the program, update your Office from File > Account page.

After the update, if you have Python for Excel, it will show up in the formula ribbon, as depicted below.

If you don’t have it yet, just wait a few weeks. It will show up eventually.

How to use Python in Excel:
A Quick Tutorial

Open Excel and load any of your data files. Alternatively, if you need sample data, copy paste the below table into Excel.

Sample Data (copy paste)

Sales Person	Product	Country	Date	Sales	Boxes
Gigi Bohling	Manuka Honey Choco	India	20-Jul-23	8162	742
Barr Faughny	White Choc	Canada	16-Aug-23	2485	355
Marney O’Breen	Peanut Butter Cubes	India	14-Jul-23	10255	733
Wilone O’Kielt	Mint Chip Choco	India	2-Jul-23	16800	800
Gunar Cockshoot	Orange Choco	New Zealand	2-Aug-23	2842	203
Andria Kimpton	Baker’s Choco Chips	Canada	18-Jul-23	9373	427
Beverie Moffet	Fruit & Nut Bars	India	14-Jul-23	6573	598
Mallorie Waber	Baker’s Choco Chips	India	24-Jul-23	3598	150
Barr Faughny	Spicy Special Slims	Canada	11-Jul-23	5138	571
Dennison Crosswaite	White Choc	Canada	22-Jul-23	1547	258
Ches Bonnell	99% Dark & Pure	New Zealand	16-Aug-23	12901	993
Andria Kimpton	Organic Choco Syrup	USA	16-Jul-23	7161	651
Gunar Cockshoot	Fruit & Nut Bars	New Zealand	19-Jul-23	11935	1492
Beverie Moffet	After Nines	India	18-Aug-23	5089	268
Gunar Cockshoot	Peanut Butter Cubes	USA	11-Jul-23	9247	578
Andria Kimpton	Peanut Butter Cubes	India	22-Jul-23	10731	826
Gigi Bohling	After Nines	Australia	4-Jul-23	9730	609
Gunar Cockshoot	Eclairs	USA	1-Aug-23	3150	287
Karlen McCaffrey	99% Dark & Pure	USA	6-Aug-23	2247	205
Roddy Speechley	Peanut Butter Cubes	USA	1-Jul-23	2765	213
Brien Boise	Caramel Stuffed Bars	India	3-Aug-23	7112	647
Wilone O’Kielt	Organic Choco Syrup	UK	27-Aug-23	3787	345
Dennison Crosswaite	Peanut Butter Cubes	Canada	29-Aug-23	2674	168
Gigi Bohling	White Choc	India	14-Aug-23	378	54
Karlen McCaffrey	Raspberry Choco	Australia	7-Jul-23	7217	401
Marney O’Breen	Spicy Special Slims	New Zealand	19-Aug-23	735	147
Mallorie Waber	Organic Choco Syrup	UK	3-Jul-23	4690	427
Karlen McCaffrey	Manuka Honey Choco	India	24-Jul-23	8008	572
Wilone O’Kielt	Spicy Special Slims	Australia	18-Jul-23	12586	2518

Sample Data (download)

Download the sample data file.

1. Once you have some data in Excel, press CTRL ALT SHIFT P to enable Python mode. If you get a “welcome to Python screen” complete the tour and then press the shortcut again.
2. Using your mouse or keyboard, select the data in your workbook. Excel should write the necessary XL() command to capture your data into Python as a dataframe.
3. To see the dataframe you just built, press CTRL Enter. Excel will display a “Python Object” in the cell.

DATAFRAME: a dataframe is a python concept for storing data. They are like Excel tables. Each column of dataframe has one kind of data.

To see the output as values
instead of Python object

You can see the “actual” values of your Python code anytime. Just select the cell with Python output and either press CTRL+ALT+SHIFT+M or right click on the cell and choose “Python Output” > Excel values option.

10 Python Coding Examples

Use these code samples to play with Python in Excel. Before starting.

• Copy the above table of sample data and paste it in Excel (in range A1:F30). Alternatively, download this file with the data.
• To type the code, enter python mode (CTRL ALT SHIFT P) or use the formula =PY( in a cell.

Example 0

Construct dataframe

df = xl("A1:F30", headers=True)

 

Explanation & Output

This will just create a dataframe named df and return that to the cell. You can either leave it or see the underlying data (which will be same as A1:F30) by changing the output style.

Example 1

Description of the data

df.describe()

 

Explanation & Output

This will generate a dataframe with statistical descriptions for all your number columns. Example output is shown below.

Example 2

Description of the data, all columns

df.describe(include="all")

 

Explanation & Output

This will generate a dataframe with statistical descriptions for all your columns. Perfect for situations when you have some text, dates and numbers in your data. Sample output shown below:

Example 3

Unique Product Names

df["Product"].unique()

 

Explanation & Output

This will generate a python array (ndarray) that has all the product names with duplicate values removed.

Example 4

Add “Sales per Box” calculated column to the dataframe

df["sales per box"]=df["Sales"]/df["Boxes"]

 

Explanation & Output

This will add a new column [“sales per box”] to the dataframe with the calculation logic: sales divided by boxes. You can use the same approach to add many other columns

Example 5

Add “Sales as percentage” calculated column to the dataframe

total_sales = sum(df.Sales)
df["Sales as a percentage"] = df["Sales"]/total_sales
df

 

Explanation & Output

First, we calculate the “total_sales” and keep it in a variable. Then we use that variable to calculate the sales as a percentage.

TIP: Do you notice the different ways in which you can refer dataframe columns? You can use dot notation (ex: df.Sales) or bracket notation (ex: df[“Sales”])

HOMEWORK PROBLEMS

Can you add below columns to the df dataframe?

• Sales value rounded to nearest thousand.
• Month number of the sales date
• Flag each record as “Canada” or “Non-Canada”

Example 6

Group Sales by Date and Show a Pivot

df.groupby(by="Date").sum()

Explanation & Output

This creates a default groupby (similar to pivot in Excel) of your data by showing totals by date. This will sum() all the number columns in your dataframe. See the below sample output.

Example 7

Group Sales by Date but only show Sales & Boxes columns

df.groupby("Date")[["Sales", "Boxes"]].sum() 

Explanation & Output

This creates a customized groupby with Sales & Boxes columns totals by Date. Use this pattern when you don’t want to summarize certain things (like Sales per box).

Example 8

Create a bar graph with Daily Sales

import matplotlib.pyplot as plt
plt.bar(df["Date"], df["Sales"])

 

Explanation & Output

We import the plotting library matplotlib.pyplot and use that to generate a bar graph with default settings.

Sample output is shown below:

 

Example 9

Create a bar graph with Daily Sales – another method

df_groups = df.groupby("Date")["Sales"].sum()
df_groups.plot(kind="bar")

 

Explanation & Output

This code uses the built-in plotting function of the pandas library to generate the bar graph. Notice how this doesn’t show missing dates.

Sample output is shown below:

Example 10

Filter the dataframe to show all records where the product has the word “Choc”

df[df["Product"].str.contains("Choc")] 

Explanation & Output

This code generates a new dataframe that contains all rows where the Product column has the word “Choc” in it.

MORE HOMEWORK PROBLEMS

• Can you filter all the records that have either “Choc” or “choc”?
• Create a bar graph of this data to show total sales by each product

How does Python in Excel work?

You need internet connection to run Python code in Excel. All the code you write is executed in Microsoft Cloud. This also means your data travels on the network to Microsoft Cloud and returns with the result.

What happens if your code has an error?

 

If there is an error in your Excel Python code, you will see a new error message #PYTHON! in Excel.

You will also see #BUSY! when Excel is running your Python code (in Microsoft Cloud).

In case of an error in your code, Excel automatically opens the Python Diagnostics tab and displays more information there.

Execution order of your code

The python code you write in Excel will run in row-major order. This means, the code runs row by row, left to right. See this illustration to understand the process.

Resources to Learn Python

Now that you are familiar with Python in Excel, you may want to learn more. May I suggest using the below approach.

1. See if you can enable use Python in Excel to get a feel of the technology.
2. Install a proper Python IDE like Anaconda, VS Code or something else to learn & practice Python properly.
3. Understand the Python programming concepts like variables, conditions, list comprehension, dataframes and EDA. Here is a good article on the process.
4. Apply these concepts on your own / business data to solidify your understanding.
5. If you need practice datasets, try Kaggle.

Python Videos

Python in Excel (video by Chandoo)

[NEW]

How to use Python as an Excel Person – FREE Masterclass + 3 Projects

[300k+ views, 1.5 hours long]

End to End data manipulation with Pandas – 10 Examples

[35k views, 18 mins]

Python Playlist on Chandoo

---

Python in Excel (video by Leila Gharani)

Python for Beginners (video by Mosh)

Python Books

• Python Crash Course 2nd Edition by Eric Matthes – https://amzn.to/3PBzYRK

This is the book we all (Jo, kids & I) read and really loved it. The explanations and examples are easy enough to get started. There is enough variety to please everyone. 

• Automate boring stuff with Python – https://amzn.to/3Py5T5w

More practical if you want to get things done with Python. I read it a few times and really like the practicality of the book.

• Python Data Science Handbook – https://amzn.to/3MFKOUK

Python is particularly useful for doing data science & building machine learning models. This is an area of focus for me in the next months. I suggest getting the Python Data Science book once you have strong foundation in the language.

Note: I am using affiliate link for these books. 

Microsoft Resources

As part of the Python for Excel launch, Microsoft also added many resources and example pages to their website. Check out these pages.

• Getting started with Python in Excel
• Introduction to Python – Microsoft Learn Course
• Using “dataframes” in Excel
• Power Query and Python in Excel
• Libraries supported by Excel Python

The post Python is in Excel! – Here is a complete getting started guide for you appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Power BI is one of the most prominent data analytics technology out there.

Power BI is also one of the most marketed and hyped technology out there.

Unfortunately, both of these statements are true.

In a world where data is the buzz word, Power BI (or other similar platforms like Tableau) appeal to CXOs as panacea for all their data troubles.

But, just as any technology, Power BI too has it’s own shortcomings. So in this episode of my podcast, let’s uncover 4 ugly truths of Power BI.

I want to preface this discussion with a huge note of thanks & gratitude for what Power BI offers. It is a beautiful technology with a few patches of ugliness.

Episode outline:

• ONE: Power BI is not one technology, even though it is marketed as one.
• TWO: Power BI changes frustratingly often. This makes it a hard technology to learn and use.
• THREE: Power BI can get prohibitively expensive as your organization & data needs evolve.
• FOUR: Power BI visuals / outputs are unimpressive.

Listen to this episode

Subscribe to the Show

Resources for this podcast episode

Learn more about Power BI:

• The truth about Power BI and how to learn it properly (video)
• Free vs. Pro vs. Premium Power BI – which one you should choose? (video)
• Introduction to Power BI and full tutorial (article)
• When & How to use various Power BI charts (guide)
• Power BI Beginner to PRO – Full Course (paid class)

The post CP03: The Ugly Truth About Power BI (actually, 4 of them) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Calendar (or date) table is crucial for performing date intelligence calculations in Power BI. 

Normally, you would find a reasonable calendar table in most data models. But occasionally I come across models where there is no calendar table. 

So I present to you, the ultimate & best way to generate calendar table using Power Query.

How to create the perfect calendar table with Power Query?

Start by creating a new “blank query” connection in Power BI.

Then use the =List.Dates() function to generate the dates you want.

For example, to get the calendar dates for year 2023 use below code:

				
					= List.Dates(#date(2023,1,1),365, #duration(1,0,0,0))
				
			

This will generate a list of all the dates in 2023. 

Now, convert the list to a table using the List Tools > Transform ribbon. 

Once you have the dates in a table format, you can use the “add column” ribbon and “Date” options to introduce many date related columns.

For example you can add:

• Year (4 digit year value)
• Month number
• Month name
• Weekday name
• Weekday number
• Start of month
• End of month
• Start of week
• Quarter of the year
• Days in a month

Additional "smart" date columns

Apart from all the columns above, I normally add these two columns to my calendar tables.

• Year month (a 6 digit representation like 202308 for ex.)
• Type of month (current month, previous month, next month)

Let’s look at the Power Query (M) code for these columns.

Year Month (yyyymm):

 

				
					= Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month])
				
			

Type of Month:

For this we need to do a few steps.

1. Calculate the current date’s Year Month (yyyymm) using the below M code:

 

				
					=Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow()) in Date.Year(cm) * 100 + Date.Month(cm))
				
			

2. Then subtract this value from each date’s year month
3. That number will be 0 for current month, -1 for previous month, 1 for next month etc.
4. Then use the “conditional column” to set month type accordingly.

Refer to the full M code script below for the entire calendar table in one go.

Full M Script for generating the calendar table

Use this M script to generate the calendar table for year 2023. 

To apply this, create a blank query in PQ and then go to View > Advanced Editor and paste the code there. Adjust the year in source step (step 1) to get the calendar for any year.

				
					let
    Source = List.Dates(#date(2023,1,1),365, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 0 then "Yes" else "No"),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Added Custom" = Table.AddColumn(#"Inserted Start of Month", "Year Month", each [Year] * 100 + [Month]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Current Month", each let cm = Date.StartOfMonth(DateTime.LocalNow())
in Date.Year(cm) * 100 + Date.Month(cm)),
    #"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Year Month] - [Current Month], type number),
    #"Added Conditional Column1" = Table.AddColumn(#"Inserted Subtraction", "Month Type", each if [Subtraction] = 0 then "This Month" else if [Subtraction] = 1 then "Next Month" else if [Subtraction] = -1 then "Previous Month" else "Other Month"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Current Month", "Subtraction"})
in
    #"Removed Columns"
				
			

Perfect Calendar with Power Query - Video

Check out this video to understand the process better.

More ways to make the calendar table

Check out below tutorials from other Power BI / Excel folks to see different Power Query scripts.

• All in one script for calendar table – Radacad
• Parameterized Date Tables – BI Gorilla

The post How to “auto” generate calendar tables with Power Query – The best method appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

We can calculate any Finance & Accounting KPI values using Excel easily. In this article, I  am sharing the top 10 accounting KPI calculations. These are…

Topics

Net Profit Margin

Net Profit Margin = Net Profit / Sales

Positive profit margin indicates business is profitable while negative indicates the business is in loss.

Revenue Growth

Revenue Growth = This year revenue / last year revenue – 1

Tells you how much revenue has grown compared to previous period. You can use the same logic to calculate COGS growth, profit growth too.

Quick Ratio

Quick Ratio = (Total Current Assets – Inventory) / Total Current Liabilities

Tells you how strong the liquidity of a business is.

QR of 1 or more indicates the business is able to pay off its current debts using the cash or cash equivalents it has on hand. 

QR under 1 indicates the business would struggle to pay off its current debts with what it has on hand.

Inventory Turnover

Inventory Turnover = COGS / Average Inventory

Inventory turnover measures how quickly a business is able to offload the inventory. 

The actual value of Inventory Turnover and how to interpret it changes from business to business. As a rule, a higher Inventory Turnover indicates more efficient inventory cycle.

Days Inventory Outstanding

Days Inventory Outstanding = Average Inventory / Cost of Goods Sold x 365

DIO or Days Inventory Outstanding measures how much inventory a business is carrying. A lower number indicates well oiled manufacturing & inventory cycles while a higher number indicates sluggish demand or oversupply. 

Please note that the typical values change from industry to industry and depend on other macro-economic factors.

For example, a vegetable business would have low DIO as the goods are naturally perishable, while a furniture business might have a high DIO as the goods do take long time to sell.

Avg. Revenue Per User (ARPU)

ARPU = Total Revenues / Total Users

ARPU or Avg. Revenue per User reflects the amount of revenue generated per user. This is a useful measure in service business (especially software as a service or SAAS or web).

Net Present Value

NPV = NPV(rate, cash flows)

Net present value indicates the current (or present) values of a series of cashflows that occur in future. We can use the NPV function in Excel to calculate this if the cashflows are uniformly spaced.

NPV is an extremely useful calculation to understand the financial soundness of a business decision.

Learn more about NPV & IRR functions in Excel.

Compound Interest

Compound Interest = Principal * (1+rate of interest) ^ number of years – Principal

Excel doesn’t have a compound interest function, but we can easily calculate the same using a simple formula. 

Learn more about compound interest & CAGR with Excel.

CAGR (Compounded Annual Growth Rate)

CAGR = RRI(duration, opening value, closing value)

CAGR measures the rate at which a business grew over a period of time. For example, if we say Apple grew at 10% CAGR since 2018 to 2023, it means, every year the sales of Apple went up by 10% compared to the previous year from 2018.

Excel doesn’t have a CAGR function but we can use the reverse IRR function – RRI to calculate the CAGR.

AAGR vs. CAGR:

AAGR or Average Annual Growth Rate simply measures the average of individual yearly growth rates (same as Revenue Growth we calculated in #2 above). 

Consider adding AAGR (Average Annual Growth Rate) to your analysis as sometimes CAGR is hard to explain to others.

Learn more about compound interest & CAGR with Excel.

Depreciation

Depreciation = the value of an asset lost during a period

Excel offers many functions to calculate depreciation of your assets or equipment. You can use below methods:

• Straight Line Method: This depreciates the asset value by the same amount every year until the end of its life. We can use SLN function for this.
• Declining Balance Method: This depreciates the asset value by same percentage every year. You can use DB function in Excel to calculate this.
• Double Declining Balance Method: This uses an accelerated DB method to depreciate assets faster in the initial years.
• Variable Depreciation Method: You can use a mix of either SLN or Declining methods depending on your needs using the VDB function in Excel.

Learn more about Depreciation Calculations in Excel.

Top 10 Accounting KPIs - Video

Learn more about all these calculations using the video below. You can also see it on my channel here.

Top 10 Accounting KPIs - Excel Workbook

Click here to download the sample file with all these calculations.

It has all the formulas & sample data for you to learn and practice.

Need more?

Let me know the in the comments if you are looking for any additional Accounting / Finance KPI calculations. I will include them in this page as time permits.

The post Top 10 Accounting KPIs and How to Calculate them in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

So you want a 6 figure data job? In this podcast episode, I am going to share 6 strategies and tactics to land a $100k+ data job.

Episode outline:

• My experience of working in a a 6 figure ($100k+) data job
• 6 strategies
  • Build wide data skillbase
  • Cultivate deep technical skills in 1-2 areas
  • Develop business & functional knowledge
  • Cultivate interpersonal skills & present yourself better
  • Strong Interviewing skills
  • Focus on L

Listen to this episode

Subscribe to the Show

Resources for this podcast episode

In the podcast, I talked about creating a digital wardrobe. My recommended webcam, mic & lights are below:

Recommended Webcams:

• Premium webcam: Elgato Facecam. I have been using Elgato Facecam for the last few months and just love it. It produces crisp, HD quality images & videos with almost no effort. Get it from Amazon.
• Budget webcam: If you want a good quality webcam that produces decent video and good light capture, try Logitech c920. This was my primary webcam for more than 4 years and never disappointed me. Get it from Amazon.

Recommended Mics:

• Premium mic: If your job involves a lot of talking, I suggest getting a good mic to enhance your voice quality. I use Blue Yeti Mics for my videos and livestreams and just LOVE them. Get it from Amazon.
• Budget mic: I use the Plantronics Blackwire USB headset on the go and love the voice clarity it produces. Get it from Amazon.

Recommended Lighting:

• The best lighting is natural light. So position your seat opposite to a window so that your face is getting natural light at angle. Try using sheer curtains to diffuse the strong direct sunlight.
• Alternatively get a ringlight if you work from a dark room or away from windows. I suggest this one on Amazon.

The post CP02: Six Tips to get a Six Figure ($100,000+) Data Job appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Ladies & gentlemen… I have an exciting announcement. I am relaunching my podcast!!!

It only took me 7 years, but Chandoo.org podcast is now BACK! I am planning to make regular episodes on Excel, Power BI, SQL & Data Analytics.

Why I am starting it again?

Over the last year, I have been thinking about about restarting the podcast. I listen to a lot of podcasts (Tim Ferris show, All-in, Tropical MBA, Office Ladies, YouTube Creators Hub to name a few). They educate, entertain and inspire me. I want to pass along some of those benefits to you. So here we go (again).

In this episode…

In the 1st episode of Season 2 of Chandoo.org podcast I talk about, Top 5 Excel Skills you need to be a Successful Data Analyst in 2023

What is in this session?

In this podcast,

• Welcome message
• Top 5 (Advanced) Excel Skills
  • Tables
  • Power Query
  • Dynamic Arrays & Spill Ranges
  • Pivot Tables & Data Model
  • Charting & Story-telling

Listen to this session

Resources for this podcast

Comprehensive guides on,

• Excel Tables
• Power Query – Deep introduction
• Power Query – Mini Course
• Dynamic Arrays & Spill Ranges
• Pivot Tables
• Data Model & Relationships
• Charting & Story-telling Examples
• Advanced Excel Skills
• Top 5 Excel Skills – Video
• Top 5 Data Skills – Video

The post Top 5 Excel Skills you need to be a Successful Data Analyst in 2023 (podcast) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Power BI has a LOT of chart types. More than 30 of them. So, picking the correct chart for a data analysis situation is hard. Here is a picture of a frustrated Power BI developer trying to figure out the right chart for her data analysis.

In this article, let me explain when & how to use these Power BI charts along with my top tips to make them.

The Power BI Visualization (Charting) Options

Here are the 30+ charting options in Power BI (as of July 2023). In this article, I am going to cover a majority of them.

We can categorize these charts into below groups.

• Bar / Column Charts
• Line & Area Charts
• Pie & Donut Charts
• Card Visuals
• Tables & Matrices
• Scatter (XY) plots 
• Maps
• Everything Else

Let’s understand when & how to use these charts now.

Bar & Column Charts in Power BI

When:  Use them to compare things

Examples

Default Title

Column Chart

With "legend"

With Levels

Small Multiples

Top Tips

• Use the “Legend” option to split the chart
• If you put more then one field in the “axis”, you can drilldown on the charts. 
• You can also split the chart into many using the “small multiple” option. See this video to learn more.
• When using “time” or “date” axis, try the column charts instead of bar charts. 

Line & Area Charts in Power BI

When:  Use them to see the trends

Examples

Line chart

Area chart

Stacked chart

Don't do this

Forecasting

Top Tips

• Convert line to “stepped” line chart by enabling the “stepped” option in formats.
• Enable markers to improve the line chart readability.
• When you have a line chart with date axis, you can use the “analytics” options to introduce forecast or trend lines.
• Don’t make line chart with “things” on axis. They are meaningless (most of the time).

Pie & Donut Charts

When:  To understand the full picture & contribution

Examples

Pie chart

Donut chart

Too many slices ?

Top Tips

• Always customize the labels and legend to get best look for your pies (or donuts)
• Avoid making too many slices. No one likes a tiny slice of pie.

Card Visuals

When:  To share single numbers, KPIs & messages

Examples

Card

Multi-row Card

New Card

Add Context

Top Tips

• Add context to your cards whenever possible. This is a powerful way to engage your audience. Read this article for more.
• Power BI recently introduced a “NEW Card” visual. Give it a try. It lets you build cards with more formatting options and it is faster than regular cards.

Table & Matrix Visuals in Power BI

When:  To show details

Examples

Table

Matrix

Formatted Table

Formatted Matrix

Top Tips

• Adjust row padding on tables & matrices to make them look easy on eyes.
• Add conditional formatting to one or two fields to elevate the data.
• Apply correct sort order on your tables. If you hold SHIFT while sorting the tables, you can sort on multiple columns!
• When you have lots of data, Filter your tables down to just important points.

Power BI Maps

When:  To explain geographical data

Examples

Map chart

Azure Map

3D Column

Top Tips

• Power BI has a lot of map visuals. Try them all to see which one gives you best fit for your data. My favorite is the new Azure Map Visual. Learn more about it here.
• While maps are useful, I find them clunky. So try tables or other charts from time to time.
• Do not overlay useless information like traffic on to maps. Keep them light so the focus can be on the data.

Scatter (XY) Plot in Power BI

When:  To explain 2 dimensions in one picture & to explore relationships

Examples

X Y Plot in Power BI

Animated X Y Plot

Top Tips

• XY plots are great for exploring relationships. But remember the golden rule – Correlation is not same as causation.
• If you have a Date dimension, use it on the “Play Axis” option to make a cool animated XY graph in Power BI.

Waterfall Chart

When:  To explain how things have changed from one point / place to another. 

Examples

Waterfall chart

Top Tips

• Use the “explain” feature of Power BI to auto-generate useful waterfall graphs for you.

Don't forget

Don’t forget title, sub-title, legend, labels & tool-tips. 

These can make or break a chart.

Examples

Default Title

Great Title & Highlight

Tooltips

Top Tips

• Don’t settle for default titles. Write a great title & subtitle on your charts to elevate them.
• Highlight important data points using conditional formatting feature. Here is a tutorial.
• Set up a tooltip page to further explain your data. Here is a tutorial on Power BI Tooltips.

Everything Else...?

Use with caution.

When in doubt, try them with your audience, get their feedback and proceed as needed.

Demo Workbook with all these charts

Here is a Power BI workbook with all these visuals. Check it out to learn a bit more. 

When & How to use Power BI Charts - Video

I made a short & useful video on all of the important chart types in Power BI. Check it out below for some extra tips, pointers and information on when & how to use Power BI visuals.

Watch it below or go to my channel.

Play Video about Power BI - Chart Selection - Handy Guide

More Resources on Power BI

• Course: I run an online Power BI course to make you awesome with it. Check out the program and sign up today to learn Power BI, the right way. 

 

Here are some articles & videos to help you master Power BI:

 

• Introduction to Power BI with an end to end example
• What is Power BI – 10 minute tutorial
• Create a sales dashboard in one hour using Power BI  -video
• Beginner to PRO Power BI Lesson
• End to End Power BI online class

The post When & how to use the 30+ chart types in Power BI? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Today, let’s learn how to make an impressive map chart with Excel. Something like this:

Step 1: Set up your Data for the Map Chart

You need geographical fields (district / county/ state or country), few numeric values to create the map chart in Excel. Here is the layout of the data I’ve used.

I will explain the logic for “Calc. Column” in Step 3.

Step 2: Create “selection mechanism” with Data Validation

Select a blank cell to set up “drop-down” selection mechanism. This is where we will select one of the products to show on the map.

Go to Data ribbon & click on “Data Validation”. Change the rule type to “List” and point to the list of products. You can set up this list elsewhere in the spreadsheet or directly point to the table headers.

Related: Learn more about setting up “drop-down lists in Excel”

Step 3: Calculate the values for “selected” product

I recommend using either XLOOKUP or INDEX MATCH to calculate the values of “selected” product for our table.

Here is the XLOOKUP formula I’ve used. This assumes the drop-down list selection is in cell G6.

=XLOOKUP($G$6,
Table2[[#Headers],[Mint Chip Choco]:[After Nines]],
Table2[@[Mint Chip Choco]:[After Nines]])

The above formula looks up the G6 (selection values) against table headers and returns the corresponding product sales values to the calculated column.

Step 4: Make the Map chart

Select the geographical fields & the calculated columns alone, then go to Insert Ribbon & click on the Filled Map chart option (see below).

Customize the map chart colors & title if you want.

Place chart next to the selection cell and you will have a dynamic map chart in Excel, like below:

Download the sample file

If you need a hand with this technique, check out the example workbook.

Video Tutorial – How to make map charts in Excel?

I made a video explaining this process with a few more examples & tips. Check out the video below or my YouTube channel.

Known issues & problems with Maps in Excel

Excel map charts are not perfect. Here are some known issues with it.

• Not all geographical data is supported: In many countries, you can’t show data by district or city or regions. For example, if you try to make the same chart with districts of an Indian state, the map will not work.
• No support for LAT / LONG or street addresses: You can’t create map charts with specific point information (like addresses or coordinates).
• Incorrect maps: When a geographical boundary is disputed (due to wars or whatever), the map chart doesn’t show correct outlines or altogether omits the regions.
• Unable to zoom: You can’t zoom in to a specific region with the map charts.

Possible Alternatives:

You can use Power BI or Tableau other data visualization tools to make the maps. They allow working with more granular data or can let you control the zoom for your maps.

Got a question?

Do you have a question or doubt about using map charts in Excel? Leave a comment so I can help.

The post Make an Impressive Interactive Map Chart in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Microsoft Excel is a great tool for creating business dashboards. Recently, I made the below dashboard using Excel in just 5 steps.

Step 1: Figure out what you need

The first step for any successful dashboard is to understand what information your users want and how they want it.

• Talk to your users and ask them “what and why” they need the information.
• Prepare a mock-up sketch of the dashboard and get the buy-in.
• Identify data sources and refresh options.

At the end of STEP 1, you should have a clear idea about where you data is, how it should be gathered, analyzed and presented.

Step 2: Clean & Set up data

If you look long and hard, you can find the Loch Ness Monster, the truth about Bermuda Triangle before you locate the Perfectly Clean Business Data.

Chandoo Tweet

The next step is to clean & structure your data in a tabular format.

• Use Power Query to connect to the data source(s) and fetch the data.
• Set up necessary data cleaning processes with in Power Query.
• Load the data to Excel either as tables or to the data model.

At the end of this step, you should have one or more tables of data in Excel.

Step 3: Calculate the KPIs with Pivots

Load the cleaned data into “data model” based pivot tables in Excel. Now, using Power Pivot DAX measures, calculate the necessary KPIs.

For example, for the above business dashboard, I’ve calculated:

• Total Sales =SUM(sales[Sales])
• Total Boxes = SUM(sales[Boxes])
• Total Expenses = SUM(sales[Expenses])
• Total Profit = [Total Sales] – [Total Profit]
• Profit % = [Total Profit] / [Total Sales]

Create the pivot tables using these measures.

Related: Learn more about Power Pivot & DAX in Excel.

Step 4: Make Interactive Charts

First create the necessary pivot charts (refer to the mock-up you made in step 1). 

Then, make them interactive using Slicers, Timelines or Report Filters.

Learn how to create a slicer here.

Step 5: Bring Everything Together & Format

We are ready for the big finale!

Bring all the charts, KPIs and slicers you have created so far to a new sheet. (Cut & Paste).

Arrange them neatly and format as needed.

Viola, your dashboard is ready!

Video Tutorial - How to create a dynamic business dashboard

I’ve created a 30 minute tutorial on how to create the above dashboard with all the necessary steps. Watch it below or on my Youtube channel.

Sample Workbooks

Please download the blank template file from here.

If you want the full dashboard (with al the workings), you can find it in my Excel School Online Course.

More Dashboard Tutorials & Examples

I have a confession to make. Dashboards are kind of my thing. So I have been writing about them for almost 15 years now. Here are some of my best Excel & Power BI dashboards.

Designing a dashboard to track Employee vacations [case study]

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

D’oh – Visualizing Homer’s favorite sayings in Power BI

How to create a fully interactive Project Dashboard with Excel – Tutorial

Easy Website Metrics Dashboard with Excel

Employee Turnover Dashboard – Power BI for HR

The post How to Create a Dynamic Excel Dashboard in Just 5 Steps appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Excel formulas acting slow? Today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.

10 Tips to Optimize & Speed up Excel Formulas

1. Use tables to hold the data

Starting Excel 2007 you can keep all the related data in a table. For example call center data in our recent dashboard is kept in a table. Tables can be used in formulas with structural references, can be used as a source for pivot tables etc. And since tables grow & shrink as you add / remove data, none of your formulas need to be dynamic. As an example, if you have table called cs, then the formula sum(cs[column_name]) refers to sum of all values in the column_name of table cs. Even if you add more data to CS, the formula still works.

Resources to learn about Excel Tables:

• Introduction to Excel Tables – what are they and how to use them?
• Example: Customer Service Dashboard – Data & Calculations

2. Use named ranges, named formulas

By using names and named formulas, you can simplify your spreadsheet. Not only that, since named ranges & named formulas can hold arrays (ie lists of values), you can hold intermediate results or values that you need to refer many times in these named formulas. This will reduce the formula overhead and makes your workbooks faster.

Resources to learn about named ranges & named formulas:

• Excel School Program: In this comprehensive course, I talk about how to think about and write better formulas for data analysis work.
• Musings on Live Calendar [Excel Hero]
• Examples of Named Formulas – 2023 Calendar in Excel

3. Use Dynamic Arrays & Spill Ranges

Introduced in Excel 365, Dynamic Arrays allow us to build complex calculations with ease. I suggest incorporating new functions like:

• FILTER to fetch a list of values that meet one or more criteria.
• SORT to sort the values
• UNIQUE to eliminate duplicate values on the fly
• XLOOKUP to perform various lookups
• VSTACK / HSTACK to combine datasets
• TOCOL / TOROW to convert tables of data to single row or column formats
• # or Spill operator to manage spill ranges

Learn more about Dynamic Array functions here:

• Dynamic Array Functions – A deep introduction
• Dynamic Array Functions – how to use them [Video]
• How to use XLOOKUP

4. Use Pivot Tables

Many times, even when we do not need formulas we use them, because we can. Pivot tables are an excellent way to calculate a lot of summary values with few clicks. Once the pivot is built, you can refer to the pivot values with GETPIVOTDATA or simple cell references. This will reduce a lot of unnecessary calculations. If you are changing the data, you can just go to DATA ribbon and refresh all pivots in one go. This process works smoothly when you use tables to hold the data.

One of the reasons for slow workbooks is lot of data. Since, pivot tables are designed to work with lots of data, by using them, you can speed up your workbooks.

Resources to learn Pivot Tables:

• Introduction to Excel Pivot Tables
• Examples, Details & Downloads on Excel Pivot Tables

5. Sort your data

One of the reasons for sluggish performance is that you are searching for something in a lot of un-sorted data. You are making Excel look for a needle in a hay-stack. Many times we inherit un-sorted data thru data imports. By sorting the data & using correct operators in lookup formulas, we can instantly speedup a sluggish workbook. If you feel that sorting the data is a pain, you can even automate it with Power Query or a sort procedure (thru a simple VBA macro).

Examples on Sorting:

• Remove duplicates & sort a list using Pivot Tables
• Use Power Query to pre-sort the data you are working with

6. Use Manual Calculation Mode

Speed is the hefty price you pay for complexity. But many times, we want our Excel workbooks to be complex, because only then they would reflect real world. In such cases, you can set formula calculations to manual mode.

Just press F9 whenever you want to run the formulas. Please note that Excel runs formulas whenever you save the file too.

7. Use Non-volatile formulas

There are a class of formulas in excel called as volatile formulas. These formulas are re-calculated whenever there is a change in the workbook. Examples of volatile formulas are RAND, NOW, TODAY, OFFSET etc. So when your worksheet has a lot of volatile formulas, any time you make a change all these formulas must be re-calculated. Thus, your worksheet becomes slow.

Solution? Simple, do not use volatile formulas. For example, instead of using OFFSET to construct a dynamic range, you can use INDEX. Since INDEX is non-volatile, it tends to be faster. Or better still, use a table.

Resources to learn more:

• Excel INDEX function
• Volatile functions in Excel

8. Keep formulas in a separate sheet

Formulas are the driving force behind any Excel workbook or model. By keeping all them in a separate worksheet(s), you minimize the chance of mistakes, omissions or repetitions.  Debugging or investigating slow performance becomes an easy task when all formulas are in same place. I usually keep all the formulas in one sheet whenever I am designing a dashboard or complex workbook. This structure also helps me in thinking thru various calculations and planning the formulas in a structured way.

9. Write better formulas

Here are some guidelines that I follow when writing formulas.

• Built-in formulas tend to better than your own version – for example SUMIFS is easier to write and just as fast as SUMPRODUCT.
• Do not refer to entire column when you need just a few values. Do not write SUM(A:A), when you know values are only in A1:A10.
• IFERROR instead of lengthy IF(ISERROR formulas. Use IFERROR to simplify your error checking.
• Remove or Fix formula errors [how to, findout why formulas are not working]
• Use newer Dynamic Array formulas instead of old clunky array formulas
• Remove or Reduce references to other workbooks. Use Power Query instead.
• Remove any named ranges that result in error or missing links.
• Try to come up with alternative formulas: this not only sharpens your mind, but lets you discover better solutions.
• Do not calculate something if you do not need it.
• Do not calculate same thing twice. Use the first result second time too. Use LET for accomplishing this.

Resources to write better formulas:

• Introduction to SUMIFS formula
• Introduction to XLOOKUP formula
• How to use the new Dynamic Array functions in Excel
• Introduction to SUMPRODUCT formula
• Introduction to IFERROR formula
• Excel Formula Forensics
• Excel School program

10. Desperate times need desperate measures

Sometimes, no matter what you do, the workbook remains slow. Here are a few whacky ideas that I try in such cases:

• Replace formulas with values. I take a backup of the formulas. Then I select everything, CTRL+C, ALT+ESV (or CTRL Shift V). Done!
• Develop the workbook from scratch: Sometimes it helps to design the workbook afresh.
• Replace external data links with actual data: And import data by copy-pasting if needed.
• Reduce the functionality: See if the end user can live with fewer features in the workbook.
• Find an alternative solution: Trying to do everything in Excel is foolish. See if there is any external tool that can do this better & faster.

BONUS: Learn new formulas & play with them

Optimization is not a one-shot exercise. It is an ongoing-business. So you need to constantly learn new formulas, new uses & play with them. This way, you see new ways to improve a sluggish workbook. To begin with, explore our Formula homework & formula forensics pages and see how you solve these problems.

How do you speed-up your Excel formulas?

So how do you optimize & speed-up your Excel formulas? What techniques do you use? Please share using comments.

The post Speed up your Excel Formulas [10 Practical Tips] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Recently I optimized a pretty long nested IF formula using a simple but elegant trick. I made it 80% shorter! In this article, let me explain the process and share the formulas.

The Situation: Discount Calculation

Imagine you have to calculate the appropriate discount using below rules.

And you have transaction data like below:

How would we write the formula in column H?

Option 1: Long, Nested IFs

We can calculate the discount by writing a complex, lengthy and nested IF formula.

Here is one such formula:

				
					=LET(cat, XLOOKUP(F5,products[Name], products[Category]),
        IFS(OR(D5="India", D5="USA",D5="UK"),
            IFS(AND(G5>=200, G5<=999), 
                IF(OR(cat="Bars", cat="bites"),
                        IF(E5="New",Rules!$F$7, Rules!$F$8),
                    0),
                AND(G5>=1000, G5<=1999), 
                    IFS(cat="Bars", 
                            IF(E5="New",Rules!$F$11, Rules!$F$12), 
                        cat="Bites", Rules!$F$14,TRUE, 0),
                G5>=2000, 
                    IF(E5="New", Rules!$F$16,
                        IFS(
                            cat="Bars",Rules!$F$17, 
                            cat="Bites", Rules!$F$18, 
                            TRUE, 0)
                    )
                )
            )
        )
				
			

What this formula is doing?

1. We start by calculating the “category” of the product using XLOOKUP and storing it in the variable cat
2. Then we check the rules (refer to picture 1 above)
3. Once we reach the lowest level of the rule, we get the matching discount from the rules worksheet cells.
4. If no discount applies, we return 0

What is wrong with this formula?

Long and error prone

Such formulas are too long to write correctly and errors are not easy to catch.

Not easy to update

When the business rules change, the formulas become hard to edit.

Hard to maintain

The formulas become a nightmare to maintain and document.

Option 2: A smart alternative

What if we can rewrite the rules so that we can write shorter formulas?

I suggest rewriting such business rules as a table like this:

Once you have such a table, we can then rewrite our formula using SUMIFS & wildcard characters. Like below:

				
					=LET(cat, "*"&XLOOKUP(F5,products[Name],products[Category])&"*",
        SUMIFS( discount[Discount],
                discount[Country],"*"&D5&"*",
                discount[Category],cat, discount[Customer Type],"*"&E5&"*",
                discount[Quantity from],"<="&G5,
                discount[Quantity to],">="&G5))
				
			

How does this formula work?

1. We start by calculating the “category” of the product using XLOOKUP, pad this with * and store it in the variable cat
2. Then we use SUMIFS to add up [Discount] column by 
   1. Checking the country (in D5) against [Country] column of discount table
   2. cat against [Category] column
   3. Customer type (E5) with [customer type] column
   4. Quantity (G5) with the to & from ranges
3. If there are no discounts then the SUMIFS would be 0
4. Else it would tell us what the discount is.

Advantages of the SUMIFS approach

Easy to write & test

The formula is easier to write and test. Hence fewer errors.

Easy to maintain & update

Whenever the business rules change or new products are added, updating the discount table is all you need to do.

Scalable

Even when you have 100s of rules, this table approach is easy to scale and won't increase the formula size.

Use FILTER to work with text

If you need to get a non-numeric value as the output, we can use FILTER() instead of SUMIFS.

Video Tutorial - Honey, I shrunk the formulas

I made a video about this concept. See it below or click here to watch it on my YouTube channel.

Try it yourself - Here is a sample file

Want to have a play with the data and see which formula is easier to write?

Click here to download the sample file.

Your thoughts on this approach?

What do you think about this approach? Leave a comment. 

Also, do check out below pages for more on other ways to make advanced Excel formulas.

• Introduction to SUMIFS
• Introduction + examples on XLOOKUP
• Use LAMBDA to create new functions
• New Dynamic Array functions in Excel
• Excel Formula Forensics

The post A clever technique to simplify your long, nested IF formulas appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

In this hands-on tutorial learn how to create a simple, elegant and functional to-do list to track your projects and tasks.

Download FREE To-Do List Excel Template

Don’t want to make your formulas and formatting? You can download my ready-to-use To-Do List file and change the data as per your project.

Click here to download the template.

Step 1: Set up your To-do data tables

We need two tables of data to begin with.

Table1: this is our to-do activity data table. It should have activity, optional due date and status columns at the minimum.

 

Table 2: This table lists all the possible status options. You can load them with below values (or come up with your own statuses).

Step 2: Link Status options to the todo Table

Once we link the “status” and “todo” tables, we can easily update the todo status, as demoed below:

To do this (excuse the pun),

1. Select the “todo status” column in our second table
2. Use “Formula” ribbon > Define Name button (ALT M M), and create a name for this column. I named mine status_options
3. Go back to the “Todo” table and select the status column.
4. Go to Data ribbon > Data Validation and set up a List validation with source as status_options

Now our status column is linked with the options we have defined.

Step 3: Create the CANVAS for visual To-Do List

Let’s setup the canvas for our todo list. Add a blank sheet and,

1. Make three columns wide enough to show the todo activities
2. Add a title and format it nicely. I used the “shapes” in insert ribbon for this.
3. Add a footer and do the same.
4. Add background colors and tidy up as needed.

Here is a high-speed show-reel of my formatting setup.

Step 4: Formula Time

Time for some number crunching. We need to use two formulas to get the ongoing activity list and structure it for our output page.

Add another sheet for the calculations and set up below two formulas.

The formulas

Our first formula (set up in cell B3 in the calculation worksheet) fetches all the ongoing activities and sorts the data using FILTER & SORT functions.

				
					=SORT(FILTER(todo[[Activity]:[Due-date?]],(todo[Status]="Ongoing")+(todo[Status]="")),2,1)
				
			

The second formula rearranges this data in to a single column using the TOCOL() function.

				
					=TOCOLO(B3#)
				
			

LAST STEP: Bring everything together to make the To-Do List

Go the canvas todo list we created in step 3 and get first 12 rows of our TOCOL() output for first column, next 12 rows for the middle column and the next 12 rows for the last column.

Tidy up and format as needed.

Tadaaaa, our To-Do List is ready!

Video Tutorial - How to make a To-Do List in Excel?

If you need more help with these instructions, check out the video tutorial I made below or on my YouTube Channel.

Download FREE To-Do List Excel Template

Don’t want to make your formulas and formatting? You can download my ready-to-use To-Do List file and change the data as per your project.

Click here to download the template.

Ready to use Project Management Templates

Create Gantt charts, Project Dashboards, Burn Down Charts and Risk Trackers with your data in just minutes.

Get my ready to use Excel Project Management Bundle from here.

The post Create a beautiful, elegant & interactive to-do list with Excel (FREE Template + Tutorial) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Do you have an Excel report or graphs that need to be emailed to various people every month? We can use Excel automation features to do this task quite easily.

 

The inspiration – A client request for Excel Email Solution

The idea for this came from a recent project I did for a client. They wanted me to build an Excel workbook which shows latest sales summary and then allows them to email the snapshot to the relevant people in one click.

Here is a snapshot of the solution I created for them (with dummy data):

1. You select a product and see the dynamic report
2. Pick the person who will receive the report (from drop-down list)
3. Click on the “Send email” button to send the email

The ingredients – What we need

Here, I am using 3 main ingredients.

• A report created in Excel 365
• Office Scripts to generate the email contents and trigger the mail process
• Power Automate flow to send the email

Below is a schematic of the whole process.

The Recipe – How to send emails from Excel

The actual recipe is a bit detailed and harder to explain in text only format. So I made a video with the whole thing. Watch it here or on my YouTube channel. I have included the key steps as text below too.

 

https://youtu.be/eeCqaokSSoQ

Scripts & Instructions:

We can send either text or images as the email. In our case, I have both text content and images. The images come from a grouped object named Group 5.

GenerateReport Script:

Go to your “Automate” ribbon in Excel and click on “New Script” button.

In the script window, paste below script and customize the names as needed (refer to the video for explanation on the script).

 

				
					function main(workbook: ExcelScript.Workbook):myOutput {
    // Your code here
    let ws = workbook.getWorksheet("Report");

    let repGroup = ws.getShape("Group 5");

    const repImage = repGroup.getImageAsBase64(ExcelScript.PictureFormat.png);
    const emailSubject = ws.getRange("c2").getText();
    const sendTo = ws.getRange("I22").getText();

    console.log(emailSubject);
    console.log(sendTo);

    return {repImage,emailSubject,sendTo};

}

interface myOutput {
  repImage: string;
  emailSubject: string;
  sendTo: string;
}
				
			

Set up the Power Automate Flow

1. Go to Power Automate website and login with your credentials
2. Create a new instant cloud flow
3. Set the trigger as “When HTTP request is received”
4. Add “Run Script” step in Excel
5. Add Send an email (v2) step
6. Set up the flow as depicted below.

Obtain the Trigger URL

1. Save your flow
2. Now go back the “trigger” step (step 1 of your flow)
3. Make sure you set the method to GET
4. Expand and copy the URL.

Back to Excel to make one more Script

We are nearly done. We just need to add one more script & a button in our sales report so that we can initiate the flow from Excel.

Add one more script in Excel and use the below code.

				
					function main(workbook: ExcelScript.Workbook) {
    // Your code here
    const triggerURL = "___YOUR TRIGGER URL___";


    let request = new XMLHttpRequest();
    
    request.open("GET", triggerURL, false);
    request.send(null);
}
				
			

• Save your script. 
• From “Code Editor” click on the options menu for your script and use the “Add Button” to add a button on Excel worksheet.
• Whenever you click on this button, your flow will start.

Other ways to Automate this:

We can also use VBA to create & send emails automatically. I have previously written about that approach too. Read this article for VBA Excel Email Sender.

 

VBA vs. Office Script approach – which is better?

Both technologies offer automation. I have summarized the pros & cons of each technology below. 

As of 2023 March, my preference is to use VBA for things like Email automation as it is easy to control and deploy. 

VBA

VBA Pros:

• Works in any version of Excel
• Easy to learn, easy to code
• Lots of help & resources
• Very old and stable language base
• Works with Excel, Office & Windows Objects & API

VBA Cons:

• Can't use with Web / Mobile version of Excel
• Not easy to integrate with Cloud platforms (Power Automate, Sharepoint etc.)
• Security problems

Office Script

Office Script Pros:

• Works on Web / Mobile versions too
• Integrates with cloud platforms (Power Automate etc.)
• Future ready technology

Office Script Cons:

• No easy help or resources
• Hard language to learn and master
• Doesn't work in older versions of Excel
• Can't use all objects of Excel. Will not work with Windows API etc. too
• Needs costly subscription plans to use
• Runs on server, thus no control and susceptible to downtimes etc.

Thanks to Mark Proctor

I got the idea for URL trigger from Mark Proctor. Thanks Mark for the fantastic work

 

Got questions?

Do you have any questions reg. this implementation. Post a comment so that our community can help you.

More automation with Excel

• Send Emails from Excel with VBA
• Create Power Point presentations from Excel
• Create Word reports from Excel data
• More VBA articles & tutorials

The post Send Email from Excel with Office Scripts & Power Automate appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Gone are the days when Excel is all about manual intelligence. Now a days, we can summon the powers of artificial intelligence with a click of a button and watch the magic on your spreadsheets.

So in this post, let me summarize some of the key AI features of Excel.

Pattern Recognition with Flash Fill

Need to fill-up some data but follow a smart pattern? May be you have a bunch of email addresses and want to extract the domain names or you have a list of expenses and want to extract the amounts? You can use Flashfill.

Summon it with CTRL+E or just type some data and watch Excel fill up the rest.

See this demo, or read this for more.

Automatic Formulas (Excel)

Want to write a formula but not sure which one would do the work for you? You can just type the data and Excel can figure out the formula logic for you.

Currently available in only Excel online, this feature writes formulas for you. It is called formula by example.

See this demo or read the page for more.

Automatic Formulas (Power Query)

If you need to create a new column of data derived from existing columns, you can use Power Query’s “Column from Examples” option. This is excellent for creating new data without figuring out the M code or PQ steps.

I use it all the time for:

• Creating initials from names
• Extracting parts of the text

See this demo to understand more or read this page to learn more about Power Query.

Automatic AI Analysis

Ever feel the urge to say, “I don’t know how to analyze this data, just do it yourself”. Well, you can now yell that at Excel and it will oblige.

Use the “Analyze data” button in Excel 365 (home ribbon) and let Excel do automatic analysis of your data. It can surprise you

See this demo to understand more.

Recommendations

I am sure if Shakespeare is alive today, he would proclaim that.

All jokes aside, if you are not sure which chart to make or what pivot table to create from your data, you can just use the “Recommend” feature of Excel.

To create a recommended chart:

1. Select your data
2. Go to Insert ribbon and click on “Recommended charts” option
3. Pick one of the recommended choices.

To create a recommended pivot:

1. Select your data
2. Go to Insert ribbon and click on “Recommended Pivots” option.
3. Pick from one of the suggested pivots and insert it.

See this demo or read this article to learn how to pick the right charts yourself.

Forecasting

Need to whip up a forecast from your sales data? Not sure how to do it?

You can ask Excel to study your data and create the forecast for you. Just select your data, go to data ribbon and click on the “Forecast sheet” option.

Boom!

See this demo or learn more about forecasting in Excel.

AI Features of Excel - Video

I made a video about these AI features with many tips & clear explanation. Watch it below or on my Youtube channel.

What is your favorite AI feature of Excel?

Your turn to go.

Tell me what AI features in Excel you like the most? Did I miss something? Let me know thru comments.

The post Top AI features in Excel – a round up appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Learn Advanced Excel in 3 Months

Roadmap with Resources

• Chandoo
• January 30, 2023

Who is this roadmap for?

This roadmap is for anyone interested in learning Advanced Excel Skills for Data Analysis or Reporting work. Follow this roadmap if you:

• Want to learn "Advanced Excel"
• Can allocate 4-8 hours every week for 12 weeks
• Have basic understanding of Excel
• Are motivated and driven

Month # 1

Focus on getting & cleaning data

This month focus on how to get, clean & manage data within Excel. The key learning objectives will be how to use Power Query, Tables, Data management features and how to perform simple "Exploratory Data Analysis" with Excel.

Technical Skills

• How to get in and use Excel?
• How to use Excel tables?
• How to use Power Query to load and clean data?
• Working with Flashfill, text to columns and other clean-up functions in Excel (video)
• How to filter & sort data in Excel?
• Doing simple descriptive analysis of data with in Excel
• How to use Excel References (video)

Course

If you need step-by-step course with clear examples, sample files and downloadable materials to teach you advanced Excel & dashboard reporting, please check out my Excel School program.

This comprehensive online class will teach you everything mentioned in this roadmap + more.

 

Click here to know more.

Domain Skills

In Month 1, learn about your industry / domain by:

• Talking to your friends who work in the same domain
• Visiting company websites and understanding their business model and nature of work.
• Read books on Statistics and data analysis.

Project

In Month 1, You can do a simple project to implement all the skills you have learned.

1. Build an Expense Tracker in Excel that loads data from your bank statements (in PDF or CSV or Excel formats), cleans the data and tells you where your money is going.
2. Run a survey about anything on your friends and analyze the data. Use tools like Microsoft Forms or Google Forms to automate the data collection and Power Query to consolidate the data.
3. Download any sports / movies or other favorite topic data and cleanse & analyze it. See if you can find anything odd in the data. Use websites like kaggle to get the data.

Recommended book: Data Analysis & Business Modeling by Wayne Winston

Month # 2

Focus on analyzing the data

This month learn how to analyze the data by asking right questions, using formulas + pivot tables to answer them. Learn how to highlight important points and information with conditional formatting and how to make interactive analysis with slicers & data validation.

Technical Skills

• Formulas:
  • Top 10 Excel Formulas for Data Analysis
  • SUMIFS
  • XLOOKUP
  • FILTER
  • SUBTOTAL
  • IFERROR
• Pivot tables:
  • Introduction to Pivot tables (video)
  • Advanced Pivot Table tips & tricks
  • How to use Data Model & Relationship feature of Excel
  • Introduction to Power Pivot
  • More on Pivot Tables
• Concepts:
  • Average vs. Median vs. Quartiles
  • Data & BI Terminology

Domain Skills

In Month 2, learn about your industry / domain by:

• Studying necessary statistical and functional areas of your work.
• Talk to your friends in the industry. Take them out to coffee and ask them what they do, what kind of analysis they often do and how they do it?
• Find volunteer / freelance / extra work opportunities to practice what you are learning. 
• Next time you meet your friends, discuss / analyze your favorite sports team / tv show / politics / weather / video game. 
• Read books or watch videos about your domain. 

Project

In Month 2, You can do a simple project to implement all the skills you have learned.

1. Sales Report from awesome chocolates dataset. Use this dataset and generate a sales report to show how an individual salesperson has done in 2021. Show their sales & boxes by product. Make it interactive so we can select another person easily. Build this with formulas alone and then with Pivot + formulas.
2. Build a financial goal tracker. You can input any number of goals and their cost (in today’s terms) and the tracker tells you how much you should save to have the money in future (with inflation, various return rates).

Month # 3

Focus on presenting the data

This month build skills to present your data. Understand how various charts work, how to create and customize them, how to make them interactive and how to lay them out. Learn when to "not" use charts. Build verbal and written communication skills to share your insights with others.

Technical Skills

• Charting:
  • Select right chart type for your data
  • Rules for making column charts in Excel (video)
  • How to make a budget vs. actual chart in Excel
  • Create a basic interactive chart in Excel
  • How to use slicers in Excel?
• Dashboards
  • What is a dashboard and how to create one?
  • KPI Dashboard in Excel
  • Project Management Dashboard with Excel
  • 200+ Dashboards – Examples, templates
• Concepts:
  • How to use Data Validation in Excel
  • Essential Excel Keyboard Shortcuts
  • How to use VBA
  • How to use Power BI

Additional Resources:

1. Excel Basics page on this website
2. Advanced Excel Skills page on this website
3. Learn statistics & probability using Excel
4. How to build a simple business model in Excel

Domain Skills

In Month 3, learn about your industry / domain by:

• Thinking about how the technical concepts relate to domain concepts you are learning.
• Imagine work scenarios and see what tech, domain skills are needed to solve them. (For ex, you want to know why shipments are delayed in first quarter of the year, what domain knowledge is needed and what tech skills are needed to figure the reason?)
• Think about the human side of analysis. How people read & consume information etc.
• Learn more about your area of work and what will make you successful.

Recommended book: Storytelling with data by Cole N Knaflic

Project

In Month 3, here are a few projects you can try:

1. Build a one page dashboard for Awesome Chocolate CEO with Excel Use this dataset
2. Build a system to automate data collection, clean-up, analysis and visualization for your industry
3. Make Expense Tracker 2.0 that can read a folder of monthly debit & credit files, show where the money is coming from and where it goes, spot trends and does forecasting.

All 3 Months
in One Course

Introducing Excel School

Learn all the Advanced Excel Skills in one place with my Excel School Program. Rated 5 stars by more than 10,000 students, this course will give you everything you need to be successful in workplace.

Check out the course and enroll today

Got Questions?

Share your questions and comments below...

Got a question about this roadmap or have suggestions? Please share your thoughts in the comments section below.

The post Learn Advanced Excel – 3 Month Roadmap with Resources appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.