The post 3 Weeks Roadmap to Learn Power BI (with free resources) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?  Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.

Updated for 2026(v2): Download the mutual fund tracker – India now.

NOTE: File updated on 23 August 2026 to fix a refresh error. Download again if you need to.

How to use this mutual fund tracker Excel workbook?

1. Download and save the file to a folder on your computer (do not leave it in the downloads folder)
2. Right click on the file in your file explorer, go to Properties and check “unblock”. See the screenshot below.
   

   

3. Open the file in Excel (you need Excel / Office 365 to use it. If you are using older version of Excel, you need free Power Query add-in)
4. If prompted, enable “External connections”
5. Go to Data and click on Refresh all
6. This will fetch updated funds list and latest NAV (Net Asset Value) from AMFI India website.
7. On the My Funds page, specify the funds you own, units, purchase price and purchase date
8. The tracker will calculate your return, CAGR (Compounded annual growth rate of funds) and simple return of your portfolio and display it
9. When you want to see updated value of your portfolio, simply refresh the tracker (Data > Refresh All or press CTRL+ALT+F5)

How this mutual fund tracker is made?

We will use 3 simple excel features to achieve this – Power Query, Cascading Drop downs and vlookup()

1. First, lets put a tabular format for our portfolio: We can have fund name, # of units, purchase NAV (Net Asset Value, the cost of unit for your when you bought it), purchase date, total value at purchase (units * purchase NAV), current NAV (we will pull this data from internet), value as of now (units * current NAV), Profit / loss amount and profit / loss % as our table columns. Once you learn how to do this, you can add more columns depending on what / how you want to track your MF portfolio.When you finish creating the table, it would look something like this:
   

   

2. Next we will use Power Query to automatically fetch the funds and latest fund value from AMFI website. This site maintains updated funds and values and publishes the list as TXT file at URL- https://portal.amfiindia.com/spages/NAVAll.txt We can connect to this page as web URL from PQ and setup some simple rules to clean the data and extract the relevant bits we need. The result looks like this in Power Query editor.
   

   

3. Once we load this data to Excel, we can build a simple 2 level cascading drop-down system to capture details on the “my funds” page. That way, our drop down will be small and easy to use. Please read cascading drop downs page for details on how to do this.
4. Finally, based on the fund name, we fetch the NAV and NAV date using, you guessed it – VLOOKUP formula. The rest is easy to calculate.
5. Now as you input fund names and refresh data, your portfolio gets updated.
   

   

Few ideas on how you can enhance this:

• Add graphs to see visually how the funds are doing
• Build some VBA to store previous NAV values of your funds so that you can see historical dates

Problems with MF Tracker?

• I cannot refresh data: You need internet connection. You also need Power Query for Excel (this is available by default in Excel 2016 or above, Office 365. If you have an older version of Excel, download Power Query add-in for free.
• The URL on AMFI website has changed (again): The new updated 2026 file provides an option to customize AMFI url in the “datasource settings” sheet. Just put the new URL there and refresh everything. As long as the file format is not different, you should be able to see NAV updated based on this new URL.
• I see some error during download / refresh: The AMFI people have been inconsistent with their file formatting. It could be a weekly issue (ie every Friday they might be publishing some extra data.) Try again in a day or two. If the problem persists, post a comment so I can suggest a work around.
• Some other problem: Please post a comment so I can look in to this for you.

The post Mutual Fund Portfolio Tracker using MS Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Mandatory Training at Awesome Chocolates

You are the head of L&D department at Awesome Chocolates. As part of employee induction, every new hire at Awesome Chocolates must complete these 3 trainings:

• CM101 – Chocolate Making – 101
• CC101 – Cookies vs Chocolates
• EAT101 – How to truly savor a chocolate

You were given a spreadsheet with 2 tables of data (download it here).

1) All the new joiners since 1 Jan 2026 (table name: staff)

2) All the training done by your staff since 1 Jan 2026 (table name: trainings)

Based on these two tables, please answer below 3 questions.

Question 1: How many employees have completed all the mandatory trainings?

Find out the total number of employees (started on or after 1 Jan 2026) who completed all 3 mandatory trainings? A mandatory training is considered to be completed only if the employee passes the final assessment.

Question 2: How many employees have completed at least one mandatory training?

