We can calculate any Finance & Accounting KPI values using...

Read MoreHey there, **I have a SUPER exciting announcement**! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you choose to accept it: Craft the most EPIC dashboard for the Awesome Chocolates CEO with sales & financial insights! **Winners stand a chance to score up to $500 in Amazon Gift Cards, plus some serious bragging rights!**

- Participants must use Power BI (any recent version) to create the dashboard.
- The dashboard should be built with native visuals only. No add-ons or marketplace visuals or Python/R visuals.
- The dashboard should contain a readme / help tab to explain the features and methodology.
- Your dashboard should feature the Awesome Chocolates logo, contest title on it. These assets are included in the dataset file below.
- Limit your dashboard to one Power BI Page (readme / help goes on a separate page)
- You are free to combine the business data of Awesome Chocolates with any publicly available datasets. Credit any sources in the readme / help tab.
- The participant agrees to allow indefinite copyright and sharing of the workbooks (and any extra datasets) with chandoo.org for further publication, remixing, and usage.
- Your dashboard should be submitted by
**30th of April 2024**using the upload instructions (to be added later). - Participants agree to have their name and any social media handles listed on the contest website.
- Only one entry per participant. But your dashboard PBIX may contain more than one page with different versions of the dashboard.

I have set up an exclusive community on Microsoft Teams to run & manage this contest. To participate, please follow the below-instructions.

- You need a personal email address (such as @gmail.com or @hotmail.com) or your phone number.
**Go to my Dashboard Champions Circle on Microsoft Teams**- Use your personal email address or phone number to register. You will need to switch to “personal” mode of teams if prompted.
- Join the community and find the dataset & dashboard purpose information inside.
- Start building your dashboard.
- Submit your entry by 30th April using the instructions in the community.

Please download the Awesome Chocolates Dataset using the link inside the teams community.

The purpose of the dashboard is to inform the CEO of Awesome Chocolates about the performance of our business in the last 13 months. The dataset contains daily shipment data from 2023 February to 2024 February. Here are a few things our CEO is interested in, but she likes surprises too. So get creative.

- How sales, units, shipments, profit, profit%, low-box shipments (shipments with box count under 50) trends are looking
- A detailed performance understanding at product or salesperson level
- Interesting patterns in product / geography data
- Ability to drill down to details if needed.

You are encouraged to use various powerful interactive features of Power BI like tooltips, bookmarks, DAX, conditional formatting, pictures and anything else you might fancy. Again, get creative and showcase what you can build, but stick to one standard sized Power BI page.

You should submit your Power BI dashboard by 30th of April, 2024 (end of day, pacific time).

Remember, only one dashboard per person.

Please submit your dashboard using the instructions inside **my exclusive teams community.**

For more information and guidelines, visit the Awesome Dashboard community page.

The total prize money for this competition is $500.

- First prize: $250 Amazon Gift Card
- Second prize: $150 Amazon Gift Card
- Third prize: $100 Amazon Gift Card
- Don’t forget the bragging rights!

A panel of Power BI experts and dashboard pros will select the winners. I will share the details later on the teams community.

All the best

*Note: This competition is powered by Microsoft Teams*

The post Announcing Power BI Dashboard Contest (win $500 prizes!) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Compound Interest Formula in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using *percentiles. *

In your workbook, set up a mapping (or lookup) table like this to map out each of the letter grades to the test score boundary.

- When setting up the mapping table, make sure to start from lower score to higher score (for ex: 0 to 100)
- For each grade, just specify the lower boundary value. So for example, Grade F begins from 0, Grade B- begins from 65

For this you need all the test scores for your students. Let’s say you have the test scores in column C, from cell C4. In an adjacent column, you can calculate the letter grade using the below LOOKUP formula.

**=LOOKUP(C4,$G$6:$G$16,$F$6:$F$16)**

In this formula:

- First value (C4) refers to the score for which you need the letter grade
- Second value ($G$6:$G16) is the “scores from” column of your mapping table set up in Step 1.
- Third value ($F$6:$F$16) is the “letter grade” column of your mapping table.

Once you have a result for the first test score, drag the formula down to see letter grades for all students.

Sometimes you may want to calculate the letter grade from the percentile of the test score. This sort of thing is also called relative grading (RG). To do this, we can use the PERCENTILERANK functions of Excel.

Here is a 3 Step process for that:

In your worksheet, set up a mapping table for letter grades like this:

Let’s say you have test scores in column C, in the range C4:C43.

In column D, write the formula =PERCENTRANK.INC($C$4:$C$43,C4) to calculate the percentile of test score in first cell against all scores. The result of this would be a percentile from 0% to 100% (both inclusive).

When you get the result for first cell, drag the formula down to fill up the rest.

**Tip: **If you want to calculate the percentile by excluding 0th and 100th percentiles, use the PERCENTILE.EXC function instead.

For this, we can use the LOOKUP function again. In column E use the below function:

**=LOOKUP(D4,$H$6:$H$16,$G$6:$G$16)**

In the above formula:

