fbpx
Search
Close this search box.

All articles with 'quick tip' Tag

Quick tip: Rename headers in pivot table so they are presentable

Published on Mar 15, 2018 in Pivot Tables & Charts

Pivot tables are fun, easy and super useful. Except, they can be ugly when it comes to presentation. Here is a quick way to make a pivot look more like a report.

  • Just type over the headers / total fields to make them user friendly.

See this quick demo to understand what I mean:

So simple and effective.

Continue »

Filter all records for November or 11AM or 2017 [quick tip]

Published on Dec 4, 2017 in Quick Tip
Filter all records for November or 11AM or 2017 [quick tip]

Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?

Use filters of course.

Continue »

Conditional Rank, the easy way [quick tip]

Published on Oct 20, 2017 in Pivot Tables & Charts
Conditional Rank, the easy way [quick tip]

Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Continue »

VLOOKUP that fat table with ease [3 quick tips]

Published on Sep 18, 2017 in Learn Excel
VLOOKUP that fat table with ease [3 quick tips]

Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig. 

If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.

Continue »

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 »

Copy & paste visible cells only [Excel Trick]

Published on Aug 24, 2017 in Keyboard Shortcuts, Learn Excel
Copy & paste visible cells only [Excel Trick]

Here is something annoying with Excel.

Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.

What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.

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 »

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 »