Find out the total number of employees (started on or after 1 Jan 2026) who completed at least one mandatory training? A mandatory training is considered to be completed only if the employee passes the final assessment.

Question 3: How many employees have not attempted any of the mandatory trainings? Who are they?

Find out the total number of employees (started on or after 1 Jan 2026) who have not attempted at least one mandatory training? List all such employee ids & names. A mandatory training is attempted if the employee starts the course (irrespective of pass / fail).

Bonus Question: List training completion rates by department

Generate a summary table that shows department name, total head count of new starters, how many have done all the mandatory trainings, percentage of completion (done count / headcount).

Post your answers on the homework page or reply back to me with your answers.

All the best ?

Download the sample data file

Click here to download the sample data file. It contains the data, answer key (so you can check if your answer is right or wrong) but not the solution.

Don’t struggle, Gain Excel Skills

If you struggle with solving business questions like this using Excel formulas or pivots, my Excel School program is the perfect fit for you.

It is designed to give you confidence and right skills so you can efficiently solve business problems, build proven Excel solutions and be a rock star at work.

Click here to Check out the program.

Solution & Explanation: in my Excel School

I will be including a quick & elegant solution for these questions along with clear explanation in my Excel School program next week. You can access it along with 20+ hours of amazing spreadsheet skills content when you enroll in the program.

Want more challenges? Check out the homework page.

The post Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

• See the calendar for entire year in a single view
• Dynamic any month calendar with detailed plan view
• Beautifully formatted and ready to print 12 month calendar view
• Automatic updation of holidays, weekends and activities
• Snapshot of upcoming activities
• Fully customizable – start on any day, any weekend, custom holidays
• Optimized for screen and print outs

Click the below button to download the free 2025 calendar template.

Compatibility

This calendar is compatible with Excel 365. It uses modern dynamic formula techniques to automatically generate the calendar, planner and month views.

If you have an older version of Excel (such as 2019 or 2016, 2013) then please use this alternative version.

How to use the 2026 Calendar Workbook?

The calendar & planner file has 4 tabs.

1. Calendar tab: See the 12-month calendar view + upcoming activities in this page. It also highlights any holidays, weekends and planned activities on the calendar in a different color.
2. Any month tab: This page lets you see the calendar for any specific month in a detailed view. You can change the month from cell C3 and the calendar updates automatically. The calendar shows date, any activities planned in a neat grid view.
3. Printable 12 Month Calendar: This tab presents an elegant and ready-to-print 12 month calendar. You can print it or save this as PDF to generate all the 12 pages instantly. The colors and fonts are also fully customizable.
4. Planner tab: Use this tab to set up your activities. Whatever items you list here will automatically show up on the calendar & any month tabs.
5. Customizations tab: Do you want to change the way your week begins? Need to add some holidays or change the icons? Use the customizations tab.

How is this calendar made?

The calendar workbook has two main components.

• Calendar
• Planner

Calendar Generation

To generate the calendar, I am using the dynamic array functionality of Excel 365. We can use the SEQUENCE function to create all the dates in any given year.

For example, =SEQUENCE(365,,DATE(2026,1,1)) generates all the 365 dates in the year 2026.

I then used the same logic to generate monthly calendars for all the 12 months and adjusted them based on the week start option.

Once the monthly calendars are generated, then I highlighted the weekends, holidays and activities using conditional formatting.

Activity Planner

You can set up any number of activities in the planner table. I am then using FILTER function to filter out the activities for a given day and show them next to the calendar date.

Also, if “highlight activities” is enabled, then I am highlighting the calendar cells in a different color.

In the 12-month calendar view, I am showing upcoming 10 activities using FILTER function too.

Interactive any month calendar page:

We use the same logic as above, but limit it to a selected month (with data validation drop-down) to show the calendar for any specific month. Here is the calendar for April 2026.

Know more about these calculations

If you want to learn more about the calculations and set up of this workbook, please refer to these articles + videos.

• Dynamic array functions in Excel – introduction to FILTER, SEQUENCE etc.
• Conditional formatting to highlight values in Excel
• Data validation to select a month in Excel cell
• Excel tables – How to create a simple tracker in Excel
• Video: How to use FILTER function in Excel

Previous year calendars – 2025 Calendar, 2024 Calendar, 2023 Calendar, Free Excel templates & files

The post FREE Calendar & Planner Excel Template for 2026 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Our first challenge involves Employee Data Analysis. You can score maximum of 35 points.

