- Excel School & Dashboards program – Save $30
- Excel School, VBA & Dashboards program – Save $50

Our holiday sale for 2016 is now live. **Click here** to purchase your favorite Excel, advanced Excel, dashboards & VBA courses at discount.

Two of our most popular, awesome & valuable courses are on sale.

- Excel School & Dashboards program – Save $30
- Excel School, VBA & Dashboards program – Save $50

*Our customers from India save Rs 1,200 & Rs 2,000 respectively.*

**In Excel School & Dashboards program, you will get**

- Detailed & Step-by-step training on all day to day aspects of Excel
- Tutorials & examples to make you advanced user in Excel
- Knowledge on how to use various important formulas, how to combine them
- How to set up tables, analyze data with pivot tables & do so much more
- How to create charts, how to add interactive, dynamic features to them
- Instructions on how to create world-class dashboards, MIS reports

Excel School program has more than 32 hours of video training, 50+ example workbooks & files, 1 year unlimited access so that you can become awesome at a pace that works for you.

**In Excel School, VBA & Dashboards program,**

- Everything in Excel School & Dashboards as mentioned above
- Detailed & Step-by-step training on VB language & macros
- Thorough understanding of VBA Eco system
- Dozens of examples on loops, conditions & common coding techniques
- Overview of SQL, Access & File systems
- Elaborate examples on user forms, MS Access integration
- Practical track with every day work problems & solving them with VBA
- Interesting class projects, homework to test your skills

VBA Classes includes more than 50 hours of video training, 100+ example files, macros & workbooks, 1 year unlimited access so that you can become a rock star in Excel, VBA & Dashboards.

**This holiday sale closes Friday mid-night** – that is 9th December, 2016 mid night – Pacific Time.

Go ahead and enroll in one of these courses. Become awesome in Excel.

**Click here for the holiday sale page.**

PS: This year alone, more than 1,000 people have enrolled in above 2 courses. You too can unlock the power of Excel & make difference in your work. Go ahead and join us.

]]>**And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.**

Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, *hence *a manager record).

**So how would you write the lookup formula?**

Let’s assume your data starts at B4 and goes for 200 cells (*ie *100 employee and 100 manager names), and the employee name you want to lookup is in cell F4.

We can find the position of the employee using below MATCH formula. (Array formula, so press Ctrl+Shift+Enter after typing it)

`=MATCH(F4,T(OFFSET($B$4,ROW($A$1:$A$100)*2-2,,1,1)),0)`

This will give us the position of employee (ie 3 for Andrea Nichols in the sample data image above).

Once we have the position, we can use below INDEX formula to get the manager’s name.

`=INDEX($B$4:$B$203,`

*position**2)

**How do these formulas work?**

The INDEX formula is fairly obvious. So let’s dig deep in to the MATCH formula.

**First we extract all the odd cells**in the range B4:B203 using`OFFSET($B$4,ROW($A$1:$A$100)*2-2,,1,1))`

part. The`ROW(A1:A100)`

portion generates an array of numbers from 1 to 100 which we then convert to even numbers using simple arithmetic.**We then use**to convert the odd cell values in to an array of text values.`T()`

formula- Finally MATCH() looks for the employee name in F4 against this list to find the matching position.

Please refer to OFFSET tutorial & INDEX formula tutorial to understand the syntax and array usages.

**How to make these formulas generic:**

In our formulas above, we use a fixed range A1:A100 to generate the even numbers. If you have different sized list, you can use below generic version of the ROW formula.

*Assuming your list is named list:*

Replace the ROW() formula above with below version:

`ROW($A$1:OFFSET($A$1,COUNTA(list)/2,,))`

**Click here to download example workbook for odd lookup problem**. Play with the formulas in cells F5 & F6 to learn more.

Bad data is everywhere. Recently, I have come across a data set that is precisely like this. I ended up using the above pattern to find the answer quickly.

* What about you? How would you lookup odd values? *Please share your approach in the comments section.

Don’t let dirty data drag you down. Check out below resources to learn more.

- Introduction to Excel VLOOKUP formula
- Multi-condition lookups
- VLOOKUP second or third (or nth) match
- Lookup the answer in matrix
- Lookup discounted pricing tier
- Range lookup – find out which range contains the answer

]]>

And you want this.

Except, there is a teeny tiny problem.

The sort order on the classification is all messed up.

**Here is a quick fix to get custom sort order on your pivot table row labels.**

OK, I lied, There are actually two ways to do this.

