All articles in 'Excel Howtos' Category

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 »

Find out how many times a value is present in a cell [formulas]

Published on Jul 19, 2016 in Excel Howtos, Learn Excel
Find out how many times a value is present in a cell [formulas]

Here is an interesting problem to start your day.

Let’s say you work as DNA sequencing engineer at The Enterprise. And you just unlocked the sequence that is responsible for all male problems. The early onset of baldness. The sequence code is AAAA. And you want to find out how many times this sequence is found in a sample of DNA strings, in the range B6:B19. Essentially you want the above.

So how do you write the formula?

Continue »

Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]

Published on Jun 8, 2016 in Excel Howtos, Learn Excel
Sum up neither “A” nor “B” values – How to use DSUM function in Excel [video]

We know how to use SUMIFS function to answer questions like, “What is the sum of values for ‘A’?”  But how would you answer questions like,

  • What is the sum of values that are neither “A” nor “B”?

We can still use SUMIFS, but it will get awfully long. So let’s turn our attention to other functions in Excel.

Continue »

Generating sequence numbers from cluster values [VLOOKUP to the rescue]

Published on Jun 2, 2016 in Excel Howtos
Generating sequence numbers from cluster values [VLOOKUP to the rescue]

Last night I got an email from Joshua, one of our readers with the subject – Hard Excel problem. Hard?!?, at this stage of summer, the hard problems seem to be (in no particular order),

  1. Lack of good quality mangoes to eat
  2. Intense heat and humidity
  3. Lack of good quality mangoes to eat

Yes, I like mangoes.

Any how, back to Joshua’s email, So I got curios and read it. He is facing a curious problem.

Continue »

Show more of your workbook on screens [quick tip]

Published on May 31, 2016 in Excel Howtos, Learn Excel
Show more of your workbook on screens [quick tip]

Ever wanted to show your workbook to someone and felt that you had less screen real estate? This tip will help you get more out of your workbook.

So how to get 50% more space for your workbooks?

Simple, just follow these steps.

Continue »

Excel Tips, Tricks, Cheats & Hacks – Readers Edition

Published on May 19, 2016 in Excel Howtos, hacks, Huis, ideas, Learn Excel, Posts by Hui, Quick Tip
Excel Tips, Tricks, Cheats & Hacks – Readers Edition

Over the last month we have seen some Excel Tips, Tricks, Cheats & Hacks presented by some of the best Excel practitioners on the internet.
In this final post of the series we highlight the Readers Contributions.

Continue »

How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Published on May 13, 2016 in Excel Howtos, Learn Excel
How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

Continue »

Excel Tips, Tricks, Cheats & Hacks – Readers Edition Prequel

Published on May 12, 2016 in Excel Howtos, hacks, Huis, Posts by Hui, Quick Tip
Excel Tips, Tricks, Cheats & Hacks – Readers Edition Prequel

Over the past 3 weeks we have been able to showcase some Excel Tips, Tricks, Cheats and Hacks from some of the best excel practitioners on the net.
But now it’s your turn…

Continue »