Imagine you have employee data in this “staff” table (in Excel / Power BI / SQL / Python or whatever tool you fancy)

Download sample data file (it has all the 28 employees) or use the below CSV.

Emp ID	Name	Reports to
TO-0002	Jim Halpert	TO-0007
TO-0004	Pam Beesly	TO-0015
TO-0007	Michael Scott	TO-0067
TO-0009	Erin Hannon	TO-0004
TO-0010	Jan Levinson	TO-0067
TO-0013	Karen Filippelli	TO-0076
TO-0015	Dwight Schrute	TO-0007
TO-0018	Andy Bernard	TO-0002
TO-0021	Cathy Simms	TO-0002
TO-0024	Kevin Malone	TO-0035
TO-0028	Creed Bratton	TO-0007
TO-0030	Toby Flenderson	TO-0067
TO-0031	Kelly Kapoor	TO-0028
TO-0033	Robert California	TO-0067
TO-0035	Angela Martin	TO-0007
TO-0039	Stanley Hudson	TO-0007
TO-0043	Ryan Howard	TO-0031
TO-0046	Oscar Martinez	TO-0035
TO-0050	Meredith Palmer	TO-0028
TO-0051	Gabe Lewis	TO-0072
TO-0055	Phyllis Vance	TO-0030
TO-0059	Roy Anderson	TO-0004
TO-0063	Nellie Bertram	TO-0030
TO-0066	Darryl Philbin	TO-0039
TO-0067	David Wallace	TO-0072
TO-0068	Pete Miller	TO-0015
TO-0072	Jo Bennet	
TO-0076	Todd Parker	TO-0002

Emp ID	Name	Reports to
TO-0002	Jim Halpert	TO-0007
TO-0004	Pam Beesly	TO-0015
TO-0007	Michael Scott	TO-0067
TO-0009	Erin Hannon	TO-0004
TO-0010	Jan Levinson	TO-0067
TO-0013	Karen Filippelli	TO-0076
TO-0015	Dwight Schrute	TO-0007
TO-0018	Andy Bernard	TO-0002
TO-0021	Cathy Simms	TO-0002
TO-0024	Kevin Malone	TO-0035
TO-0028	Creed Bratton	TO-0007
TO-0030	Toby Flenderson	TO-0067
TO-0031	Kelly Kapoor	TO-0028
TO-0033	Robert California	TO-0067
TO-0035	Angela Martin	TO-0007
TO-0039	Stanley Hudson	TO-0007
TO-0043	Ryan Howard	TO-0031
TO-0046	Oscar Martinez	TO-0035
TO-0050	Meredith Palmer	TO-0028
TO-0051	Gabe Lewis	TO-0072
TO-0055	Phyllis Vance	TO-0030
TO-0059	Roy Anderson	TO-0004
TO-0063	Nellie Bertram	TO-0030
TO-0066	Darryl Philbin	TO-0039
TO-0067	David Wallace	TO-0072
TO-0068	Pete Miller	TO-0015
TO-0072	Jo Bennet	
TO-0076	Todd Parker	TO-0002

1. How many people are directly reporting to my boss? (5pts)

The first question is simple. For a given employee ID (say TO-0021, Cathy Simms), how many people are directly reporting to their boss (TO-0002, Jim Halpert)? The answer should be 3.

Write the necessary Excel formula / DAX / Power Query / SQL or Python code to find the answer.

2. Who is my boss’s boss? (10pts)

We are going to level up. For a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss. The answer should be TO-0007, Michael Scott.

3. What is my reporting chain? (Score=20pts)

For a given employee (say TO-0021, Cathy Simms), print their entire reporting chain, delimited by the symbol ->.

Expected Answer is:
Cathy->Jim->Michael->David->Jo

Note: You may assume a maximum depth of 7 nodes if that helps.

Download Sample File

Grab the sample data file (Excel format) here. Use it to solve the problems. Alternatively, I have pasted the data above. Copy it and paste it in Python or SQL.

Post your answers below.

Leave a comment with your solutions / approach. All the best

Want more?

Check out my Excel Homework Tag page for more challenges and problems.

The post Who is my boss’s boss? [Data Analytics Challenge – 001] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to calculate NZ GST using Excel?

Assuming you have the sale price in cell C5, the GST is calculated by the below formula.