- First value (D4) refers to the percentile we calculated in step 2.
- Second value ($H$6:$H16) is the “percentile from” column of your mapping table set up in Step 1.
- Third value ($G$6:$G$16) is the “letter grade” column of your mapping table.

I made a quick Excel template to calculate letter grades from your test / exam marks. Just plug-in your values and see the results instantly. **Download the template here.**

That is right. All these formulas will work exactly same with Google sheets too. Here is a Google Sheets template if you need some help.

Do take these cautions when calculating alphabetic letter grades from your exam marks.

**Mapping table setup:**your letter grade mapping table needs to be from lowest marks / scores to highest. Just specify the lower boundary for each letter grade.- For example, if grade F is from exam score 0 to 35, then write 0.
- If grade B+ is from 80 to 85, then write 80

**Clean up your data:**If your test score data has missing values (for example, absent or hyphens) then the LOOKUP formula will give #N/A error. So clean up your data before you apply the LOOKUP function.

Now that you have calculated the letter grades, you may want to see the distribution of your student grades or understand which students are failing and need help. Use below Excel concepts & resources to do that.

- Understand the distribution of your data in Excel
- Create a histogram in Excel
- Using conditional formatting in Excel to highlight top 10 values
- How to use Excel Pivot Tables to understand and explore your data

The post How to convert test scores to letter grades in Excel? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>** Gender Pay Gap **is the difference in pay for groups of men & women and usually based on the average or median salaries. We can use Microsoft Excel to quickly calculate the GPG (Gender Pay Gap) from your data. In this article, let me explain the process, Excel formulas and offer you a ready to use GPG calculator.

* According to NZ Government, *

Gender pay gaps are differences in pay for groups of women and men, usually based on the median or mean pay that men and women receive.

Source: Statistics New Zealand

Assuming you have average salary of men & women in two cells C3 & C4, we can calculate Gender Pay Gap using the below formulas:

**Gender Pay Gap in $s:**

**=C3 - C4**

Generalized formula = average of male salary - average of female salary

**Percentage Gender Pay Gap:**

**=(C3-C4)/C3**

Generalized formula = (average of male salary - average of female salary) / average of male salary

Excel is a great option for identifying and reporting gender pay gap issues when you have full employee data. Let’s say you have the staff data in an Excel table as shown above.

In this case, we can use below formulas to calculate the Gender Pay Gap:

Create a 3-column table in Excel with the staff ID, gender & annualized full-time salary. (Related: Learn how to create a table in Excel)

Name your table as “staff” using the Table Design ribbon in Excel.

You can use AVERAGEIFS function in Excel to calculate the male & female specific average salary.

The formula for male average looks like this:

**=AVERAGEIFS(staff[Annualized Full-time Salary],staff[Gender], "Male")**

And the formula for female average looks similar.

The formulas for this are explained above. They are:

GPG in $s: =Average Male Salary – Average Female Salary

GPG in %: =(Average Male Salary – Average Female Salary) / Average Male Salary

Format the GPG $ and Salary calculations in your currency formatting (Ctrl Shift 4)

Format the GPG % in Percentage formatting using Excel format cells option (CTRL Shift 5)

Please refer to below illustration for formula set up and help.

It is a good idea to calculate both average and median GPG values from your data. We all know that an odd high value can impact the average calculation. May be your CEO is a female and her high $$$ salary thus she bumps up the average female pay significantly.

Firstly, calculate the median pay for both male & female groups. *Unfortunately, Excel doesn’t have a MEDIANIFS function. *So, use the below formula instead:

**=MEDIAN(IF(staff[Gender]="Male",staff[Annualized Full-time Salary]))**

**Caution: Array formula**

After typing the formulas, press **CTRL+Shift+Enter **to get the correct result.

Change the gender value to Female for the respective median salary.

Once both medians are calculated, you can easily calculate the gender pay gap (both in dollars and percentage) using the same formulas as above.

Click here to download my **Gender Pay Gap calculator template**. Copy and paste your data and the file calculates the GPG automatically.

Once you have calculated the Gender Pay Gap in dollars, just divide the number with total annual hours of work. In most countries, this would be 2080 hours (ie 52 weeks times 40 hours per week).

So, for example, if you have a pay gap of $3,117, then the hourly pay gap is $1.50

This means, female staff are earning $1.50 less than their male counterparts *every hour.*

A negative GPG value indicates that your female staff are paid more (on average or median basis) compared to the male staff.

While Gender Pay Gap offers a great insight into the compensation of men vs women employees, it has a few limitations.

**GPG doesn’t explain any hierarchical distribution issues.**If you have a lopsided distribution of staff in your organization (may be more female staff at lower-level positions and more male staff in senior positions), GPG doesn’t expose this issue. I recommend visualizing the male vs. female distribution by salary bands or seniority for a better insight in to these issues.**A low or zero Gender Pay Gap is not enough.**If you want an equitable and fair organization, aiming for a zero gender pay gap at aggregate level is not enough. You need to examine GPG by:- department level GPG
- city / location level GPG
- manager vs. non-manager GPG
- new hires vs. existing staff GPG

