All articles with 'quick tip' Tag

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 »

Jo’s first keyboard shortcut

Published on Jun 12, 2017 in Keyboard Shortcuts

Jo, my lovely wife quit her job as my partner in crime at Chandoo.org recently and took up a lucrative position at NZ govt. agency. The other day I asked her “how was your day?” when she got home. She smiled and said, “I learned my first Excel shortcut!”.
Guess what it is?

F4.

That is right. The mighty F4 key. You can use it to repeat any action.

Jo was using it to insert rows in her workbook. After inserting first row (using CTRL+ of course), she would press F4 to add more rows as needed.

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 »

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 »

Use CTRL to make copies of worksheets quickly

Published on Feb 15, 2017 in Keyboard Shortcuts, Learn Excel
Use CTRL to make copies of worksheets quickly

The other day, I found myself making copies of a templated report worksheet. After trying the usual route of “right click on source sheet, select move or copy, check create a copy and press OK” a few times, I thought “well that is asinine.” So I figured, may be CTRL+Drag will create a copy. And what do you know, it does.

So that is our quick tip for the day. Whenever you need to make a copy of something, simply hold CTRL key and drag the thing.

It works for charts, drawing shapes, worksheets and even ranges.

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 »

Sorting to your Pivot table row labels in custom order [quick tip]

Published on Nov 29, 2016 in Pivot Tables & Charts

Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification.

And you want this.

pivot-table-row-label-order-incorrect

Except, there is a teeny tiny problem.

The sort order on the classification is all messed up.

Here is a quick fix to get custom sort order on your pivot table row labels.

Continue »

Find first & last date of a sale using Pivot tables [quick tip]

Published on Oct 26, 2016 in Pivot Tables & Charts
Find first & last date of a sale using Pivot tables [quick tip]

Here is a quick Pivot table tip. Let’s say your work at ACME inc. requires some fancy pants analysis of product sales. Imagine looking at below data & trying to find out the earliest & latest date for each product sale.

Of course, we can concoct a version of MINIFS & MAXIFS to answer the question. But why bother, when you can answer the question with just a few clicks.

Continue »

PSA: Don’t let auto correct spoil your party

Published on Aug 26, 2016 in Learn Excel
PSA: Don’t let auto correct spoil your party

So here is a news from strange but true department. Microsoft Excel blamed for gene study errors [bbc.com].

Microsoft’s Excel has been blamed for errors in academic papers on genomics.
Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.
Gene symbols like SEPT2 (Septin 2) were found to be altered to “September 2”.

Aah, classic!

This is what happens when you spend countless hours learning genome sequencing and very little about the software tools where your data goes. May be we need clippy back to warn people about such sticky situations.

Continue »

5 Shortcuts for you, one for every weekday [Awesome August]

Published on Aug 1, 2016 in Keyboard Shortcuts, Learn Excel
5 Shortcuts for you, one for every weekday [Awesome August]

Let’s kick start our Awesome August 2016 with a simple but very useful set of shortcuts. 5 shortcuts, one for each weekday.

  1. Monday ALT + M N: Open name manager. Very handy, if you have quite a few range names and want to edit / manage them. Remember, this is a sequence shortcut, that means, you press ALT M, let go of both keys and then press N.
  2. Tuesday CTRL + T: Create a new table from data in current region. For more on tables, check out our Introduction to Excel Tables page.
  3. Wednesday CTRL + W: Close the current workbook, while keeping Excel open.
  4. Thursday ALT + T O: Opens Excel options. Very easy to remember too.
  5. Friday CTRL F: Fridays can be hard to concentrate. Use CTRL+F to find what you want in the current workbook. Use CTRL+H if you wish to do a find replace.
Continue »

On / Off conditional formatting with this simple trick

Published on Jul 13, 2016 in Learn Excel
On / Off conditional formatting with this simple trick

Here is a quick & awesome way to make your dashboards sexy. Add an on / off switch to your conditional formats.

Take a look at above demo to understand what I mean.

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 »

Figure out slot from given time [quick tip]

Published on Apr 19, 2016 in Excel Howtos, Quick Tip
Figure out slot from given time [quick tip]

Here is an interesting scenario.

Let’s say you are looking at a time, like 9:42 AM and want to know which 15 minute slot it fits into. The answer is 9:30 – 9:45. But how would you get this answer thru Excel formulas?

Continue »

Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Published on Feb 26, 2016 in Keyboard Shortcuts, Learn Excel
Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Let’s say you have data in a worksheet in various ranges, and you want sum up each range at the bottom.

Something like this:

How to do all this one shot?

Simple. We use multi-select & ALT=

Continue »

Format charts quickly with chart styles & color themes [quick tip]

Published on Jan 27, 2016 in Charts and Graphs
Format charts quickly with chart styles & color themes [quick tip]

Here is a quick tip to reduce the time you spend on chart formatting – use chart styles & color themes.

Excel offers various pre-defined color schemes and chart styles. Using them is very simple.

  1. Select your chart
  2. Go to Chart Design ribbon
  3. Click on the style or color scheme you want.
  4. Your chart changes instantly.
Continue »