=C5 * 0.15

We multiply the “sale” or “service” price by 0.15 (or 15%) as the official GST rate in New Zealand is 15% [ref].

Excel formula for NZ GST from “total” price

Let’s say you want to figure out the GST from total price (GST inclusive price) of something. This is quite common in retail scenarios. You have an item for $140 on the shelves, but you need to figure out what the GST should be on this. In this case, you can use the below Excel formula.

Assuming your “total” price is in cell C15, the GST is calculated with this formula:

=C15 * 15/115

GST Reverse Calculation – What is the “sale price” if I know GST?

Occasionally, we may have the reverse problem. We know how much the GST is, but just need to figure out the total. In this case, you can use the below formula.

Assuming your GST is in cell I5, the sale price can be calculated with below Excel formula

=I5*100/15

and total price can be calculated with this formula

=I5*115/15

The 3 / 23rds and 3/20ths rules

Here is a handy shortcut to quickly figure out the GST from total or sale prices.

3/23rds rule – GST from total

If your total amount is known, just multiply that with 3 and divide by 23 to get the GST.

For example, if your total is $230, then GST would be $30.

=230 x 3 / 23
=690 / 23
=30

3/20ths rule – GST from Sale Price

If you know the “sale” price, just multiply it with 3 and divide by 20 get the GST.

For example, if your sale price is $140, then GST would be $21

=140 x 3 / 20
=420 / 20
=21

GST for hourly rates, services

If you work as a plumber / electrician / some other type of service provider and you charge by hour, then you can use below formulas for calculating GST.

Assuming your hourly rate is in cell I15 and hours worked in cell I16, the GST formula looks like this:

=I15*I16*0.15

FREE NZ GST calculator workbook

I have created a simple, plug-n-play GST calculator workbook for you. Please download it here, enter your price / total / hourly information and the file automatically calculates the GST for you. It also has the GST formulas / patterns that you can apply to your own data.

Click here to download the NZ GST Calculator.

Need Spreadsheet help?

If you are an NZ business and need spreadsheet help or automation services, please get in touch with me. I am a Wellington based Excel / automation expert and I have been helping clients for the past 15 years in creating simple & easy automation and Excel solutions. Please email me on hello@chandoo.org to discuss more.

Other Excel Templates to help you

• Free 2025 Calendar & To-do list template
• Free Project Tracker Template
• Free Household budget template

The post New Zealand GST Calculation with Excel [Free Template] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Option 1: If you have access to “original” data

Ha, I know, but we can dream eh? So, if you do have access to the original data from which the pivot is generated, just use that data and make the new pivot as you want.

If you need help creating a pivot report in the first place, learn the process here.

Option 2: Making a Pivot from Another Pivot when you don’t have access to original data

Tbh, this is the real scenario for most of us. We have a pivot and don’t have access to the data that was used to make it. Now we need to make another pivot. In this case, follow the below steps.

Pivot from Another Pivot – FREE Excel Template

I created a free Excel template to guide you thru the process with sample data. Download it here and use the sample data to understand the process better.

Step 1: Select and name your pivot range

1. Select the entire pivot table (including any headers) in Excel.
2. Go to the name box (next to formula bar on the left)
3. Type the name “pivot_range”
4. Pro tip: If you have multiple pivots, you can use names like “pivot_range1”, “pivot_range2”

See this illustration for the step.

Step 2: Go to Data Ribbon and load up the “pivot” to Power Query

1. Keep the pivot table selected
2. Go to Data Ribbon
3. Click on “From Table/Range” option in the Get & Transform Data area

This will load the Power Query Editor with your Pivot Table Data.

Step 3: Let’s “unpivot” the Pivot Table with Power Query

Now that our “pivot table” is in Power Query, we can “unpivot” it and create a regular table. This can be used to make our new pivot table.

Here is a snapshot of how the Power Query editor looks with the pivot_range data.

[optional step] Promote headers if needed

Depending on how your source Pivot is setup, you may need to adjust the column headings in Power Query. For example, in my case, I need to promote the headers. To do this, click on “Use First Row as Headers” button in the Home ribbon of Power Query editor.

See below illustration.

Step 4: Replace “null” with value from above

In my sample pivot, you can see that Rep name is not printed in all rows, just the first row. This shows up as null in the Power Query editor for rest of the rows. We just need to fill these down based on the top value.

