Today, lets tackle an interesting problem.

Lets say you are looking at some data as shown aside and wondering what is the sum of budgets for top 3 projects in East region with Low priority. How would you do that with formulas?

This article is inspired from a question asked by acpt22 in our forums.

Sum of top 3 values based on filtered criteria [video]

Watch below video to understand how to find sum of top 3 values using formulas & pivot tables.

Watch this video on our YouTube channel.

Download Example Workbook

Click here to download example file and play with it. Examine the formulas & pivot table settings to learn this technique better.

Do you calculate sum of top ‘n’ values often?

Often, I have to calculate sum of top ‘n’ values and I use SUMPRODUCT + LARGE combination. SUMPRODUCT (or simply SUM) is such a versatile formula that you could almost use it when your car breaks down on a free way.

What about you? Do you calculate sum of top ‘n’ values? Which techniques do you use? Please share using comments.

Learn more

If you sum & count for your living, then you are going to love below tips.

• Introduction to SUMPRODUCT formula
• How to find 2nd largest item meeting a criteria?
• Calculating average of top 5 values – howto?
• Sum of values meeting multiple criteria – SUMIFS formula

Then out of blue he said, he has a problem with his spreadsheet. In this own words,

When entering data for credit cards, I use one column per card. But in my report view, I want to show credit card details in rows. How do I do this?

Something like this:

Transposing values in a row to column using formulas

If it is a one time process, my friend can use Paste Special > Transpose feature and be done. But this is no one time business. So lets understand which formula helps us do this.

1. Lets assume original data is in $F$4:$J$5. Row 4 has card names & Row 5 has amounts.
2. Wherever you want the out put, just list running numbers (1,2,3….) in a column. Lets say these are in cells D10:D14.
3. To get the first card name, you can use the formula =INDEX($F$4:$J$4, $D10)
4. To get the first amount due, use the formula =INDEX($F$5:$J$5, $D10)
5. Now drag both these formulas down and you are done!

This is good, but I don’t like the extra column…

If that is the case, you can use the ROWS() formula to generate these running numbers for you on the fly. For example,

=INDEX($F$4:$J$4, ROWS($A$1:A1)) would work perfectly.

Learn more about: using ROWS / COLUMNS formula to generate running numbers.

Play with this formula

See the embedded Excel workbook below. Play with the formula.

(alternatives: download the example file or view it online)

How do you transpose values?

I love using INDEX formula. I use it for transposing values, tables, getting a cell value (or reference) from a large table, use it along with MATCH etc. It is a very versatile formula and I keep learning new uses for it.

What about you? Do you transpose values often? What formulas do you use? Please share using comments.

More on transposing your data:

If you like to transpose, wrestle or arm twist your data often, then you are at right place. Chandoo.org has tons of tutorials, material and tricks on this. Start with these:

• Transpose a table of values in Excel
• Transpose a table quickly with this simple trick
• Transpose data in charts

Also, check out more quick tips.

So it wont be an understatement when I say, I feel like a kid in candy store knowing that Bill Jelen aka Mr. Excel is just a few miles away from where I live. Since Rob Collie and Bill are good friends, I asked Rob if we 3 can meet for dinner. And Bill said yes. 

I am meeting Bill for dinner on Friday and Rob, Bill & I will be discussing spreadsheets, technology, share our experiences and bump ideas off each other.

So what is in it for you?

But I want you to be part of this too. So go ahead and  post your questions to Bill in comments. I am going to ask him all these and feature in an interview style post (with a photo Bill and me of course) next week. You can ask anything related to Excel, Bill’s life, mrexcel.com or general stuff. Hilarious, whacky, outright fun questions are welcome.

Bonus: Signed copy of Bill’s book

I will pick one random commenter (on this post) and send her / him a signed copy of one of Bill’s books. I am trying to get Excel 2013 in-depth, but the title may change if we cant get hold of a copy in time.

The book will be autographed by Bill Jelen, Rob Collie and yours truly.

I am sure as a Chandoo.org visitor, you know the value of this. Just in case you don’t, here we go: You might be able to sell it for millions if the world becomes spreadsheet oriented as we approach singularity

Go ahead and ask your questions

Go ahead and ask your questions thru comments. (Click here to comment)

My travel to US was fantastic and fun. Many thanks to Jocelyn & Rob Collie, who found a nice apartment for us and made sure we are settled down in almost no time. Weather has been excellent so far too with lots of sunshine and blue skies. We went to a nearby park the other day and kids really loved walking on trials and exploring.

Gentle reminder

Today is last day to sign-up for my USA Masterclass

Today is last day to join my USA Advanced Excel & Dashboards masterclass with early bird discount. If you live near Chicago, Washington DC or Columbus and love Chandoo.org style of teaching, then you will benefit alot from my masterclass. We have very few spots left in Chicago & Columbus. So go ahead and book yours today and save $200.

Click here to know more and join us.

Excel links – after a long time

After a long time, I have a few more Excel links to share. Check out these for useful Excel tips, information & ideas.

Neat compilation of Excel keyboard shortcuts