**GPG is meaningless for small organizations**. If your total headcount is less than 30, GPG calculations can be meaningless or less insightful.

Gender Pay Gap is a key metric (KPI) in HR data analysis. Calculating, measuring and tracking GPG is helpful to understand any underlying pay issues in your organization. But don’t forget to explore the staff distribution, hiring patterns and historical trends to fully understand your data.

For more on HR data analysis, check out below articles:

- 9 Box Grid & talent mapping analysis with Excel
- Understanding Employee Churn using Excel
- Employee training tracker with Excel
- HR Dashboard using Excel – Video
- HR Dashboard using Power BI

The post How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Today we will learn how to calculate weighted average in Excel with percentages.

Weighted average or weighted mean is defined as [from wikipedia],

The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.

…If all the weights are equal, then the weighted mean is the same as the arithmetic mean.

**Well, it is because, in some situations normal averages give in-correct picture.** For eg. assume you are the CEO of ACME Widgets co.. Now you are looking annual salary report and being the numbers-gal you are, you wanted to find-out the average salary of your employees. You asked each department head to give you the average salary of that department to you. Here are the numbers,

Now, the average salary seems to be $ 330,000 [total all of all salaries by 5, (55000+65000+75000+120000+1200000)/5 ].

You are a happy boss to find that your employees are making $330k per year.

**Except, you are wrong.** You have not considered the number of employees in each department before calculating the average. So, the correct average would be $76k as shown above.

There is no built-in formula in Excel to calculate weighted averages. However, there is an easy fix to that. **You can use SUMPRODUCT formula**. By definition, SUMPRODUCT formula takes 2 or more lists of numbers and returns the sum of product of corresponding values. [related: Excel SUMPRODUCT Formula – what is it and how to use it?]

**So, if you have values in B4:B8 and the corresponding weights in C4:C8,** you can use SUMPRODUCT like this to get weighted average.

**=SUMPRODUCT(B4:B8, C4:C8)**

**Caution: **However, the above method works only if C4:C8 contains weights in percentages(%) totaling to 100%.

`=SUMPRODUCT(<your values>, <your weights>)`

May be your weights are more than 100 percent. Or may be they are less than 100 percent. In both cases, you can use the below formula variation.

**=SUMPRODUCT(B15:B19, C15:C19) / SUM(C15:C19) **

The idea is to divide the total of weights with the SUMPRODUCT result so that we can adjust Weighted Average as the weights don’t add up to 100 percent.

`=SUMPRODUCT(<your values>, <your weights>) / SUM(<your weights>)`

If you have count of observations instead of weights, you can still use the SUMPRODUCT formula to calculate weighted average in Excel.

Here is the formula for above example:

**=SUMPRODUCT(B26:B30, C26:C30) / SUM(C26:C30) **

Notice that this formula is same as the formula for weighted average with weights not adding up to 100 percent.

`=SUMPRODUCT(<your values>, <your counts>) / SUM(<your counts>)`

Let’s say you have city wise observations and weights. And you want to calculate the weighted average, only for ** Boston **values. In this case, you can use a variation of the formula like below:

**=SUMPRODUCT((C5:C16)*(B5:B16=F5),D5:D16)/SUMIFS(D5:D16,B5:B16,F5)**

**How does this formula work?**

- SUMPRODUCT calculates the total value for BOSTON by summing up C5:C16 (value column) where B5:B16 is Boston (highlighted portion of the formula) and multiplies that with the counts.
- So in the above example, this will just give us the total of Boston – ie 218,600
- We then divide this with the total count of Boston (using the SUMIFS formula) – ie 400
- This results in the weighted average for Boston values alone – ie 546.50

For more information on how the conditions work inside SUMPRODUCT formula, please read this article.

In this workbook, you can find 4 examples on how to to calculate weighted average in excel. Go ahead and download it to understand the formulas better.

Here is a video with Weighted Average formula explained. Please watch it below to learn more. Alternatively, head to my YouTube page to see the weighted averages in Excel video.

Weighted averages are a great way to explain data and every data analyst should know how and when to use them with their data. Apart from Weighted Average, I suggest learning how to use moving average and average of top n values. These will help you explain the data and trends to your audience better.

What do you use it for? What kind of challenges you face? Do you apply any tweaks to weighted average calculations? **Please share your ideas / tips using comments.**

- Syntax and Examples of Excel Average Formula
- Formula for Average of Top 5 values
- Calculating Moving Average in Excel
- Using SUBTOTAL formula and calculating averages
- Showing Averages in Pivot Tables

The post Weighted Average in Excel [Formulas] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Excel 365’s FILTER() function is great for getting a cut of data that meets your criteria. But what if you need to filter and then show non-adjacent columns? Something like below. In this article, let me show you a few options to get discrete columns after filtering with the FILTER function.

Imagine you have a table data named “staff” and you want to see all the staff who joined in year 2021. We can use below FILTER function for that.

