• 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.

Why you may want to use XLOOKUP with two sheets?

If you have data in two places (sheets or Excel workbooks) like depicted above, you may want to use XLOOKUP to combine data from both places to get the full picture. For example,

• Student list in sheet1, course price list in sheet2, you want to know the price of courses against student names in sheet1
• Invoice list in sheet1, payment list in sheet 2, you need to know which invoices are paid up (reconciliation)
• Equipment list in sheet1, inspection details in sheet2, you want to know when the last inspection date is for each equipment

What you need?

• You need two sheets of data.
• or if data is in two separate Excel files, then open both files.
• If you need a sample data file, grab my free xlookup two sheets template.

XLOOKUP with two sheets (step-by-step instructions)

Step 1: Identify common column between both sheets

For example, in the above scenario, “Fee” is the common column between sheet 1 (student list) and sheet 2 (course list).

Step 2: Write the XLOOKUP formula

Go to the sheet where you want to get the data from “other” sheet and write the XLOOKUP function using the pattern below.

=XLOOKUP(
  all cells in first sheet, 
  common column in second sheet,
  column you want in second sheet,
  optional output for missing values
  )

for example, in our students & fees case, we will use the below XLOOKUP function.

=XLOOKUP(C4:C43,
         Courses!B4:B15,
         Courses!D4:D15
         )

As demonstrated above, xlookup can automatically spill values for all the rows based on the common column you have specified. No need to individually write or drag the formulas. You also don’t need to “lock” your references with this style of formulas. Learn more about the spill functionality and dynamic array behavior of Excel here.

=XLOOKUP([@Course Name], courses[name], courses[fee])

What if I have more than one common column? (XLOOKUP multiple criteria)

Let’s say you have different fee per course based on the the student’s location (ex: In state is $600, out of state is $900). Something like this:

In such cases, your student’s data will also have both “course code” and “student type” columns. something like this:

As you can see, our XLOOKUP needs to check both of these columns to figure out the correct fee per row.

XLOOKUP with two sheets, multiple columns (step-by-step instructions)

Step 1: Identify common columns between sheets

In our case, the common columns are,

• Course Code (column C in sheet 1, column B sheet 2)
• Student Type (column D in sheet 1, column C in sheet 2)

Step 2: Write the multi-criteria XLOOKUP

Instead of looking up for a specific column value, we start the xlookup with 1 and construct a “boolean” checking array. The formula looks like this:

=XLOOKUP(1,
('Courses NEW'!$B$4:$B$27=Students!C4)*('Courses NEW'!$C$4:$C$27=Students!D4),
'Courses NEW'!$D$4:$D$27)

Formula Explanation:

