Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

All articles with 'spreadsheets' Tag

A Spreadsheet walks in to a bar … [open mic]

Published on Sep 7, 2012 in Humor

Lets have some fun this Friday.

Post your favorite Spreadsheet jokes using comments. Have a big smile.

I will start:

1. A pivot table walks in to a bar and orders a beer. It says, “Put me in the same tab, will ya?”
2. A slicer and pivot chart walk in to a bar. The bartender says, “look at those bast*rds, walking around with out a pivot!”.
3. Once Excel, Access and Windows were bragging to each other. Excel says, “I once crashed so hard, it took 5 minutes to recover”. Access says, “Oh thats nothing. I once crashed and took down an entire data base. It took them 30 minutes to recover”. Windows doesn’t say anything. Excel pokes him in the arm asks “what about you?”, Windows jolts & replies, “Sorry, what did you say, I just crashed again.”

Continue »

Sorting values in Olympic Medal Table style [Quick Tip]

Published on Aug 7, 2012 in Excel Howtos
Sorting values in Olympic Medal Table style [Quick Tip]

It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,

A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.

So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).

Continue »

What is so special about Go To Special? [15 tips]

Published on Mar 12, 2012 in Excel Howtos
What is so special about Go To Special? [15 tips]

We briefly covered Excel’s Go To Special function in the Managing Spreadsheet Risk series and in this post, we are going to explore Go to special feature in detail and learn how to use it.

What is Go To Special?

Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it. Read on…,

Continue »

Announcing Online VBA Classes from Chandoo.org, Please Join Today

Published on Jan 11, 2012 in products, VBA Macros

VBA Classes from Chandoo.org - Learn Microsoft Excel VBA & MacrosFriends & Readers of Chandoo.org,

I am so happy to tell you that our VBA Classes are now open for your consideration. Click here to know more & join us.

What is this VBA Class?

VBA Class is a structured and comprehensive online training program for learning Microsoft Excel VBA (Macros). It is full of real world examples & useful theory.

The aim of VBA Classes is to make a beginner an expert in VBA.

Read on to understand the benefits of this program & how to sign-up.

Continue »

8 Tips to Make you a Formatting Pro

Published on Dec 5, 2011 in Excel Howtos, Learn Excel
8 Tips to Make you a Formatting Pro

We can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

In this, you will learn how to
1) Use tables to format data quickly
2) Change colors of your worksheet in a snap
3) Use cell styles
4) Quickly clone formatting using format painter
5) Clear formats to begin with a clean-slate
6) Formatting shortcuts
7) Formatting options for print
and 8 ) Why you should not go overboard formatting

So go ahead and become a formatting pro.

Continue »

Compare 2 Lists Visually and Highlight Matches

Published on Oct 27, 2011 in Excel Howtos, Learn Excel
Compare 2 Lists Visually and Highlight Matches

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel.

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos. And you want to know whether all the queries are answered with a demo or not.

Continue »

Split an Excel File in to Many using VBA [Videos]

Published on Oct 19, 2011 in VBA Macros
Split an Excel File in to Many using VBA [Videos]

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps …,

Continue »

Quickly Fill Blank Cells in a Table [Reader Tip]

Published on Oct 17, 2011 in Excel Howtos
Quickly Fill Blank Cells in a Table [Reader Tip]

This post is authored by Martin, one of our readers.

Situation:

Sometimes I encounter data in my tables with blank cells where there is a repeated value from the cell directly above. See below:

This can be annoying when it comes to interpreting the data and when sorting columns.

Solution:

To know how I solve this, just read rest of the article.

Continue »

Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

Published on Oct 10, 2011 in Excel Howtos, Learn Excel
Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

Filtering is a simple yet powerful way to analyze data. When you apply filters to any list of values, Excel gives you some really useful pre-defined filters so that you can analyze the data in a variety of ways. So, assuming you have data like this: We all know how to filter data for Bananas. […]

Continue »

Sum of Values Between 2 Dates [Excel Formulas]

Published on Sep 27, 2011 in Excel Howtos, Learn Excel
Sum of Values Between 2 Dates [Excel Formulas]

Lets just say, you run a nice little orange shop called, “Joe’s Awesome Oranges“. And being an Excel buff, you record the daily sales in to a workbook, in this format. After recording the sales for a couple of months, you got a refreshing idea, why not analyze the sales between any given 2 dates? […]

Continue »

Refresh All Pivot Tables at once [Quick Tip]

Published on Sep 19, 2011 in Learn Excel, VBA Macros
Refresh All Pivot Tables at once [Quick Tip]

Pivot Tables are an integral part of Excel based reports. So it is no surprise that many of create multiple pivot reports in one Excel workbook. So when the underlying data changes, we often spend time refreshing individual pivot tables. Today, I want to share a quick tip about how to refresh all pivot tables […]

Continue »

Announcing Online VBA Classes from Chandoo.org, Please Join Today

Published on Sep 5, 2011 in products, VBA Macros
Announcing Online VBA Classes from Chandoo.org, Please Join Today

Dear Friends & Readers of Chandoo.org

I am happy to announce that our Online VBA Training Program is now open for your consideration. Please read this short post to understand the benefits of this program and how you can join. Click here to join our class, if you are ready.

What is this VBA Class?

VBA Class is a structured and comprehensive online training program for learning Microsoft Excel VBA (Macros). It is full of real world examples & useful theory.

The aim of VBA Classes is to make a beginner an expert in VBA.

Continue »

Interactive Dashboard in Excel using Hyperlinks

Published on Jul 20, 2011 in Charts and Graphs, Learn Excel, VBA Macros
Interactive Dashboard in Excel using Hyperlinks

Last week we learned how to create dynamic hyperlinks in Excel. Today, I want to show you something even cooler. An interactive dashboard based on hyperlinks, like shown above.

Isn’t it impressive?

Well, to create something like this, you don’t need a degree in advanced cryogenics. You just need a bunch of data, a chart, a one line macro code and some pixie dust (go easy on pixie dust).

Continue »

Sales Analysis Charts in Excel – 78 Alternatives

Published on Jun 30, 2011 in Charts and Graphs, Excel Challenges, Learn Excel
Sales Analysis Charts in Excel – 78 Alternatives

Recently, I ran a contest asking you to analyze a bunch of sales data and present your results in charts. We received a total of 78 charts from 45 people. The contest entries had a mind-boggling variety of excel charts, techniques and ideas. It took me a while to go thru all the files and compile the results. Thanks for your patience. In this post, you can find all the charts along with my comments & links to download files.

Continue »

Use Copy & Paste to Preserve References to Tables [Quick Tip]

Published on May 23, 2011 in Learn Excel
Use Copy & Paste to Preserve References to Tables [Quick Tip]

With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural references. That means, you can write easy to understand formulas like this,

But, there is a problem. When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

Well, there is a simple workaround for this problem

Continue »