October 23, 2017
Fix Excel Dates Formatted as Text
Dates incorrectly entered or imported as text are a showstopper. We need our dates entered as date serial numbers to
Read More
October 20, 2017
Excel Date and Time Formatting
Even though dates and time are actually stored as a regular number known as the date serial number, we can
Read MoreOctober 19, 2017
Excel NETWORKDAYS.INTL Function
The Excel NETWORKDAYS.INTL function (new in Excel 2010) returns the number of working days between two date serial numbers, excluding
Read More
October 12, 2017
Power Query Get Files from a Folder
If you receive your data in separate daily, weekly or monthly files then before you can even begin analyzing the
Read More
October 2, 2017
Label Excel Chart Min and Max
Highlighting the minimum and maximum in an Excel chart can help focus your readers’ attention. We can manually select points
Read More
September 26, 2017
Dynamically Label Excel Chart Series Lines
The default Excel chart legends can be awkward and time consuming to read when you have more than 2 series
Read More
September 19, 2017
Highlight Cells Referenced in Excel Formulas
In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells
Read More
September 7, 2017
Excel Pivot Chart Drill Down Buttons
Excel Pivot Chart Drill down buttons are a new feature available in Excel 2016. You’ll find them in the bottom
Read More
August 22, 2017
Excel Extract a Unique List
As with most things in Excel, there are many ways to extract a list of unique or distinct values from
Read More
August 15, 2017
Excel Find and Remove Duplicates
Sometimes we want to find and remove duplicates in Excel, other times we just want to identify or highlight duplicates.
Read More
August 10, 2017
VBA SELECT CASE
VBA SELECT CASE allows you to test an expression against multiple conditions, and then carry out a series of commands
Read More
August 3, 2017
VBA IF THEN ELSE
The VBA IF THEN ELSE statement lets you test a condition, and then carry out other commands if the test
Read More
July 27, 2017
Excel Convert Dates to Fiscal Quarters and Years
If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters
Read More
July 20, 2017
Power Query Unpivot Scenarios
In this post I’m going to show you how to use Power Query’s Unpivot tool to fix 3 common data
Read More
July 11, 2017
Symbols in Excel Slicers
Did you know you can display symbols in Excel Slicers? That’s right, Slicers aren’t just limited to listing boring old
Read More
July 5, 2017
Excel Chart Secondary Axis Alternatives
Sometimes a secondary axis is a necessary evil. After all, most of the time you can’t plot big numbers and
Read More
June 29, 2017
Excel Chart Highlighting
One of the cool features of Power BI is the ability to cross filter and highlight charts. With a simple
Read More
June 21, 2017
List Files in a Folder Using FILES XL Macro (XLM)
Way back, when Excel 4.0 was around, it used a macro language called XLM. VBA was introduced in Excel 5.0.
Read More
June 13, 2017
Set Excel PivotTable Default Layout
New in Excel 2019 onward and Microsoft 365 is the ability to set a PivotTable default layout, which can save
Read More
June 6, 2017
Script Lab – Explore the Office JavaScript API & Learn to Build Add-Ins
Microsoft recently announced a project called Script Lab, previously known as 'Add-In Playground', which aims to help you learn how
Read More
June 1, 2017
Excel Dates Displayed in Different Languages
If you share Excel files and reports with users from different countries then you might want to give them a
Read More
May 24, 2017
Power Query Updates
The monthly update cycle for Excel’s Power Query sure keeps us on our toes. Power Query Updates for Excel 2010
Read More
May 17, 2017
Excel Themes
Excel Themes are a bundle of default colors, font styles and Shape effects. There are lots of built in Excel
Read More
May 12, 2017
More Tips for Debugging VBA
After my last post on debugging VBA I received a few comments with other tips or commands people use for
Read More
May 4, 2017
Debugging VBA Code
When you write VBA, or any programming language, you are going to encounter errors in it, or should we call
Read More
April 26, 2017
Power Query Date Formats
Power Query* can easily take a column of dates and extract, or convert it into various handy date formats with
Read More
April 20, 2017
Excel Row and Column Shortcuts
Inserting, copying, moving, or cutting and pasting rows and columns is something we do every day in Excel. And there
Read More
April 14, 2017
Power Query Lists
In Excel we often need to work with lists. List of numbers, letters, dates, countries, products, you get the idea.
Read More
April 3, 2017
Excel PivotTable Distinct Count
With the integration of Power Pivot into Excel 2013 (known as the Data Model), we now have access to a
Read More