All articles with 'Learn Excel' Tag

How to Get “Either-Or” Condition in Data Validation?

Published on May 4, 2010 in Learn Excel
How to Get “Either-Or” Condition in Data Validation?

In a recent consulting assignment I had a tricky data validation problem. The customer wanted to have an either-or condition in the data validation, like this: My initial reaction to this requirement was “hmm… that is not possible“. But before shooting the email back to client, I got curious and checked if excel data validation […]

Continue »

Excel Links – Project Management Edition

Published on May 3, 2010 in excel links

It has been quite a while since we had an excel links post. It doesn’t mean that I am not reading anything new on excel or charting. I have been reading lots of awesome articles and useful content and sharing it on my google reader recommendation list. But it is just that I have been […]

Continue »

Quarterly totals when you have multi-year data [SUMPRODUCT again]

Published on Apr 30, 2010 in Excel Howtos, Learn Excel
Quarterly totals when you have multi-year data [SUMPRODUCT again]

In yesterdays post – Find Quarterly Totals from Monthly Data, we have learned how to use SUMPRODUCT formula to find totals by Quarter from a set of monthly values. The approach is fine, but has one glaring draw back. It only works when you have data for one year. In that post, Martin said, … […]

Continue »

Find Quarterly Totals from Monthly Data [SUMPRODUCT Formula]

Published on Apr 29, 2010 in Excel Howtos, Learn Excel
Find Quarterly Totals from Monthly Data [SUMPRODUCT Formula]

Here is a problem we face very frequently. You have a list of values by months. And you want to find out the totals by Quarter. How do you go about it? There are 2 options: You can make a pivot report from the data and then group dates in that to find totals by […]

Continue »

SUMIF works in 2D too [quick tip]

Published on Apr 27, 2010 in Learn Excel
SUMIF works in 2D too [quick tip]

We all know that SUMIF formula can be used to find the sum of values meeting a criteria. Like this, But I was pleasantly surprised to realize that SUMIF works equally well for 2D ranges too, like this: During a recent consulting work with a client I had a requirement to sum up values that […]

Continue »

What are your favorite colors for charts?

Published on Apr 23, 2010 in Charts and Graphs
What are your favorite colors for charts?

Ok, it is Friday again. That means poll time. I would like to know what color choices you prefer for your charts?
My Favorite Colors for Charting are,

These are the colors I use when making charts in Excel 2007 (it has capability to set any color for chart elements).

Continue »

Making a Dynamic Dashboard in Excel [Part 3 of 4]

Published on Apr 22, 2010 in Charts and Graphs, VBA Macros
Making a Dynamic Dashboard in Excel [Part 3 of 4]

In this post we are going to look at a simple example of the VBA behind the Dynamic Dashboard. Essentially we will learn to write macros for doing this. Read the rest of this post to find code samples and downloadable files to play with.

Continue »

Budget vs. Actual Profit Loss Report using Pivot Tables

Published on Apr 21, 2010 in Learn Excel, Pivot Tables & Charts
Budget vs. Actual Profit Loss Report using Pivot Tables

This is continuation of our earlier post Preparing Quarterly and Half yearly P&L using grouping option. You can also do budget v/s actual comparison using Pivot Tables. For this we will use calculated items feature of Excel PivotTables.

To begin, we have to add one more column to our data. I have added column Data Source to the end of data table. Existing data is marked as Actual and I have added more data rows which are marked as Budget. You can download new file with updated data and basic Pivot P&L.

Continue »

Introduction to Excel SUMIFS Formula

Published on Apr 20, 2010 in Learn Excel

If SUMIF formula is like a regular knife that you can use to slice up your data and get what you want, then SUMIFS is like a Swiss army knife. What is SUMIF? We all know that SUMIF formula helps to findout the sum of all values meeting a condition. So, if you have data […]

Continue »

How to Select Right Chart for your Data

Published on Apr 19, 2010 in Charts and Graphs
How to Select Right Chart for your Data

Bar chart or Line? Scatter plot or box plot? These are the questions we ask ourselves when we set out to make a chart. Because, “Selecting right chart for our data” is very important to tell our story.

In this article, we will learn how to “select the right chart” based on our data and situation.

Continue »

Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

Published on Apr 15, 2010 in Excel Howtos
Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

These days I rarely use Excel 2003. But when I do open the trusty old software, I always look for opportunities to improve my productivity with it. And I am pleasantly surprised to find a shorter and faster way to turn off grid lines on spreadsheets in Excel 2003. (I like my spreadsheets without any […]

Continue »

Updates on Excel School [and 3 free lessons]

Published on Apr 14, 2010 in Learn Excel, products
Updates on Excel School [and 3 free lessons]

Remember Excel School? It was all I talked about between Jan last week and middle of Feb. Then as if someone has pressed mute button, there was not even a single mention about the school. So I thought, why not give you all an update on Excel School and tell you how the classes are […]

Continue »

Excel Formulas are not working?!? What to do when all you see is the formula, not result

Published on Apr 12, 2010 in Excel Howtos
Excel Formulas are not working?!? What to do when all you see is the formula, not result

Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. But nothing. The […]

Continue »

Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]

Published on Apr 9, 2010 in Charts and Graphs
Survey Results in Dot Plot Panel Chart [followup on Incell Panel Chart]

In a follow up to last week’s how to visualize survey data in excel, we will explore how you can create an incell dot plot panel chart. Please follow the links in the article to get more commentary and insights from established persona in the visualization world (including Stephen Few, Naomi, Jon etc.)

Continue »

Make your Chart Legends Legendary

Published on Apr 8, 2010 in Charts and Graphs
Make your Chart Legends Legendary

We all know that legend can be added to a chart to provide useful information, color codes etc.

Today we will learn how to make the chart legends smarter so that they provide more meaning and context to the chart, like this:

This type of legend can be more useful than a plain legend as this provides more useful information without taking up too much space.

Continue »