Category Archives: Tutorials

How to Convert Weekly Data into Monthly Data in Excel

Data comes to us in many forms, and often our biggest challenge is translating it from the form it came in into the form we need it in. Date-based data is especially challenging – there are days of the week, weekly totals, months with different numbers of days, and holidays that land on different weekdays each year. This article is part of a series that will help you work with date-based data in Excel to get it into the formats you need.

This tutorial will teach you how to convert weekly summary data into monthly total data by allocating the days in each week to the appropriate month of the year. Let’s dive in!

Continue reading How to Convert Weekly Data into Monthly Data in Excel

How to Lock Cell Formula References for Table Columns in Excel

One of the most powerful features of Excel formulas is the ability to create absolute references that don’t move around when you drag to extend cell formulas or copy them to different places in your spreadsheet. Most Excel users figure out how to lock these references by either toggling through the options with the F4 key or using the $ (dollar sign) symbol in front of the column and/or row references.

When you start using data tables in Excel, many users are frustrated to discover that the normal methods for freezing the row or column references don’t work! This can make it time consuming to build your spreadsheets when they use tables. Fortunately, there are some workarounds! We’ll walk through a few of them in this quick tutorial…
Continue reading How to Lock Cell Formula References for Table Columns in Excel

How to Unpivot Columns into Rows in Excel Using Power Query

Unpivot Logo LeadWe often use Pivot Tables in Excel to make large data sets easier to read. Pivots let us organize huge flat files by putting dates or categories into their own columns, making them easier to read.

Sometimes, though, we need the exact opposite format for our data. If you receive data that is already pivoted and need it converted from columns back into individual rows, you’ll need to unpivot the table to get your results. Fortunately, Microsoft has a very powerful free plug-in for Excel called Power Query that makes this very easy to do! This is a tutorial to walk through how to unpivot existing data using Power Query.

Continue reading How to Unpivot Columns into Rows in Excel Using Power Query

Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

FasterVLOOKUPLeadWith the combination of INDEX and MATCH functions and Excel’s powerful array formulas (entered using CTRL+SHIFT+ENTER), we can already make Excel do the hard work of looking up data with multiple criteria for us. I wrote about it in the article How to VLOOKUP with Multiple Criteria Using INDEX and MATCH, and you should definitely check it out first if you need to use multiple conditions when looking up data in Excel.

The only downside of the INDEX/MATCH method is that it takes a lot of crunching to get its results. If you just need to look through a couple hundred, it does a great job! If you’re working with truly big data sets numbering in the thousands or hundreds of thousands of rows, you might need something that can work faster… This tutorial will show you how to combine CONCATENATE and a helper column to make VLOOKUP faster than ever!

Continue reading Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE

How to Calculate Percentiles and Conditional Ranking Using SUMPRODUCT

Conditional Rank LeadThe Excel functions for performing ranking and establishing percentiles are poorly described and confusing to use on the best of days. The PERCENTILE function doesn’t give the percent ranking of the item, but the instead the value at a given percentile (which might not even exist!). This makes it difficult to calculate even simple percent rankings in Excel. But what if you want to rank a sub-set of a list based on some criteria? In this tutorial, we’ll walk through the challenges of calculating percentiles and ranking values based on filtering criteria using a basic example.

Continue reading How to Calculate Percentiles and Conditional Ranking Using SUMPRODUCT

How to SUM Only Visible (or Filtered) Rows Using SUBTOTAL

SUBTOTAL LeadExcel’s basic functions, like SUM, AVERAGE, COUNT, MIN and MAX are indispensable for harnessing the power of spreadsheets, but they don’t always work well with filters and structured reports. By default, these functions tally every cell in their ranges, regardless of whether the cells are filtered or hidden. Many times in reporting, it is useful to limit functions to only consider rows that are visible on the sheet and ignore the hidden or filtered values. SUM, AVERAGE, and the like can’t do this on their own. Fortunately, Excel has a function called SUBTOTAL that will consider only visible or filtered rows in its calculations, and it can perform all the operations mentioned and more! This tutorial will walk though the use of the SUBTOTAL to sum a filtered data table… Continue reading How to SUM Only Visible (or Filtered) Rows Using SUBTOTAL

How to Show Percentages in Stacked Bar and Column Charts

Stacked Chart Percentage LeadExcel’s Stacked Bar and Stacked Column chart functions are great tools for showing how different pieces make up a whole. Unfortunately, the are somewhat limited, since they don’t automatically provide totals for the stack, and they don’t let you show the percentage contribution that each piece provides to the whole (like you can with pie charts in Excel). The good news is, there are work-arounds for displaying total volumes or dollars at the top of a stacked chart and percentages for each of the pieces. This tutorial will walk through the steps to get you results like you see in the image.

Continue reading How to Show Percentages in Stacked Bar and Column Charts

How to Add a Vertical Line to a Horizontal Bar Chart

Vertical Line Horizontal Bar ChartLeadExcel’s built-in chart types are great for quickly visualizing your data. The horizontal bar chart is a great example of an easy to use graph type. Sometimes, though, it can be useful to call attention to a particular value or performance level, like an average or a min/max threshhold. In that case, you’ll want to add a vertical line across the horizontal bars at a specific value. This quick tutorial will walk through a quick way to add a vertical line to the horizontal bar chart type in Excel. As an example, we’ll use annual sales performance with an average value line. Continue reading How to Add a Vertical Line to a Horizontal Bar Chart

Build a Better, Cleaner, More Professional Line Chart


Better Line Chart LeadExcel has some great basic charts and graphs build in, which makes it easy to build visualizations of your data. They are functional, but they don’t give a very professional look to your data. To make your presentation, website, or sales pitch really make a good impression, you’ll want to find a way to improve on the default charts. Even a basic line chart can be given a cool makeover with better colors, axes, and  shaded background to make it stand out. These touches can give the boring old graphs a fancy new look. Here’s how to make a better line chart in Excel…
Continue reading Build a Better, Cleaner, More Professional Line Chart

How to Calculate Net Work Hours Between Two Dates

Net Work Hours LeadCounting the number of hours between dates and times in Excel is normally a straightforward process. Since Excel stores dates as decimal numbers, you can just subtract the two to get your result. But when you are working with business hours, like for time sheets or hours worked, you need to take weekends and holidays into account. Excel has a function called NETWORKDAYS, but this only works with complete days. To calculate the Net Work Hours between two dates, we need to build our own formula. Here’s how… Continue reading How to Calculate Net Work Hours Between Two Dates