• Select the column(s) with this problem
• Go to “Transform” ribbon in Power Query Editor
• Click on “Fill” and select Down to fill down all the nulls with the value from above

Step 5: Remove rows with “totals” & “sub-totals”

We don’t need totals or sub-totals any more. We will calculate them in the new pivot as needed. For now, let’s remove all the rows and columns that have totals.

1. Select the first column that has “total” labels
2. Click on “filter” button
3. Uncheck any total labels.
4. Repeat the steps for any other rows that need this clean-up step.
5. Pro tip: Use Text Filters > Does not contain to filter out all rows with “total” word in them.

Step 6: Remove Grand total / Sub-total columns (if any)

Let’s also remove any “grand total” columns and “sub-total” columns from our pivot report. Right click on the column with totals and select “remove” to take this column out.

Step 7: Unpivot the data

Finally, our pivot report is ready to be unpivoted.

1. Select the column(s) with row labels. In the above example, I selected “representative” and “day of week” columns
2. Pro Tip: Hold SHIFT or CTRL to select multiple columns in one go.
3. Right click on the column headings of either column.
4. Select “unpivot other columns”
5. This should reshape the pivoted data to unpivoted format.
6. See this quick demo (GIF):

Step 8: Rename the new “attribute” & “value” columns

Double click on the newly added “attribute” and “value column headers to rename them to appropriate labels. In my case, I named them – Gender & Calls.

Step 9: Load the data back to Excel so we can make the pivot

Ok. We are done. Just load the data back to Excel. To do this, go to “Home” ribbon and click on “Close & Load” button.

Step 10: Create the Pivot from the loaded data

Once the data is in Excel, just select any cell in the data, go to Insert > Pivot Table (shortcut: ALT N V T) and set up the pivot as per your needs. In my case, I needed the pivot report with number of calls by Day of Week & Representative. So here is how I made it (see the quick video demo).

Things to keep in mind:

The Power Query based approach to create pivot from another pivot is great, but you need to keep a few things in mind.

• Doesn’t work for averages: If your original pivot table has “averages” instead of “sums”, the new pivot will not be correct. This is because you will make the mistake of “averaging averages”. This technique works great for sums & counts only. Any other measures like average / median / min /max, you need “actual” data to make the new pivot.
• Power Query steps can get complicated: If your original pivot has a very complex, nested layout, then the PQ steps needed to “transform” data can be complex (but not impossible). I suggest learning how to use Power Query to solve such issues. Refer to this article or video to start your PQ journey.
• Needs refresh for data changes: If your original Pivot table changes (new values or new rows / columns), you need to update the “pivot_range” named range and refresh the power query data.
  • To update the named range: Go to Formula ribbon in Excel and click on “Name Manager”. Select the name “pivot_range” and edit it. Adjust the cell references as per your newly updated pivot.
  • To Refresh Power Query: Right click on the Power Query data you have loaded in Step 9. Select “Refresh’ to update the loaded data with new changes. Now go to the pivot you made (in step 10) and refresh that too (you guessed it right! Right click and Refresh).

What to do if you get an error (in Power Query):

Errors can happen either during the initial process (steps 3 to 9) or when you refresh the power query connection. Solving the error depends on your exact pivot table layout and what changes were made. But here are the most likely reasons for the error.

• Column names have changed: You will get error if your columns (in the original pivot) were changed between updates. Adjust the names in the original pivot or go to Power Query editor, locate the step where the error is happening and adjust the names there.
• Data type issues: If for some reason, your original pivot’s values are read by Power Query as “text”, it can create issues. Right click on the columns with numbers and explicitly convert them to numbers in PQ.
• Layout changes: If your pivot layout changes (say, instead of 2 columns, it now has 3 columns of row labels), then your refresh will fail. You need to select one more column before unpivoting (step 7).
• Other issues: Leave a comment with the issue / error you are facing so I can help.

Pivot from Another Pivot – FREE Excel Template

I created a free Excel template to guide you thru the process with sample data. Download it here and understand the process better.

In conclusion:

Power Query in Excel offers an elegant, simple and easy way to deal with the annoying issue of using “pivot tables” as data source. I had plenty of success with this method and I hope will too. If you do have any questions or face issues during the process, leave a comment.

The post Make a Pivot from Another Pivot Table in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.