* Drag and drop the row labels to re-arrange them*. Pivot table will remember this order even when you refresh. Of course there is a downside. In case you add some new values in the row label area, they will be at the bottom.

- First define the sort order in a list. Let’s say this list is in $I$3:$I$12
- Now, add an extra column to the original data.
- Using MATCH formula, find the order of each row label (in our case, classification) in the sort order list. Assuming classification is in D3, use =MATCH(D3, $I$3:$I$12, 0)
- Create a pivot table with data set including sort order column.
- Add sort order column along with classification to the pivot table row labels area.
- Add the usual stuff to values area.
- Set up pivot table in tabular layout.
- Remove sub totals
- Finally
**hide**the column containing sort order. - Your new pivot report is ready.

*Good news for people with Excel 2013 or above:*

- Once you have the sort orders table, just link to your original data set thru data model.
- You can then use sort order column in the pivot report directly. No need to write MATCH() formula.
- Refer to our relationship advice for Excel 2013 or above users.

If all of this instruction is sort of tricky for you to follow, **click here to download the example workbook**.

If my data is in Power Pivot, I rely on the *excellent *sort by feature. It is a god send. But when my data is in Excel (or I can’t use Power Pivot), I rely on the approach outlined in this post.

**What about you? **How do you deal with messed up sort order problems in your pivot tables? Please share your tips in the comments.

Over the past 9 years Chandoo has written about 2,200 posts on all things Excel and Hui has contributed another 140 posts mostly targeted at the application of Excel techniques to real life situations.

But is this really what you want to see us write about?

So in this post we’re opening the floor to you, with a single simple question:

**What would you like to see discussed in future posts in 2017 at Chandoo.org ?**

Your ideas can be as specific or general as you like:

One Rule only: The Idea must involve the Functionality, Use or Application of Excel !

We cannot guarantee that your idea will result in a Post, But if you don’t ask, you won’t receive

We will do our best to schedule posts where most requested and suitable skills and time is available by authors.

**So, What would you like to see discussed in future posts at Chandoo.org ?**

Let us know what you’d like to see in future posts in the comments below:

]]>Yesterday, a friend asked me an interesting question. He has school distance data, like below. He wants to know which is the closest school for each school.

There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.

All the distance data is in a table named *dist. *

Assuming you have school names & types in cells H5, I5, we want to find out the closest school of any type and same type in adjacent columns, as shown below.

Let’s take a look at the formulas first. All of these are array formulas. So press CTRL+Shift+Enter after typing.

- J5:
**Closest School Distance (Any type):**=MIN(IF(dist[From]=H5,dist[Distance])) - K5:
**Closest School Name (Any type):**=INDEX(dist[To],MATCH(H5&J5,dist[From]&dist[Distance],0)) - L5:
**Closest School Distance (Same type):**=MIN(IF(dist[From]=H5,IF(dist[To Type]=I5,dist[Distance]))) - M5:
**Closest School Name (Same type):**=INDEX(dist[To],MATCH(H5&L5,dist[From]&dist[Distance],0))

Let’s examine them one at a time.

**Closest School Distance (Any type)**

**Formula: **=MIN(IF(dist[From]=H5,dist[Distance]))

**How it works: **

- We check if From school is same as the one in H5 and get the corresponding distances only.
- This will return a bunch of distances and FALSE values. Distances will be listed only for the schools that match H5, for all others, the IF() gives FALSE.
- We then pass this list to MIN formula to find the minimum distance.

As we are using arrays inside IF formula, we must press Ctrl+Shift+Enter to get correct results.

Related: Learn more about MAXIF & MINIF formulas.

**Closest School Distance (Same type)**

**Formula: **=MIN(IF(dist[From]=H5,IF(dist[To Type]=I5,dist[Distance])))

**How it works: **

- We check if From school is same as the one in H5 and if the [To Type] is same as I5 and get the corresponding distances only.
- This will return a bunch of distances and FALSE values. Distances will be listed only for the schools that match H5 and of type I5, for all others, the IF() gives FALSE.
- We then pass this list to MIN formula to find the minimum distance.

**Finding the corresponding school name:**

Once we know the minimum school distance, we just use array MATCH to find corresponding school number and get the name of the school with an INDEX().

=INDEX(dist[To],MATCH(H5&J5,dist[From]&dist[Distance],0))

As we are concatenating two lists in the MATCH formula, we need to press Ctrl+Shift+Enter to get correct results.

We use same logic to fetch school name for the distance in column L too.

Related: Learn about multi-condition lookups