• We start the lookup with 1. I will explain what this is in a second.
• The lookup array has multiple parts, one per common column. As we have two columns to match (course code and student type), we have two parts here.
  • Part1: (‘Courses NEW’!$B$4:$B$27=Students!C4) checks which courses in column B of sheet 2 (course code column) match with the course of current student record (column C of sheet 1)
  • Part 2: (‘Courses NEW’!$C$4:$C$27=Students!D4) does the same, but for student type
  • Part 1 * Part 2: when we multiply both of these checks, we end up with an array of 0s and 1s. for example, it will look like this: {0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
• The return array is just the fee column of sheet 2
• As we are looking for 1, xlookup matches the 1 in Part 1*Part 2 and returns the corresponding fee.

Related: learn more about what and how of this boolean multiplication logic in Excel

Watch – XLOOKUP with multiple criteria explained quickly

Generic Formula Pattern for any number of common columns with XLOOKUP

Use this pattern and adjust everything as per your data to match any number of common columns

=XLOOKUP(
    1,
      (COLUMN 1 in second sheet = value 1 first sheet) * 
      (COLUMN 2 in second sheet = value 2 first sheet) * 
      (COLUMN 3 in second sheet = value 3 first sheet) * 
      (COLUMN 4 in second sheet = value 4 first sheet),
    COLUMN YOU WANT TO GET IN SECOND SHEET,
    OPTIONAL value for missing cases
  )
      

What if I have data in two separate workbooks (Excel files) instead of sheets

The process is exactly same as two sheets. You just need to keep both files OPEN for the XLOOKUP to work. If you close the second file (one with fees in this example), the formula in first workbook works as long as you don’t touch it or recalculate the workbook (F9). At that point it will throw an error and ask you to open the file.

Alternatives to XLOOKUP for combining data from two places

While xlookup is great, you can also use below alternatives to get data from another place.

• Use VLOOKUP to combine two sheets of data: You can use VLOOKUP (or even INDEX+MATCH) to combine data from two places. Read this article for the instructions on how to use VLOOKUP .
• Power Query for combining or merging data: Excel Power Query is another great way to combine data from two places. Refer to this video to learn more about power query for combining two sheets of data.
• Power Pivot to combine data from tables to make a single pivot: You don’t always have to combine data. You can keep things where they are and join tables via common column just like databases with Excel’s Power Pivot feature. This lets you calculate total fees or averages using pivot tables. Refer to this page for an introduction on how to use data model and power pivot feature of Excel.

My preferences:

For simple scenarios and quick analysis, I prefer using XLOOKUP or VLOOKUP to quickly combine data like this.

But if the data is coming from two separate files (workbooks or even sharepoint lists etc.), then I use Power Query. It gives me more flexibility and choices. Refer to my Power Query tutorial page for more spicy examples on what this powerful feature can do for you.

Bonus: XLOOKUP with two sheets: Sample workbook

If you need a hand with the formulas explained above, download my free XLOOKUP two sheets template and refer to the formulas in columns E & F. Let me know if you have any questions by leaving a comment.

Related Resources:

To learn more about the important Excel functions and concepts, refer to below articles & videos:

• How to use XLOOKUP function in Excel?
• How to use Power Query in Excel?
• How to use Excel Tables?
• Power Pivot in Excel – Beginner tutorial

The post How to use XLOOKUP with two sheets? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

What is XLOOKUP?

Using XLOOKUP, we can search for an item in a list using the lookup value and return a matching item. For example, you can lookup for salesperson “Jackie” and return their sales amount from the data below using the XLOOKUP function, as depicted above.

It is the newest member of Excel’s lookup function family. You may already know the other members of this group – VLOOKUP, LOOKUP, HLOOKUP, INDEX+MATCH.

How to use XLOOKUP – step by step instructions

Let’s say you have data for salespeople like above and you want to find the Net Sales for “Jackie”. Follow below steps to create the XLOOKUP function in Excel.

1. Write =XLOOKUP( in a cell
2. For the lookup_value, Type the name of the salesperson in double quotes (ex: “Jackie”)
3. Tip: If you have the name of the person in a cell (like G4), you can point to the cell instead of typing the name
4. Now for the lookup_array, select the names column of your data.
5. and for the return_array, select the net sales column of your data.
6. Close the brackets and hit enter.
7. Congratulations, you’ve just created your first XLOOKUP formula in Excel.

Here is how the formula and result look in my data:

What if the lookup value is not in the lookup array?

One of the most common scenarios of lookups in Excel is not finding the value you are looking for. Imagine, you are looking for the salesperson “Chandoo”, but he doesn’t exist in the dataset. In this case, XLOOKUP will return #N/A error.

But we can use the 4th parameter of XLOOKUP – if_not_found to set an optional value to display when there is an error.

Here is an example formula with that:

=XLOOKUP("Chandoo", B4:B21, D4:D21, "Not found")

How to lookup in the middle with XLOOKUP (INDEX MATCH replacement)?

Let’s say you want to lookup the sales amount of $726 and return the name of the person (in this case, Jessy). Previously, you needed to use the INDEX+MATCH combination for this. But XLOOKUP let’s us specify any range or column for lookup_array portion. so, no more INDEX+MATCH… Yay!

To perform the “net sales” lookup and return the name of the person, we can use the below formula:

=XLOOKUP(G4, G4:G21, G4:G21, "Not found")

The above XLOOKUP example assumes that G4 contains the net sales value you want to lookup.

How to lookup higher than / lower than using XLOOKUP? (approximate match)

Say, you want to find out the closest person with the net sales of $1300. In this case, we don’t have anyone with that value in the data. You can still use XLOOKUP to get the approximate matching value, either next higher or lower in the data.

For this we can use the 5th parameter of the XLOOKUP function – match mode.

There are 4 match modes in XLOOKUP.

• 0 or Exact match. This is the default value for XLOOKUP.
• 1 or next larger match. This looks up for the exact or next highest value in the lookup array.
• -1 or next smaller match. This looks up for the exact or next lowest value in the lookup array.
• 2 or wildcard match. This looks up based on a pattern you have mentioned in the lookup value. More on this further down in the article.

To get the next highest matching value:

=XLOOKUP(G4,D4:D21,B4:B21,,1)
Note: 1 refers to next higher value for match mode.

The above formula returns “Jonathan” in the sample data, as he has the next highest amount – $1316.

To get the next lowest matching value:

=XLOOKUP(G4,D4:D21,B4:B21,,-1)
Note: 11 refers to next lower value for match mode.

This formula returns “John” as he has the next lower value – $1088.

Getting Partial Matches with XLOOKUP (Wildcard / Pattern Matching)

XLOOKUP also allows for a powerful and elegant pattern matching in your data. Let’s say you want to find the net sales for the person whose name begins with the letters Jam. In this case, we can use the match mode 2 (wild card) along with the wildcard operators * (asterisk) and ? (question mark) to create our XLOOKUP.

To find the net sales of the person whose name begins with Jam,

1. Use the formula =XLOOKUP(H4&”*”,B4:B21,D4:D21,”No such person”,2)
2. Here H4 contains the first few letters of the name, i.e. Jam
3. The lookup value is H4 & “*”. This tells XLOOKUP that we want the name to begin with the value of H4 (Jam) and then there can be any number of characters.
4. Rest of the XLOOKUP parameters are as per usual.
5. Don’t forget the match mode operator as 2. We need this for Wild card – pattern matching.

Refer to above illustration for more on the pattern matching xlookup.

Additional Pattern Matching Tricks with XLOOKUP

Refer to below handy table for some extra tips on using the partial matching feature of XLOOKUP.

What if there are two matching values?

If you have more than one matching item for the lookup value in your data, XLOOKUP, just like all other lookup functions in Excel, will always return the first matching item’s corresponding value.

As you can see in the above illustration, we have two sales persons with the name Johnson in our data.

When using =XLOOKUP(“Johnson”, B4:B21, D4:D21) we are going to get the net sales of the first Johnson ie $1540.

But what if I need to get the second or third or all the matching values?

In this case, you can use the new FILTER() function in Excel to get the second or all matching items.

Here is the formula.

=FILTER(D4:D21,B4:B21="Johnson")

For more information on FILTER function, refer to this article.

Horizontal Lookup with XLOOKUP (HLOOKUP replacement):

XLOOKUP works just as good with horizontal data too, thus replacing any need for HLOOKUP function.

In the above example, I have monthly budget table and I want to lookup the budget value for April 2024.

We can use this XLOOKUP formula to do just that.

=XLOOKUP(C10,C3:N3,C4:N4)

Returning entire row of information with XLOOKUP:

Another powerful feature of XLOOKUP is that it can return multiple values all corresponding to the same lookup value. For example, I want to see the budget, actual and balance information for the month of April 2024, from my budget spreadsheet below. We can use XLOOKUP for that easily.

=XLOOKUP(C10,C3:N3,C4:N6)

In the above formula, by using multiple rows (C4:N6) as the return array, we can return all corresponding values for the lookup value in C10 – ie April 2024. Excel will automatically spill these values into separate cells on the worksheet.

XLOOKUP – things to keep in mind:

When using XLOOKUP, you must keep these points in mind:

• XLOOKUP needs Excel 365 or Excel on the web or Excel 2021: This function is not available in all the versions of Excel. So check your version of Excel before you start using xlookup. If you notice #NAME errors when working with XLOOKUP, that means your version of Excel does not support this function. Instead, use INDEX+MATCH formula.
• Use the If not found option to fix errors: To avoid any lookup errors (#N/As), use the fourth argument of the XLOOKUP function – if_not_found.
• Lock cell references when creating lookups: If you are writing multiple XLOOKUPs in a range, don’t forget to lock your cell references to absolute mode (change B4:B21 to $B$4:$B$21) so that when you drag or fill the formula down, your lookup and return array ranges don’t change. This is a common mistake and you will end up with wrong results. (absolute vs. relative references)
• Or better yet, convert your lookup data to a table: A simple fix to the relative reference issue is to use tables on your data. This way, you can write simple XLOOKUP formulas like this: =XLOOKUP(“Jackie”,sales[Sales Person],sales[Net Sales])
  • Learn more about how to use Excel tables with your data.
• In case of multiple matches, XLOOKUP always returns the first (or last matching item, if you used search mode option) value. This is why it is important to also learn how to use the FILTER function in Excel.

How is XLOOKUP better?

• XLOOKUP makes the most used formula in Excel straight forward and less error prone. You just write =XLOOKUP(what you want to find, the list, the result list) and boom, you get the answer (or #N/A error if the value is not found)
• Looks up exact match by default: One of the annoyances of VLOOKUP is that you must mention FALSE as last parameter to get correct result. XLOOKUP fixes that by doing exact matches by default. You can use match mode parameter to change the lookup behavior if you want.
• 4th parameter to support value not found scenario In most business situations, we are forced to wrap our lookup formulas with IFERROR or IFNA formulas to suppress errors. XLOOKUP offers 4th parameter (read more about it below) so you can tell what default output you want if your value is not found.
• XLOOKUP offers optional parameters to search for special situations. You can search from top or bottom, you can do wildcard searches and faster options to search sorted lists.
• It returns reference as output, not the value. While this may not mean much for normal users, pro Excel user’s eyes light up when they discover a formula that can return refs. That means, you can combine XLOOKUP outputs in innovative ways with other formulas. For example: XLOOKUP to create dependant drop down in Excel.
• It is so much cooler to type, you just type =XL. I am not sure if this is a happy coincidence but saying =XL to get this formula is just awesome.

XLOOKUP Syntax

Simple case:

=XLOOKUP(what you want to look, lookup list, result list)

=XLOOKUP("Jackie", sales[Sales Person], sales[Net Sales]) 

returns Jackie’s [Net Sales] if the name can be found in [Sales Person]

Optional parameters:

By default, you just need 3 parameters for XLOOKUP, as shown above. But you can also use 4th, 5th and 6th parameter to specify how you want the lookup to be done.

4th parameter for XLOOKUP: IF not found (no more IFERROR!!!)

The newly introduced XLOOKUP has an even newer feature. It now supports if not found option. This is the 4th parameter.

For example, use:

=XLOOKUP(“Chandoo”, sales[Sales Person], sales[Net Sales],”Value not found”) to return “Value not found” if the lookup value is not available in the search column – sales[Sales Person].

5th parameter for XLOOKUP: Match mode or type:

Use this to tell Excel how you want your MATCH to happen. The default is 0 (exact match) but you can also use these other options, shown below.

6th parameter for XLOOKUP: Search mode

Try this if you want to search from bottom to top. The default direction is top down (1).

XLOOKUP with two sheets

If you have data in two seperate sheets (or files), you can use XLOOKUP to quickly combine the data and get what you want.

Refer to my XLOOKUP with two sheets article for detailed instructions and formula examples.

XLOOKUP – Video Tutorial:

Here is a simple but effective video tutorial on how to use XLOOKUP function and how it can replace VLOOKUP and INDEX+MATCH functions. You can also watch the xlookup tutorial on my channel.

Download XLOOKUP examples – workbook

Click here to download the 13 XLOOKUP examples file.

And one more example file, this with INDEX+MATCH replacements.

Final thoughts on XLOOKUP

In my opinion, XLOOKUP is a terrific function and a must have for any data professional. It is part of my essential Excel formulas list for data analysts.

When we nest XLOOKUP formulas, we can also perform more complex lookups like 2-WAY lookups or search across different worksheets. I discuss some of these advanced scenarios in a recent video on my YouTube channel. Please watch it here.

The only downside of XLOOKUP is the compatibility. It doesn’t work in all versions of Excel. For this reason, I still think there is value in learning how to use VLOOKUP and INDEX MATCH functions.

Do let me know what you think about XLOOKUP and if you have any questions about it using the comments section.

Learn more about Data Analysis with Excel:

This post is part of my data analysis with Excel series. Please learn other topics too and improve your data skills with Excel.

• How to use Excel Tables – 13 time saving tips
• How to use Dynamic Array functions in Excel such as Filter, Unique, Sort etc.
• Working with Power Query in Excel to save time and shine at work
• Know more about XLOOKUP with my course

The post How to use XLOOKUP in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Have a bunch of CSV files in a folder and want to merge or combine them to one big file? Follow these simple instructions to combine multiple CSV files in to one spreadssheet using Microsoft Excel (2016 or above).

What you need?

• A folder with CSV files (click here to download sample files, if you need some)
• Microsoft Excel (2016 or above version)

Method 1: Merge CSV Files using Power Query (Recommended)

This is by far the easiest and quickest way to combine data from CSV files in a folder using Excel.

Follow these steps:

1. Close any opened CSV files: Before proceeding, close any of the opened CSV files. Make a note of the folder path too.
2. Open Excel and make a new file: This will be our merged CSV data file. Here, we will setup Power Query to combine all the files.
3. Go to Data Ribbon > Get Data and click on From Folder

Paste the folder path or navigate to the folder and click “select”.

4. For simple CSV files: If your CSVs are already clean and you just want to merge them, select the Combine > Combine & Load option.
5. For complex CSVs or if you need to clean-up data before merge: Select the Combine > Combine & Transform Data option. This will open up “Power Query Editor” so you can clean up data or apply “transformations”.

6. Click “OK” in the next screen: This next screen shows a sample of your data (usually the first file) so you can confirm to Excel how your data looks. If your delimiter is not comma (for example, you have TSV files instead of CSV), you can also tell Excel about that using this screen.

7. If you selected “Combine & Load” option: Your merge is done! The combined data from your CSV files is now loaded into Excel. This is how it will look (see below). You can use the “Source name” column to figure out which file each row came from.

For “Advanced” Merge Scenarios – Combine & Transform Data

If your CSV files are not so simple or you want to further clean-up data after merging, you can follow these steps.

Select the “Combine & Transform Data” option in after Step 3 (ie once you point the folder). This will take you to Power Query editor (after you confirm the file details, as shown in step 6 above).

Once you are in Power Query Editor screen, you can apply any data clean-up and transformation steps on your data easily. I will share a few examples below. But refer to my Power Query tutorial page or video for detailed information on how to use Power Query for data cleaning and transformations.

Example 1: Removing the “Total” column from merged CSV files

Let’s say you don’t want the “total” column from these merged budget files. In the Power Query editor, right click on the “total” column and select “remove”. This will remove the total budget column. Don’t worry, it is not going to remove data from original CSV files. But when you merge the data, you won’t just see the “total” column.

Example 2: Reshaping the 12 columns to month & value column structure

While the 12 monthly column structure works best for gathering budget data, it may not be ideal for data analysis. So let’s reshape our merged data to a format like this:

• File name
• Budget Category
• Month
• Budget value

Essentially, each row of the data in original CSV file becomes 12 rows in merged file. This process is caleld “unpivoting”. It looks like this:

To unpivot data in Power Query Editor:

1. Select the file name & cost category columns (and any other columns you want to retain).
2. Tip: You can hold Shift or CTRL to multi-select columns in Power Query Editor.
3. Right click on the selected columns and select “Unpivot other columns” option.

This will replace the 12 monthly columns with a new “Attribute” and “Value” columns. These are nothing but our Month and Budget columns!

Renaming the columns: You can double click on the column header and rename it to Month and Budget.

Load combined CSV data to Excel:

When you finish the data clean up and transformations you want to do, go to the Home ribbon in Power Query editor and click on “Close & Load” to bring the finalized data to Excel.

Why this method is the best?

I have been using (and advocating) Power Query for more than 10 years. I can’t tell you how much time and effort this little trick has saved me. Here are my top reasons for why Power Query is the best way to merge CSV files.

• Fully Dynamic: You don’t need to worry about changing files or growing (or even shrinking) data. Once you properly set up the Power Query connection, your data will be merged automatically even if there are 1000s of files.
• Automated: One of the biggest challenges with data merges like this is that your raw data files change often. With Power Query, updating the “merged” dataset is really simple. Open the merged file, right click anywhere on the merged data table and select “Refresh” to automatically update the merged CSV data.

• Works even if the columns are out of order*: This method works just as perfectly even if your CSV files have columns in jumbled order, as long as the column headings are same across files. That means if file 1 has “Cost category” as column 1 and file 3 has “Cost category” as column 14, the merge still works, as the column heading is matching in both cases. I talk about how to deal with more complex situations of mismatched headers in this video.

Other ways to combine or merge CSV files with Excel

We can also use below techniques to merge CSV files with Excel (I prefer Power Query btw).

• Using VBA Macros to combine CSV files: Excel’s own coding language – VBA offers a powerful and proven way to combine multiple files (CSV, Text or even other Excel files) and get merged data in one place. This is an advanced method and not really recommended for beginners. Refer to this article for a detailed step-by-step instruction on how to combine data with Excel VBA
• Manual Copy Pasting: For something quick and dirty, you can also manually open the CSV files and copy paste the data into master Excel file. This is an error-prone and labor intensive process and should only be used in one-off cases.
• Command Line Utilities: As CSVs are just text files, you can also use a simple command line utility to combine multiple CSVs to one file. This has the disadvantage of repeating headers and not working when the headers don’t match up or columns don’t align. Here is the command for Windows. This combines the CSV files in the sub-folder “merge csv” to a new file named “combined_file.csv”.

copy /b "merge csv"\*.csv combined_file.csv

• Using online tools: Plenty of online tools offer CSV merge functionality. You can upload your files on these websites and they will combine them for you. I suggest checking the privacy policies of these websites and using them only if you can’t apply Power Query or VBA or manual methods. Here are a few that offer this service: MergeCSV, CSV Combiner

Best Practices when Merging CSV Files (with PQ in Excel):

• Columns should match (need not be in same order): The merge options in Power Query work best if your columns match, even if they are out of order across files.
• Keep the folder clean: By default, Power Query is going to combine all the files in the folder you point to. So keep the folder clean and tight. Don’t copy or create files in the folder that you don’t want to merge.
• Close files before refresh: Power Query refresh can fail or miss the files if you keep them open when updating the query. So close everything before you hit refresh.
• Data Format Issues (especially with Dates): If you have CSVs containing dates and these files use different date formats, the merged file can be a mess and throw date formatting issues. Synchronize date and currency formats across files before merging them to avoid such data format issues.

In conclusion – Use Power Query to Merge your CSVs

Excel’s own Power Query offers a superior, easy and automatic way to combine CSV files. It works beautifully even when combining 1000s of files. For a recent client project, I combined 340 different budget files with Excel Power Query in under 10 minutes. Needless to say, the client’s jaw dropped when they saw the demo!

But for whatever reason, you can’t use Power Query, try either VBA or command line utilities or one of the online CSV combine options.

Bonus: Sample Files & CSV Combine Template

If you need a hand with combining CSV files, download my sample data files and CSV combiner template using below links. Unzip the files and adjust folder path in Power Query (source step of the merged query) to make it work with your computer’s path.

• CSV Combiner Template
• Raw Data CSV Files

Resources on Power Query

If you want to learn a bit more about what Power Query can do for your data problems, check out below resources:

• Power Query Tutorial with 4 AWESOME Examples
• Combine multiple Excel files with Power Query
• How to extract common values between two tables in Excel?
• Learn Power Query for Excel in 15 minutes – Video

The post How to Merge Multiple CSV Files in Excel (Step-by-Step Guide) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.