All articles with 'Learn Excel' Tag

This year, become a Very Table Genius

Published on Jan 11, 2018 in Learn Excel
This year, become a Very Table Genius

Let’s start the new year with a bang.

Excel Tables were introduced more than a decade ago, but a lot of people don’t know them or under utilize them. So start this year by becoming a very table genius.

What is Excel Table?

Excel tables are a simple and elegant way to structure and store your data. Let’s say you have staff details like below. Instead of calling it like A1:E72, you can convert this data in to a table and call it, you guessed it right, covfefe (or more coherent option like – staff).

Continue »

2018 Calendar, daily planner Excel templates [free downloads]

Published on Jan 3, 2018 in Learn Excel

Here is a New year gift to all our readers – free 2018 Excel Calendar & daily planner Template.

This calendar has,

  • One page full calendar with notes, in 4 different color schemes
  • Daily event planner & tracker
  • 1 Mini calendar
  • Monthly calendar (prints to 12 pages)
  • Works for any year, just change year in Full tab.
Continue »

5 conditional formatting top tips – Excel basics

Published on Dec 5, 2017 in Excel Howtos, Learn Excel
5 conditional formatting top tips – Excel basics

Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.

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 »

Which power plan is best for me? Excel for everyday problems

Published on Nov 9, 2017 in Analytics, Financial Modeling
Which power plan is best for me? Excel for everyday problems

We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?

Using Excel of course.

The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.

Continue »

Histograms & Pareto charts in Excel – tutorial, tips and downloadable template

Published on Oct 5, 2017 in Charts and Graphs
Histograms & Pareto charts in Excel – tutorial, tips and downloadable template

Time for some statistics and charting fun. Let’s learn all about histograms and Pareto charts in Excel 2016. You will learn

  • What, why and when?
  • How to set up and customize histograms
  • How to use Pareto charts?
  • How to create dynamic histograms?
  • Creating histograms in old Excel (2013 or prior versions)

Sounds interesting? Let’s get started then.

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 »

Convert unevenly spaced list to table [Data from Hell]

Published on Aug 30, 2017 in Power Query
Convert unevenly spaced list to table [Data from Hell]

Introducing Data from Hell:

Watch out, its data from hell. In this new video series, we are going to examine some nutty, frustrating and fun data reshaping challenges and solve them using Excel. We will use Power Query, Formulas, VBA or other features as needed to free this data from damnation.

For our first installment, let’s reshape unevenly spaced list of values to a table.

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 »

Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Published on Aug 17, 2017 in Charts and Graphs, R programming
Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Game for some charting awesomeness?

Off late, I have been doing a lot of data analysis and visualization on performance ratings, salary hike, gender pay equality etc. Today let me share you an awesome way to visualize massive amounts of data.

Scenario: Your organization of 3,686 people recently went thru annual performance ratings & review process. At the end of it, everyone was offered some salary increase (from $0 to $24,000 per year). You have 7 business groups. How do you tell the story of all these salary hikes in one chart?

How about the one above?

Ready to know how to create this in Excel? Read on.

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 »

Extract currency amounts from text – Power Query Tutorial

Published on Jul 27, 2017 in Power Query
Extract currency amounts from text – Power Query Tutorial

Let’s say you got some text values and want to extract the amounts from them. Something like above.

How to go about it?

We could use a variety of techniques to extract the values.

Continue »

Joyplot in Excel

Joyplot in Excel

Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.

Take a look at final outcome above. Read on to learn more.

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 »