All articles with 'Learn Excel' Tag
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »