All articles with 'goto special' Tag

CP021: How to quickly compare 2 lists in Excel

Published on Oct 2, 2014 in Chandoo.org Podcast Sessions, Excel Howtos
CP021: How to quickly compare 2 lists in Excel

In the 21st session of Chandoo.org podcast, lets compare lists. Quickly

What is in this session?

Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.

Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.

In this podcast, you will learn,

  • Why I sound like I am on a secret mission at a mafia hideout.
  • 5 ways to compare 2 lists
    • Manual method
    • Conditional Formatting
    • Row Differences
    • LOOKUP formulas
    • COUNTIF formulas
  • Bonus tip: Removing duplicates
  • Conclusions
Continue »

CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO

Published on Jul 24, 2014 in Chandoo.org Podcast Sessions

In the 15th session of Chandoo.org podcast, lets answer some of your burning Excel questions.

Handling big data, Controlling model railroad sets, Overcoming Excel obsession & More - ASK CHANDOO

What is in this session?

Around last week, I invited you to ask me anything. More than 150 people responded to this call and sent in their questions. Since answering all the questions is not possible, I handpicked roughly 10 questions to answer in this episode of Chandoo.org podcast.

In this podcast, you will learn,

  1. How to fill blank cells with data from above
  2. How to work with Big data in Excel
  3. How to combine data from multiple sources & analyze it in Excel
  4. How I am managing my life after starting Chandoo.org
  5. How to create and distribute stand-alone Excel products
  6. How to control a model railroad set using Excel VBA (not fully answered)
  7. & more…
Continue »

Find first non-blank item in a list with formulas

Published on Jan 15, 2014 in Excel Challenges, Excel Howtos
Find first non-blank item in a list with formulas

Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:

Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?

Continue »

Sporadic Totals in Excel [video]

Published on Dec 18, 2013 in Excel Howtos
Sporadic Totals in Excel [video]

If this Excel problem is a Bollywood (Indian movie) plot, it would go like this:

Situation: Your boss gave you a worksheet. It has a lot of number chunks. And you need to calculate the sum of each chunk. Quickly!

Twist #1: The villain (your boss, who else) has abducted your spouse. For every extra hour you spend on the problem, your boss will make your spouse go thru one of the boring 97 slide strategy presentations. And his laptop is full of those strategy presentations.

Twist #2: The F1 key on your keyboard is missing.

Twist #3: The coffee machine in your floor is broken again.

Twist #4: And just when you are pressing CTRL+S, the movie steers in to an item song.

—-

Fortunately, no one abducted your spouse. And hopefully the coffee machine is working. But the Excel problem remains unsolved.

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 »

Excel’s Auditing Functions [Spreadsheet Risk Management - Part 3 of 4]

Published on Jan 18, 2012 in Financial Modeling, Learn Excel
Excel’s Auditing Functions [Spreadsheet Risk Management - Part 3 of 4]

This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit Part 1: An Introduction to managing spreadsheet risk Part 2: How companies can manage their spreadsheet risk Part 3: Excel’s auditing functions Part 4: Using external software packages to manage […]

Continue »

Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip]

Published on Jan 9, 2012 in Excel Howtos
Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip]

Here is a quick Excel tip to kick start your week.

Sometimes, we want to enter same data in to several cells. You can use CTRL+Enter to do this in a snap.

(1) Select all the cells where you want to enter the same data.
(2) Type the data
(3) Press CTRL+Enter
(4) Done!

See the animation aside to understand how this works.

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 »

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 »

Quickly Compare Data using Row Differences

Published on Feb 14, 2011 in Excel Howtos
Quickly Compare Data using Row Differences

Lets say you have some data in 2 columns and you want to compare row by row to spot the differences. Of course you can write a formula or apply conditional formatting. But there is a quick and dirty solution that works just as fine.

Continue »