Jul 27, 2020
Unlinking A Pivot Table From Its Source Data
You may have a situation in which you need to send someone a pivot table summary report, but you don't want to include the original data. In other words, you want to [...]
May 21, 2021
Remove those rogue records in Excel
When lists of data are merged, duplicates can be introduced. Fortunately, Microsoft Excel has a Remove Duplicates feature that will eliminate these rogue records. All [...]
May 18, 2021
Find a filter result without filtering in Excel
You may remember a previous blog post of mine about advanced filters previously. I recently had a student in one of our Excel Courses who was interested in filtering and [...]
May 08, 2021
Round, RoundUp and RoundDown in Excel
This article explains the three functions to round numbers in Excel - the ROUND, ROUNDUP and ROUNDDOWN function. Note: Before your start, if you round a number, you [...]
May 05, 2021
Group data in ranges of values in Excel
Let’s assume you have a column full of numbers, as shown in Figure 1 below. Let's say you need to visually group the Quantity column into 4 groups or ranges as [...]
Apr 17, 2021
Create a SharePoint list based on an Excel spreadsheet
You can create a list in Microsoft SharePoint by importing an Excel spreadsheet file, if your data is already in a spreadsheet format. When you create a list from a [...]
Apr 07, 2021
Copy visible cells only in Excel
I am often asked why it is that sometimes when you copy and paste a filtered or subtotaled range of cells in Microsoft Excel, they expand and paste even the hidden [...]
Mar 31, 2021
How to create custom lists in Excel
Did you know that Microsoft Excel is very smart? If you type the name of the month (e.g. January), it knows automatically what comes next in the series so that if you [...]
Feb 03, 2021
Reverse engineering a nested formula in Excel
Sometimes, understanding how a nested formula in Microsoft Excel calculates the final result is difficult because there are several intermediate calculations and logical [...]
Jan 27, 2021
Three really handy Excel keyboard shortcuts
Here are three keyboard shortcuts I find really handy in Microsoft Excel. These shortcuts each do something very useful they and are often hard to achieve using the [...]
Dec 16, 2020
Excel formulas are not just for numbers
I had a student recently say that she "didn't need to know formulas in Excel" as she "didn't work with financial spreadsheets." Formulas, however, do a lot more than [...]
Nov 24, 2020
Comparing and combining two lists using VLOOKUPs
You may remember that in my last blog post, I talked about how to do a VLOOKUP in Microsoft Excel. Today, we are going to use VLOOKUPS to compare and combine two lists [...]
Nov 14, 2020
Charts that aren't charts
In Microsoft Excel, there are a couple of ways to create a 'chart that isn't a chart.' Each produces a graphical representation of data, but using different [...]
Oct 31, 2020
Doing a VLOOKUP that finds exactly what you want
A VLOOKUP in Microsoft Excel looks up and finds a piece of information in a list, a list like the one below: The V in VLOOKUP stands for vertical because you are [...]
Sep 26, 2020
Dynamic charts in Microsoft Excel
Charts in Microsoft Excel are a great way of representing data graphically, but sometimes that data is subject to change and when it does change, we need our charts to [...]
Sep 22, 2020
All about autofill in Microsoft Excel
You all agree that Microsoft Excel’s Autofill feature is one of the most popular and versatile features in Excel, however not a lot of people know about its full [...]
Sep 04, 2020
Create an advanced filter in Excel
Filtering is a common task in Microsoft Excel and the normal filtering (the ‘autofilter’) can do quite a lot of what you will need. However, there are some filters that [...]
Jun 12, 2020
Pinpoint your 3D chart's data points with drop lines in Excel
One of the frustrating things I find about Microsoft Excel, particularly when you're producing a chart, is the huge quantity of formatting options that are available. [...]
Jun 06, 2020
Sorting made easy with custom lists in Excel
Sorting is a common task in Microsoft Excel, but it does have a few specific, sophisticated options that not too many people know. One of these is the ability to sort in [...]
May 19, 2021
Use slicers to filter table data in Microsoft Excel
Slicers were first introduced in Excel 2010 and gave us a nice easy way of filtering PivotTable data. Instead of the typical drop-down list filtering in Excel, slicers [...]
Mar 27, 2021
How to convert text to columns in Microsoft Excel
Have you ever needed to sort or filter data in Microsoft Excel but couldn't because the data have been combined into a single column rather than multiple columns? A [...]
Dec 13, 2020
Manipulate dates with custom number formats in Excel 2010
Imagine you’re in a situation where you want to send a list of the employees’ birthdays so that everyone in the company will know when to send their regards to others in [...]
