All articles in 'Learn Excel' Category

I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.

Specifically, we will cover:

• Gathering and structuring Employee data in Excel
  • How to use Power Query to collect data
  • Polish / clean data in Power Query
  • Bring cleaner data to Excel as refreshable table
• Answering questions about employees
  • Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
  • Pivot tables for data analysis
  • Understanding the results quickly with conditional formatting
• Understanding pay gap
  • Calculating gender pay gap
  • Visualize pay gap
• Creating salary distribution charts
  • Working with histogram charts in Excel 2016 / Office 365
  • Making interactive charts
• Generating letters thru mail merge
  • Calculating employee bonus based on bonus mapping logic
  • Creating 100s of letters with a single click using Mail Merge + Word

Sounds interesting? Read on for details.

Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?

Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.

Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.

What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

So you have decided to up your game with Excel and / or Power BI this year and now ravenously looking for books to read. You have come to the right place. Here is my list of recommended best Excel books, and books on Power BI, visualization, dashboards, VBA, Macros and analytics.

Use below links to navigate the relevant section of this page:

• Excel Books
• Books for data & visualization
• Power BI Books
• Excel VBA Books
• My own books

Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.
In this article, you will learn:

• What is SUMIFS function and how to use it?
• Syntax for SUMIFS
• Using SUMIFS() with tables and structural references
• SUMIFS examples – simple, wild card
• Using SUMIFS() with date & time values
• Free sample file for SUMIFS formula
• More formulas for data analysis

Ever wanted to make a cool, snazzy interactive chart in Excel? Something like this:

In this tutorial, learn all about making your very first interactive chart. We use both formulas and pivot tables to build two versions of an awesome interactive chart in Excel.

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

I believe in frugal living and paying yourself first. One of the simple ways to achieve this is by using a budget. You know how much money you get. Once you can track (or estimate) how much you are spending, it is easy to see how much you are paying your future self and what wiggle room you have. So in the spirit of making you awesome in life, not just Excel, let me share a simple but elegant household budget spreadsheet.
Here is a screenshot of the budget.

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).

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.

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.

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.

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.

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.

When faced with tough problems I react in one of three ways

1. Come up with ingenious solutions
2. See if a simpler cheat solution is possible
3. Sit back and ignore

For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.

When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.