All articles in 'Excel Howtos' Category

Copy Paste Visible Cells only (Two more ways to do it)

Published on Aug 28, 2017 in Excel Howtos, Keyboard Shortcuts, Learn Excel
Copy Paste Visible Cells only (Two more ways to do it)

Last week, we talked about how to copy and paste visible cells alone (ie exclude any filtered rows or hidden columns etc.) In the comments section many of you suggested two more ways to deal with this annoying problem. Let’s take a look them.

Continue »

Use File > Info to quickly unprotect multiple worksheets [Quick tips]

Published on Aug 1, 2017 in Excel Howtos
Use File > Info to quickly unprotect multiple worksheets [Quick tips]

Ever had a workbook with multiple protected worksheets? May be you are enterprise architect at Death Star or chief strategist at Mordor and got all the plans in a tidy little but protected workbook. Of course, you hate having to unprotect many of the worksheets every time you have a new evil plan for world domination. Don’t you worry, you can use this handy little trick to unproect en masse.

  • Just open the workbook
  • Go to File > Info
  • Right on the top, you can see all protected worksheets and a link to unprotect them.
  • Click to unprotect the ones you want to.
  • Done.
Continue »

Rounding time to nearest minute or quarter hour etc. [formulas]

Published on Jun 26, 2017 in Excel Howtos
Rounding time to nearest minute or quarter hour etc. [formulas]

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute.  We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

Use below formulas to round time in A1.

Continue »

Selective Sub-totals in Pivot Tables [Quick Tip]

Published on May 2, 2017 in Excel Howtos, Pivot Tables & Charts
Selective Sub-totals in Pivot Tables [Quick Tip]

Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like above.

How to show selective sub-totals in Pivot Tables

Continue »

Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

Published on Apr 26, 2017 in Excel Howtos, Learn Excel
Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

Imagine you are head of human resources at Casual Contracting Co. Every month you hire a lot of temporary staff who spend 1-4 months with CCC before leaving. Sometimes you hire the same people again. Of late, you have noticed a strange process gap. You are paying same person two (or more) salaries.

This is because you are hiring a person for new temp role even before their current one ended. See above picture.

So how to avoid making such hiring boo-boos.

Simple, using Excel of course.

Continue »

Relative References in Excel Tables

Published on Apr 21, 2017 in Excel Howtos, Learn Excel
Relative References in Excel Tables

Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.

While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.

Continue »

Hide columns one one tab same way as they were in another place [quick tip]

Published on Apr 5, 2017 in Excel Howtos
Hide columns one one tab same way as they were in another place [quick tip]

One of the regular reporting tasks I do involves a manual step I hated. It goes like this:

  • Dump several columns of data in the template file.
  • Hide a particular set of columns (these are not together, so must be done one at a time or with CTRL+selection)
  • Save and publish the file.

After doing this manually for last few fortnights, today I wanted to automate the column hide process. I was about to write a VBA macro to clone the hide settings from one workbook to another. But then I thought, may be paste special can be of use.

And what do you know. It does exactly that.

Continue »

Get rid of that ugly formatting with two simple tricks

Published on Jan 18, 2017 in Excel Howtos, Learn Excel
Get rid of that ugly formatting with two simple tricks

We are on a tiki tour around NZ. So far we have been to Taupo & Rotorua. And we are doing what you do when you are on a holiday – being lazy, going on walks, swimming in lakes, eating copious amounts of food and getting lost. Of course, all this means, I have very little time to access to internet & my blog. So the updates will be slow for next two weeks. Here is a quick tip (well, two of them) to keep you busy and awesome.

How to remove ugly formatting from your workbooks?

Do you have a colleague or boss (shudder) that loves to apply their special touches to every workbook their mouse lands on? Do you constantly wince and whine when you have to work on that spreadsheet.

Here are two handy ways to restore your data to its original glory.

Clear formats:

Simple, select the data you want formatting gone from, go to Home > Clear > Formats.

And Excel will weave an expelliformat spell at your data and make it clean.

Here is a quick demo.

Continue »

Untrimmable Spaces – Excel Formula

Published on Jan 12, 2017 in Excel Howtos, Learn Excel
Untrimmable Spaces – Excel Formula

Let’s talk about the untrimmable spaces.

We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.

So for example, if A1 has ” something and    one   more    ”

TRIM(A1)

will give “something and one more”

We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31).  Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.

The untrimmable spaces…?

The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”

Naturally I tried to TRIM().

But the data won’t budge. See above.

Hmm, let’s investigate why.

Continue »

Finding the closest school [formula vs. pivot table approach]

Published on Nov 18, 2016 in Excel Howtos, Learn Excel, Pivot Tables & Charts
Finding the closest school [formula vs. pivot table approach]

First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.

Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.

There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.

Continue »

Stacked Bar/Column Chart with Indicator Arrows – Advanced

Published on Sep 15, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar/Column Chart with Indicator Arrows – Advanced

Lets take last weeks Stacked Bar/Column Chart and add some high-performance steroids.

Continue »

Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Published on Sep 14, 2016 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 041 – Convert a Roman Numeral to a Number

Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.

Continue »

Stacked Bar and Indicator Arrow Chart – Tutorial

Published on Sep 12, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Stacked Bar and Indicator Arrow Chart – Tutorial

Learn how to develop a Stacked Bar chart with Indicator Arrow in this Tutorial

Continue »

Hourly Goals Chart with Conditional Formatting

Published on Sep 1, 2016 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui
Hourly Goals Chart with Conditional Formatting

A while back I developed a solution to a Chandoo.org Forum question, where the user wanted a 4 level doughnut chart where each doughnut was made up of 12 segments and each segment was to be colored based on a value within a range. If the values changed he wanted the chart to update, Conditional Formating like:
This post looks at how this was achieved.

Continue »

Add any number of days, months or years to a date with this simple trick

Published on Aug 2, 2016 in Excel Howtos, Learn Excel
Add any number of days, months or years to a date with this simple trick

Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

  1. =A1 + DATE(2,4,9)
  2. =EDATE(A1, 2*12+4) + 9
  3. =A1 + 2*365 + 4*30 + 9

Surprisingly, each formula gives a different result! So which one should you use?

Continue »