**=FILTER(staff, YEAR(staff[date of join])=2021)**

This will provide a list of all staff who joined in year 2021, as depicted below.

But we don’t want all columns, just ID, Gender, Salary and Leave Balance.

To see just columns 1,2,7 & 8 of this filtered data, we can use below formula.

**=CHOOSECOLS(
FILTER(staff, YEAR(staff[date of join])=2021),
1,2,7,8)**

This will give you exactly what you need without anything else.

Say, you do want the columns 2,6,8&9 but you want them to show up in the order 6,8,2&9 in the final output.

You can still use the CHOOSECOLS function like below.

**=CHOOSECOLS(**

FILTER(staff, YEAR(staff[date of join])=2021),

6,8,2,9)

If you want to use a range of column names and show filtered data for only those columns, we can use XMATCH along with CHOOSECOLS and FILTER, as demoed below.

- Set up your column headers in a range like Z5:AC5
- Now, we can use XMATCH to find the positions of these headers. =XMATCH(Z5:AC5, staff[#headers])
- When you pass the result of XMATCH to CHOOSECOLS, you can pick these columns.

**=CHOOSECOLS(
** ** FILTER(staff,YEAR(staff[Date of Join])=2021),
XMATCH(Z5:AC5,staff[#Headers]))**

- Let’s go inside out.
- The FILTER() function gets all the staff data for people whose joining date is in 2021.
**Range Z5:AC5 holds the names of the columns we want to see.**- XMATCH(Z5:AC5, staff[#Headers]) will tell you the column numbers for the columns you want by looking them up in the table header row.
- CHOOSECOLS() will then return those exact columns

- How to use FILTER function in Excel, FILTER function video tutorial
- How to use XLOOKUP function in Excel
- How to use tables in Excel

The post How to get non-adjacent columns with FILTER function in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Extract BOLD portion of a cell in Excel using getBoldText() function appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>So you have a ** SPILL error in your Excel tables?** In this quick article, let me show you 3 easy fixes to the problem.

This is not really a fix. But if you write certain types of formulas in table, Excel will warn you about the *potential* spill error and fix it for you. See this example:

One lesson we can take away from this *auto-correction of the formula *is that if you are using a table column style formula, change it to [@ column] style.

For example:

- Instead of LEFT([Name]) use LEFT([@Name], 1)
- =IF(LEN([Value])>6, “Too long”, “Short enough”) can be =IF(LEN([
**@**Value])>6, “Too long”, “Short enough”)

Note: This auto-correction of formulas is seems to be a new feature, so may not be active in all Excel 365 versions.

If an Excel formula can result in more than one value, *it automatically spills*. Such formulas are called **Dynamic Array Excel Functions.** A simple example is the SEQUENCE function.

=SEQUENCE(10)

will return the numbers 1 to 10.

If you use them in a normal cell in Excel, they work ok.

But when you type the same formula in a table, it gives the SPILL error (see this demo).

So **an easy fix is to change your formula to a version that doesn’t spill**.

Refer to below table to see non-spillable alternatives for some common situations.

Purpose | Spill Version | Non-Spill Alternative |
---|---|---|

To generate row numbers in the table automatically | =SEQUENCE(10) | =ROW()-ROW([#Headers]) |

Show all matching values with FILTER | =FILTER(data, data=10) | Return only the first matching value:=XLOOKUP(10, data, data) Concatenate all matches into one list:=TEXTXJOIN(“, “,, FILTER(data, data=10)) |

If you must have a spillable formula in the table column, an easy fix is to convert the table to a range. You will however, loose all the table features (such as structural references, data model connectivity and ability to send data to Power Query).

To convert table to a range, just select the table, go to **Table Design ribbon and click on “Convert to Range” button.**

Once your table is in a range format,

- Remove any spill formulas in the row 2 onwards in your range
- This will fix the #SPILL! error, as demoed below.

Tables do not support any sort of spill behavior. So another easy fix is to move the spill formula outside the table to an adjacent column. Something like this:

Unfortunately Excel ** currently **doesn’t support having SPILLABLE formulas inside tables. But if you still need a formula result along with your table data (for some calculation purposes), you can use the HSTACK function, like below:

=HSTACT(your_table, spill_formula_here)

For example, I want to add a ID number column to my table. Here is the HSTACK for that:

=HSTACK(my_table, SEQUENCE(ROWS(my_table)))

It’s not because Excel hates you. There are two things at play here.

**Dynamic Array Formulas:**want to spill the formula results down (or sideways) when there is more than value returned by the formula (example: SEQUENCE(10)). They throw a SIPLL error whenever something is preventing the formula from*spilling.***Excel Tables:**want to apply the same formula for all cells in the table column.

So when you type an array formula in a table cell, Excel tries to apply the same formula for all cells of the table. This creates a situation where each table row has a formula that wants to return multiple values. So while first row’s formula is trying to spill, second row has a formula of its own (as Excel tables automatically apply the same formula across). Thus the SPILL errors.

Please read below articles to understand Excel Tables & Dynamic Array features of Excel.

- What is Excel Table and how to use it?
- What is Dynamic Array Function in Excel?
- How to use XLOOKUP in Excel?
- More about SPILL Error [Microsoft Help]

The post How to fix SPILL Error in Excel Tables (3 easy solutions) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Loan Amortization Schedule in Excel – FREE Template appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Do you want to **create a dynamic dependent drop down list** in Excel like below? You can use XLOOKUP and data validation to set this up quickly. It is fully dynamic and works across a full column too.

Dependent or cascading dropdowns are a valuable way to make your workbooks error free and improve the user experience. Follow these steps to set them up.

In a blank area of your workbook, set up the data validation lists. ** If you have just two-levels,** use the structure as depicted below.

If you have 3 or more levels, just set up the first two-levels as shown above. Then for each additional level, create a structure like above.

Now, you will need to create data validation rules for each of the levels.

- Select the entire column of cells
- Go to Data ? Data Validation
- Change the validation type to “List”
- Specify Source as the range of cells containing the main or first category.

The process for all these other items is same. We are going to use XLOOKUP function, which can return multiple values for the search criteria.

*Related: Learn more about XLOOKUP function in Excel.*

- Select the entire column
- Make a note of the first cell of previous column. In this case, that is D5.
- Go to Data ? Data Validation
- Set the rule type as “List”
- For source, write the XLOOKUP formula with below pattern.

`=XLOOKUP(`*SELECTED_CATEGORY, CATEGORY_NAMES, SUB-CATEGORY_NAMES*)

For example, in my case, *selected category* is in D5, category names are in J4:N4 and product names are in J5:N15. So my XLOOKUP formula looks like this.

`=XLOOKUP(D5, $J$4:$N$4, $J$5:$N$15)`

Refer to below illustration to understand how these rules work.

Now that you have set up both main category and sub-category (or product) level rules, you can start to use the data validation drop downs like below.

- Select a category in the first column.
- When you go to the next column, you will see all the sub-category items there.
- Pick a selection from the list.
- Go back to first column to add a new item.

This key ingredient of this Dependent Drop-down technique is XLOOKUP. Previously, I’ve used INDIRECT formula with pre-defined names or even OFFSET formulas. But now that XLOOKUP returns the full range of values, we can simplify the process.

*Here is how this works:*

- When you select a category in D5, the data validation rule runs the XLOOKUP formula to get the matching values for that category in the range J4:N4.
- Then, as the return value for XLOOKUP is the range J5:N15, it returns the rows (or product names) corresponding to selected category.
- For example, if you picked “Bites” category in D5, then XLOOKUP will return the product values for Bites category –
*ie*the range K5:K15 - This list is then fed to the data validation drop-down.

*But what about all the blank values at the end…?*

Excel 365 automatically removes any duplicate items in the data validation list. So it would remove all the blank cells and replace them with a single blank value.

The beauty of XLOOKUP based approach is, your formulas and data set up are exactly same even if you need 3 levels or 4 levels or even more. Here is a demo of how my drop-downs work when used with 3 levels (in a table).

I made a video explaining how to create such a dependent drop down list in Excel. You will also see how the XLOOKUP really works. Watch the video below or on my channel.

Please grab my sample workbook with all the formulas for both two-level and three-level scenarios. Refer to the data validation rules to understand the formula syntax.

**Click here to download the workbook.**

**Compatibility:**The biggest limitation of this technique is you need**Excel 365**or**2019**+. So if you are your clients use an older version of Excel, you can’t rely on this technique. You can still use the OFFSET formula based approach we discussed here.**You need to set up formulas:**If you are not familiar with Excel formulas, this technique will be hard for you. Fortunately the formulas themselves are not that complex.

If you want to make dependent drop-downs using another way, check below resources:

- Cascading Drop-downs in Excel – using INDEX & COUNTA functions
- Multi-level option for older versions of Excel
- Using INDIRECT and named ranges [Contextures]

You can use a two-level data validation list instead. These are easy to setup and don’t require any complex formulas.

The post How-to create Dependent Drop Downs in Excel [Dynamic & Multiple] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, INDEX and MATCH worksheet formulas can transform you from normal excel user to a data processing beast.

Today, lets understand how to use these formulas better.

Here is the syntax for these three very powerful functions in plain English:

**VLOOKUP and MATCH are your way of asking excel to find a needle in haystack.** Imagine you have all your customer contact information in one sheet in the range A1:D5000 in the format phone number, name, city and date of birth. Now you need to find out which customer has the phone number “936-174-5910”. How do you do it?

You guessed it right, you use VLOOKUP and summon excel to do the search and return with customer name.

**While VLOOKUP is used to fetch value a based on what you are looking for, MATCH is used to fetch the position of the value you are looking for.**

See this illustration to understand :

Imagine you have a list of data like this:

Now, how do you answer the question – “*How many sales did Jimmy make?*“

Yes, your guess is right. VLOOKUP is one of the formulas you can use to answer questions like this.

VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.

So, in our case, we need VLOOKUP to search for Jimmy and return the amount of sales he made from column 3.

The syntax of VLOOKUP is simple:

`=VLOOKUP( this value, your data table, column number,`

optional is your table sorted?)

**Here is an example to get you started:**

Learn more about VLOOKUP Formula with examples

Please check out this page for **10+ examples of VLOOKUP** and how to use it to solve real world problems.

I have made a small excel file detailing 4 VLOOKUP formula examples. **The file also contains some home work** so that you can practice this formula.

**Download VLOOKUP Example Workbook**

If you are using Excel 365, you can use the new & improved XLOOKUP function. It offers a shorter & more versatile syntax for performing lookups.

For ex: the same lookup as above will be done with XLOOKUP like below:

=XLOOKUP(“Jimmy”, A2:A14, C2:C14) will lookup “Jimmy” in column A and return sales amount from Column C.

**Click here to learn more about XLOOKUP**.

**INDEX function is your way of telling excel to fetch a value from large range of values. **Since MATCH() function can tell us where the data is found, you can then use INDEX() function to extract corresponding data from another column. In this case, we can use MATCH() to find out which row has net sales 1,799 and INDEX() to return the name of the person. Like this:

Find the position of 1,799 in sales: =MATCH(1799, $C$2:$C$14, 0)

The answer will be * 8*.

To find the 8th person in names list, we can use INDEX() function like this:

=INDEX($A$2:$A$14, 8)

The answer will be **Jagjit.**

Related: Learn more about INDEX Formula.

Since MATCH returns the position of the item you are looking for in a list, you can then use this position in INDEX to fetch values surrounding the searched value.

So, we can combine both functions like this:

=INDEX($A$2:$A$14, MATCH(1799, $C$2:$C$14, 0))

This combination is called as INDEX+MATCH formulas.

Related: Using INDEX + MATCH functions & INDEX+MATCH Video

Remember, both VLOOKUP and MATCH throw a fail error of #N/A if the value you are looking for is not there. If you want to stop seeing the error, use IFERROR function.

Just use them with some dummy data, play around with arguments and see how you can say “*oh yeah, I can do that in few minutes*” to your boss next time.

Please watch this quick video tutorial to understand all these concepts and how to write VLOOKUP formulas easily.

If you want to learn VLOOKUP and other Excel lookup functions, then consider getting my VLOOKUP book.

- VLOOKUP formula with 10+ examples
- XLOOKUP – an improved version of VLOOKUP
- How to use INDEX formula
- How to use IFERROR to stop errors with VLOOKUP

The post VLOOKUP(), MATCH() and INDEX() – explained in plain English appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Do you want to manage your staff’s allocations, shift schedules and view the results in a 4 week grid fashion (like below)? Then you are going to love my FREE Staff Roster Excel Template.

- You can set up (up to 3) employees on shifts across a 4 week grid.
- See who is working on what days with staff pictures
- Manage employee’s time off and get alerted if you map someone on their day off.
- Look at staff allocation and workloads in a graph to see who is doing more and who is doing less work.
- Beautiful, simple and elegant worksheet to interactively manage the shift / roster.

This template is designed to work with modern Excel only. **You need Excel 365** or Excel on Web to use this file.

**Step 1: **Set up your employee data on the “employees” tab. You need the names & their pictures. When adding pictures, use insert picture > in-cell option to add them to the cells.

**Step 2: **Add any time-off or vacation information using the “Vacations” tab. Specify the employee name and date on which they are away.

** Step 3:** Set the start date for the roster, using the cell G3. *Optionally*, set the week start. Leave the value as 1 to start the week on Monday.

Once you change G3 value, the roster will display next 4 weeks worth of dates in the grid.

**Step 4: **Using the drop-down options in each day, map employees to the shift. You will get a warning icon if you either duplicate a name or if the employee is on vacation.

**Step 5:** Refer to the Workload graph on the right to see how busy your staff are. Use it to assign work fairly.

**Step 6:** That is all. There is no step 6. Happy Rostering.

Need help with rostering your staff or setting up employee schedules? Watch this tutorial to understand how I built this workbook and how to use it.

**Click here to download the Staff Roster Workbook**.

I have dozens of templates on all aspects of managing a business. Check out some of the below to speed up your work.

- Project Plan Template
- Talent Mapping Template (9 Box Grid)
- 2024 Calendar and Daily Planning Template
- To-do list template
- Household Budgeting Template
- Project Management Template Pack
- Ready to use Dashboard Template Pack

The post Beautiful Staff Roster Excel Template [FREE Download] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to calculate time between two dates in Years, Months & Days [Excel Formula] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.

So today let me share with you 35 shortcuts, productivity hacks and tricks to help you be even more *awesome.*

Writing formulas is a big part of analyst life. Use below tricks to cut the time you spend writing Excel formulas.

**Use F2 key**to edit any cell with formulas. This will put the cursor right the end of the formula.**Exploit intellisense**: Whenever you are typing a formula, Excel shows a list of possible functions / names that start with the same few letters you have already typed. Once the list is small enough, you can**use arrow keys (up / down) to pick the function or name you want and press TAB**to let Excel type the thing for you. This will dramatically speed up your formula writing process.**Corner click to auto-fill:**Once you have a formula, chances are you want to fill down that formula for rest of the table / range. To do this, just select the formula cell, double-click at bottom-right corner of selection. Bingo, Excel will auto-fill the formula all the way down (as long as there are values in adjacent columns).**CTRL+Enter to type same formula in a bunch of cells:**If you want to have same formula applied to a bunch of different cells, just select them all and type the formula. This will place the formula in top-left cell of the selection. Now, instead of pressing enter, press Ctrl+Enter. Excel will place the formula (and adjust any relative references) in all the cells.**Debug portions of the formula with F9 key**: When working with long formulas, often we come across situations when the result doesn’t make any sense. You can debug portions of such long formulas using F9 key. Just select the formula portion and press F9 to see the corresponding result. Once you are sure about the result, press ESC to revert to original formula.**Write plain English formulas with structural references:**Use tables and structural referencing to turn your =A2+ B2*C2 + D2 to [@[Fixed Cost]] + [@Units]*[@[Variable Cost]] + [@Commission] and make your workbooks readable (and maintainable). To convert your data to a table, press CTRL+T. Read more about structural referencing in Excel.**Setup calculations top-down:**If you have a big workbook with heaps of calculations, then set up your formulas from top down, such that formulas below refer to cells / calculations above. This speeds up workbook calculations and makes it easy to maintain.

Related: More formula shortcuts

Pivot tables are a big part of analyst’s life. Use below tricks to work faster with Pivot tables.

**Use ALT+N V to insert a pivot table quickly.**Or you can use the old school shortcut (from Excel 2003 days) – ALT + D P**Double click any value to drill down:**When looking at pivot tables, if you want to know which records correspond to a particular total, just double-click on the number. This will show a new sheet with only data for that number.**Rearrange your pivot table items by drag & drop:**Want to see a particular product name on top? Want to see the department list in a certain order? No problem. Simple select the items and drag and drop them in any order you want. This will re-arrange the pivot report the way you want.**Sort quickly with right-click:**Sort your pivot reports by simply right clicking on the value field and choosing sort option.

**Make Pivots presentable by renaming fields:**Do you know that you can type over the column / row headings in pivot tables? This makes them presentable and tidy. Give it a try. Read more about the tip here.**Master GETPIVOTDATA to make better reports:**Pivots are very powerful, but they carry a lot of baggage. If you want to harness the calculation might of pivots, but still use a friendly format in the output sheets, use GETPIVOTDATA. This can lookup in to pivot tables and give values you want. See below demo and Learn all about GETPIVOTDATA today.

Related: Pivot table tricks to make you a star at work

A good analyst must create user-friendly workbooks because a great deal of the job involves communicating with users. This is where ideas like data validation, form controls & slicers come handy. Here are few hacks to deal with such things faster.

**Multi-select slicer items by dragging:**To select multiple items on a slicer, simply drag from first item to last.*If the items you want to select are not together, hold down CTRL key and click on one slicer button at a time.*

**Set up form control linked cells faster:**To set up the linked cell for a form control, simply select the control, click on formula bar, press = and click on the cell you want to link. Done!**Cut and paste:**When setting up a complex workbook model, usually all the calculations are done in a separate worksheet tab. To speed up the process of setting up user interaction elements (such as slicers or form controls), first set them up in the calculation sheet. Once everything is working as per plan, just cut and paste them to the output sheet.**Alt + Down arrow to pick items from a validation / filter list:**Use ALT + down arrow key to pick items from a data validation drop down or filter cells.**Quickly clear filters with these shortcuts:**On a table or list, use CTRL + Shift + L to clear the filters or toggle them. On a slicer use ALT + C to clear the filter (ie select all).**Use timelines to filter date values:**Introduced in Excel 2016, timelines are a cool new way to filter date values. You can insert a timeline from Analyze ribbon or by right clicking on date columns in your pivot table filed view.**Set up hyperlinks to various parts of your report:**If you have a big report with many tabs, consider adding some hyperlinks so users can navigate easily. You can create a hyperlink from a drawing shape or cell. Use shapes or images for best results. Set up your shape, then press CTRL+K to open insert link box and select the sheet or range name to which user should go. Your hyperlink will be ready. Read more about hyperlinks.

*A good chart may get you that hike*. So it’s no wonder we, analysts spend a lot of time working on charts.

Here are few tricks to work with charts efficiently.

**Use arrow keys or TAB to select individual chart elements:**When working with charts, we have to select a chart element (bars, columns, titles, axes, legend etc.) before doing anything to it. To quickly select a chart element, simply activate the chart and use arrow keys.

**Adjust chart’s source data with drag and drop:**If you want to change a chart’s source data, simply use drag and drop. Select the chart series (for ex: in a line chart, select the line you want to change). This will highlight the source data range. Now using mouse pointer simply drag and drop the highlighted box to wherever you want. Done!**Use the select objects tool:**When working with multiple charts, often you may want to adjust settings for all in one go. Wouldn’t it be great if you can draw a box containing all charts and everything gets selected,*à la*Power Point (or image editing software)? Well, you can do that in Excel too. Simply activate select objects tool from Home > Find & Select > Select Objects.

In fact, I suggest adding this tool to quick access toolbar (right-click on the select objects tool and choose Add to quick access toolbar) so that you can fire it up when you want.**Link chart title etc. to cell value:**Default chart titles can be lame and boring. Create awesome titles (subtitles, captions etc. too) by using formulas. Then link them to chart title by using this simple trick. Select the title (or any other element), click on formula bar, press = and click on the cell containing your new title. Bingo, your chart now sports a context-sensitive, smart title. (Related: Give descriptive titles to your charts for best results | smart chart legends – how to?)**Add data to charts with copy paste:**Got a chart with sales trend for 3 products and want to add product 4 to it? Simple. Copy the data, select the chart, press CTRL+V. Tell Excel how you want this new data to be pasted and your chart is updated instantly.**Forecast with seasonality and trends easily:**If you have seasonal data, forecasting it seems like a tricky thing. Now with newly introduced FORECAST.ETS() function, this is super easy. See all about forecasting here and then read how to build a complex forecast chart.**Format a chart quickly with styles and themes:**Once you set up your charts, speed up the formatting process by using built-in themes and styles. Go to design tab to customize your charts in a jiffy.

It’s no good if you are productive. Your presentation skills are equally (if not more) important.

Let’s see some powerful formatting / presentation tricks.

**Format anything with CTRL + 1:**Simple, select the cell / chart / image / drawing shape you want to format. Press Ctrl 1. Format as you want.**Use alignment tools, you must. Hmmm:**

If your report has multiple charts (or shapes), then align them all, you must. Having perfect alignment doesn’t mean you waste several minutes nudging each chart in to right position. Simple select them all (using the select objects tool, of course) and fire up alignment tools from either Page Layout or Format ribbon. Align and space objects in a consistent way.*You can also hold ALT key when moving charts / shapes to align them with cell borders.***Repeat last actions with F4 key:**Let’s say you are changing font color for various chart elements. You can do this step once on something like vertical axis, then select other items and simply press F4. This will repeat your last action (*ie font change*) on the new selection.**Format once, paint many times:**Use format painter tool from Home ribbon to quickly apply format settings (including conditional formats) from one range to many. Works awesomely and saves you several precious minutes of formatting time. Double click on format painter to lock format painting mode. This way you can copy once and paste several times.**Add frequently used items to quick access toolbar:**Formatting tends to be a time consuming activity. To reduce the amount of clicks, mouse travel & un-necessary ribbon navigation, simply add all the frequently used formatting options to quick access toolbar.**Turn-off grid lines:**Get rid of them grid lines to instantly give your workbooks a professional & clean look. You can do this by going to View ribbon. While at it, consider turning-off formula bar & headings too if you find them intrusive.**Hide extra rows and columns:**To create a clean and polished look for your dashboard, hideaway all the unused rows and columns. Say, your dashboard is in A1:S80, then select row 81, press CTRL + Down Arrow and hide all these rows. Repeat the process for columns T onwards.**Format for print too:**Don’t forget that many users rely on print, pdf formats to consume information. So make sure your reports are formatted for printing. Start by setting up a print area. This ensures that only necessary information is printed. Also, disable print for screen features like form controls or slicers. You can’t select a slicer button on a print out. Read all about formatting for print.

Related: 8 tips to make you a formatting pro.

If your job involves data analysis & story telling (to be honest *all *jobs need these skills), then you are going to love **my Excel School Course. **This end-to-end program enables you to use Excel and get things done faster at work.

We are now open for enrollments. Sign up for the program to become awesome. Click here to know more.

The post 35 shortcuts & tricks to make you an #AWESOME Data Analyst appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Here is a fabulous New Year gift to you. A free 2024 Calendar & Activity planner made entirely in Excel. This dynamic calendar workbook lets you:

- See the calendar for entire year in a single view
- Dynamic
*any month*calendar with detailed plan 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 2024 calendar template.

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.

The calendar & planner file has 4 tabs.

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

The calendar workbook has two main components.

- Calendar
- Planner

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(366,,DATE(2024,1,1)) generates all the 366 dates in the year 2024.

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.

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.

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

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

]]>Bill Jelen is one of my most favorite people on earth. That is why I wanted to have him as my first guest when I restarted the podcast. Even though I recorded this few weeks ago, only now I got around to publishing it. Please enjoy the conversation with Bill.

More about Bill:

- MrExcel.com (Bill’s website)
- Bill Jelen on YouTube
- Excel eSports

The post CP05: Interview with MrExcel – Bill Jelen (on his incredible work ethic) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>How about a fun Excel challenge? I have data in below format in the table named ** t**rips

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.

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

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

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

]]>