While the formula approach gives answers we want, it is very tricky to write these formulas. The MIN(IF(…)) structure is not easy to master.

As the formulas check entire data, they can be very slow on large sets.

First create a pivot table from the *dist *table with below settings:

- Add From and From type to row labels area
- Add To and To type to column labels area
- Add distance to values area, summarize it by SUM
- Remove sub totals & grand totals
- Set up pivot in tabular layout

We get this.

At this stage, finding closest school gets easy. We simply use SMALL formula on each pivot table row to find 2nd smallest value (because smallest value is 0 and we should ignore it.) to get the distance. Finding school name is a simple matter of using INDEX + MATCH.

Of course, finding the distance for closest school of same type still requires using array version of SMALL with SMALL(IF(…)) structure. But this formula would be significantly faster as we don’t process all the 10000 rows of data.

Pivot table approach simplifies the problem and helps us answer the questions faster. You can also apply conditional formatting on top of Pivot Table to instantly highlight closest school(s).

**Click here to download the closest school example workbook**. Play with the formulas & pivot table to learn more. Examine the conditional formatting rules for some cool techniques.

By asking your neighbors, *of course*. Jokes aside, how would you find the closest school for a given school? Would you use formulas or pivot tables or some other approach? Please share your thoughts in the comments.

If you need to master Excel, look no farther. Excel School, your closest and most awesome online class makes you, well, awesome in Excel. Learn from basics to advanced concepts, all from the comfort of your office or home. There are over 50 lessons and dozens of sample workbooks to make you an Excel pro.

]]>Okay, I am kidding. That is not what Kristin said. Here we go again…

Let’s simplify Kristin’s problem.

You have some data in the format shown aside.

**Click here to download the sample file.**

And you want to find out the BP category for each reading, using below rules.

- Normal – SBP < 140 AND DBP < 90
- Grade 1 – SBP 140-159 OR DBP 90-99
- Grade 2 – SBP 160-179 OR DBP 100-109
- Grade 3 – SBP >= 180 OR DBP >= 110

*In case of ties, assume the higher grade.*

Use these references:

- B4 or [@DBP] for DBP
- C4 or [@SBP] for SBP

What’s a BP categorization problem with out some pressure. So time yourself when writing this formula. Try to complete it in 3 minutes and post your answers in the comments section.

**Your time starts now….**

Aah, you are the kind that strive under pressure. Here are a few more challenges to keep you busy.

- Sumerian voter problem – If formula challenge
- How many Mondays between two dates?
- Sum of Top 10 values
- Can you calculate sales commission?
*More home work problems*

If you find navigating the formula world confusing, murky and difficult, we got you covered. Check out my **Formula Crash Course** or **Excel School program**. Both of these offer excellent instruction on formulas for tremendous value for money. Your move, awesome.

But now, you want to add a line to it at 6:00 PM (or some other arbitrary point) so you can clearly see which superheros are over working.

So how do you go about it?

This tutorial assumes you have a column chart. If you have a bar chart, you should use vertical error bars in step 6.

- In a cell, enter the value for your line. Say this is in C15 (and it has 6:00 PM)
- Add this extra point to your chart. At this stage you will get this.

- Take a sip of that scrumpdillycious hot chocolate.
- Convert the new series to as XY chart. Just right click on it and select “Change series chart type”.
- Add error bars to this new dot

- Excel adds both vertical & horizontal bars. We just need horizontal line. So remove vertical (Y) error bars.

*Note: if you are adding the line to bar chart, remove X error bars instead.* - Set error bar value to 7 (same as number of categories – 1)

- Format the error bar
- Set error bar to plus side only
- Remove cap
- Set the line color and thickness
- Remove marker symbol (set it to no marker) so we have only line

- Chug the rest of your hot chocolate, we are done!

Watch this quick video tutorial on how to add a line to column chart to understand this process better.

*You can also watch this video on Chandoo.org Youtube channel.*

**Please click here to download example workbook. **Play with the input data to see the chart change. Try to recreate the chart from scratch to master this technique.

Unleash the super hero in you by learning few charting tricks and tips. Start with below, but be warned though. Everyone is going to love your charts.

- Tell all versions of truth in your dashboards
- Color changing line chart…
- Cropped chart – when some values are too big for it
- Use shapes to enhance your charts
- 15 quick & powerful ways to analyze business data

Using error bars to add a horizontal line (or vertical) is just one of the many ways to do this. * How would you add a line to column charts?* Please share your techniques in the comments section.