Dave & Lisa (his wife) created this beautiful, searchable, PC & Mac compatible list of Excel keyboard shortcuts. Check it out to learn something useful. [More keyboard shortcuts]

Create small multiples (panel charts) automatically [Excel workbook]

James sent me this beautiful Excel VBA file using which you can generate small multiples chart from a large, cluttered chart. You just enter all the data, create one big chart, tell how you want your small multiples and press a button – bingo, your small multiples are ready.

Making pretty annual report in Excel

Joey at Darkhorse analytics discusses how we can create a beautiful annual report using Excel alone and why it is important to make your work look pretty as well. Check out the downloadable file and see it yourself. PS: You need install a font if you want to see it perfectly.

Building waffle charts in Excel

Mike shows us how to create waffle charts, which are nothing but square pie charts. Very interesting technique. (You can create waffles with conditional formatting too.).

Summing data across multiple sheets with multiple criteria

Liam shows us how to calculate sum of all values across multiple sheets that meet a given criteria using INDIRECT formulas, tables and Excel trickery.

PS: here is a pic of Chandoo family in Chagrin falls park in Cleveland
PPS: If you live in Cleveland or near by shout out in comments. We can catch up for some coffee next week.

It sounded like a good challenge for a lazy Sunday morning. So here we go. (Posting it on Monday).

Now I could not get an oil rig photo or that data. So I made up few numbers and used a photo of Flinders street station I took when I was in Melbourne last year.

Step 1: Arrange the data.

Arrange the data like this.

Step 2: Create a column chart

Select the data, insert a stacked column chart (why not a regular column chart?, you will understand in a minute).

You will get this.

Step 3: Set up image as background for chart’s plot area

Select chart’s plot area. Press CTRL+1.

Choose picture or texture fill and select the file with image you want.

Step 4: Add dummy max-series

In your data, add a column which gives the difference between column values & axis maximum. For our test data, I choose 1,400 as axis maximum, so the dummy series values are,

Now add this series to chart.

Step 5: Format the chart

Now, we are almost done. Our chart looks like below. We just need to format it.

1. Select the columns (any series) and press 1
2. Adjust gap width to 0%
3. Fill the dummy series with a chosen background color.
4. Make the data series transparent (fill color = no color)
5. Add borders to data series. Border color should be same as background color.
6. Adjust the border thickness to 3pts.
7. Adjust axis maximum to 1,400 (or any value you have selected in Step 4).
8. Remove grid lines, legend and any un-necessary chart fluff.

Your column chart with background image is ready!

Note of caution: Go easy with images

The main purpose of a chart is to convey information. By adding a background images, sometimes your chart will be difficult to read. So I suggest you to go easy with background images.

Download Excel workbook with this chart

Click here to download Excel file with this chart and play with it. Examine the chart formatting settings to understand this technique better.

Do you use background images in your charts?

I confess. I rarely use this technique, because finding good background image that can reveal data is very tricky. The oil rig image is an excellent choice. Also, certain things like drop-shadow for data series are not possible with our technique (as gap width = 0%). But I enjoyed figuring out this puzzle.

What about you? Do you use such charts? Please share your tips and techniques using comments.

As I pack our bags and run the last checks, one thought keeps crossing my mind. ‘How impossible all this sounded just a few years ago’.

When I quit my well paying corporate job in April, 2010 to embark on this exciting journey of making you awesome in Excel, I never thought I would one day take my family and visit USA. I had bigger worries. I just became a dad to twins. Both Jo & I quit our jobs. We had no home, car or reasonable financial backup. Just a passion to do what I loved – share Excel knowledge, ideas with the world and somehow make a living out of it.

What followed was almost a dream journey, thanks to your generous support to Chandoo.org. You became a part of our little community, learned Excel thru our site, purchased our products, spread good word about us among your friends & colleagues.

Thank you

I just want to thank you for your continued support to Chandoo.org. With out that I would not even have the courage to dream about conducting Excel workshops in USA.

You inspire me to learn and share

You invite me in to your work & life

You ask questions, make comments

You give me new opportunities

You take time to learn and be curious

You give me a purpose and make me strong

Thank you.

Shout out to our readers in Chicago, Columbus, Cleveland, Washington DC:

I am really eager to meet you and thank you for all that you do to support us. If you are interested, stay tuned. I will post details about this in a few days.

PS: We have a few spots in my Masterclass for Chicago, Columbus & Washington DC. Click here to book yours.

Watch below to video to understand what these mistakes and how to avoid them.

10 Rookie mistakes to avoid when making dashboards [23 mins]

More resources on Excel dashboards

Excel dashboards – Information, examples & tutorials
Creating a KPI dashboard using Excel – 6 part tutorial
Sales dashboards – 32 examples & downloads

Do you make any of these mistakes?

When I made my first dashboard in Excel (a balance scorecard for our department), I made almost all of these mistakes. Today, I am more careful. But I still forget to do print settings or make small mistakes when I create dashboards.

What about you? What mistakes are you making when you construct dashboards? Please share using comments.