If you’re planning a vacation trip, Excel can help. It’s a great place to keep your packing lists, and you can track your vacation spending too (if you really want to know the total!). I’ve just uploaded a new sample file that will show how far you’ll travel. Select cities, and formulas do a mileage lookup, with total distance from start to end.
Two City Mileage Lookup
The new workbook is based on a previous one, which showed the distance between two cities. Select a city name in each green cell, and see the distance between those two cities, in miles.
The distances come from a lookup table, shown below. Find the intersection of the two selected city names, and that is the distance between them.
The old workbook was handy if you were going from one city to another, and then straight back home. But what about a longer journey, with stops at multiple cities?
Maybe you’d like to plan a trip to a few vacation spots in Florida, and see how far you’ll travel. I’ve put arrows on a Florida map, to show our imaginary vacation route. The trip starts from Gainesville, gets down to Sarasota, and then back home.
NOTE: I found a copyright-free map on The National Map site, which is run by the U.S. Geological Survey. Do not visit that site if you are easily distracted – there are lots of fascinating data collections and maps there. You have been warned!
Total Distance for a Vacation Trip
In the new workbook, there are data validation drop down lists where you can choose up to 6 cities.
There are formulas in the next column, to do a lookup from the mileage table, and another formula shows a grand total.
The Lookup Formula
To do the mileage lookup, the “Miles” column has an INDEX/MATCH/MATCH formula in each row. Read more about INDEX and MATCH on my website.
Last month, you saw J. Woolley’s technique to run command files from Excel hyperlinks. He created a SuperLink function too, that is better than Excel’s HYPERLINK function. Now he’s sharing a new technique, with better hyperlinks for Excel sheets.
Better Hyperlinks for Excel Sheets
In last month’s article, J. Woolley’s created special hyperlinks that make it easy to run command files from Excel. In his new technique, SHEET::NAME , J. Wooley has created better hyperlinks for Excel sheets.
With normal Excel hyperlinks, there are limitations:
Previous selection on the linked sheet is changed, because the hyperlink selects a specific range
Can’t link to a chart sheet, because it doesn’t have cells
If the linked cell is on a hidden sheet, the link fails silently
The SHEET::NAME hyperlinks overcome those limitations. These hyperlinks can:
activate a sheet, without changing the previous selection
activate a chart sheet, as well as worksheets
show a message if linked cell is on a hidden sheet
Get the Sheet Name Hyperlink Files
To get started, click this link to get the zipped folder with the Sheet Name files, and download it to a folder on your computer. SheetName.zip contains the following files:
When you create a pivot table, a default PivotTable Style is automatically applied. You can change to a different style, and you can even create custom pivot table styles. To help you keep track of the styles that you have, here’s a List All Pivot Table Styles macro.
Pivot Table Custom Styles
If you’re not sure how to create your own custom pivot table style, this short video shows the steps. Also, there are step-by-step written instructions on my Contextures site, on the Pivot Table Formatting page.
Better Format for Pivot Table Headings - YouTube
Default and Custom Pivot Table Styles
There are about 85 built-in pivot table styles in my version of Excel – there might be more than that in your version, or fewer styles.
If you create custom pivot table styles, they’ll be added to that list too. The custom styles also appear at the top of the style gallery.
List All the Pivot Table Styles
To get a list of all the pivot table styles in the active workbook, use the macro that’s shown below. The macro adds a sheet to the workbook, with a list of the pivot table style settings.
The list shows the style name and number, whether it’s built in or custom, the header colour, and inside border colour.
NOTE: Lots of black is used for the style formatting, so the macro shows a black dot, instead of filling the cell with black. I find that easier to read, and it also saves on printer toner, if you want to print the list!
Macro to List All Pivot Table Styles
This macro to list all Pivot Table styles is in the sample workbook that you can download. There are four other macros too, to list and set the styles.
Dim wb As Workbook
Dim lStyle As Long
Dim stl As TableStyle
Dim ws As Worksheet
Dim myRow As Long
Dim lClrH As Long
Dim lClrB As Long
Set wb = ActiveWorkbook
Set ws = Sheets.Add
On Error Resume Next
.Range(Cells(1, 1), Cells(1, 5)).Value _
= Array("Style", "Name", "BuiltIn", _
myRow = 2
For lStyle = 1 To wb.TableStyles.Count
Set stl = wb.TableStyles(lStyle)
If stl.ShowAsAvailablePivotTableStyle = True Then
ws.Cells(myRow, 1).Value = lStyle
ws.Cells(myRow, 2).Value = stl.NameLocal
ws.Cells(myRow, 3).Value = stl.BuiltIn
lClrH = stl.TableStyleElements _
If lClrH = 0 Then
ws.Cells(myRow, 4).Value = "•"
ws.Cells(myRow, 4).Interior.Color = lClrH
lClrB = stl.TableStyleElements _
If lClrB = 0 Then
ws.Cells(myRow, 5).Value = "•"
ws.Cells(myRow, 5).Interior.Color = lClrB
myRow = myRow + 1
.Range("A1:E1").Font.Bold = True
.Range("G1").Value = "• = Black"
.Columns("C:E").HorizontalAlignment = xlCenter
.Columns(6).ColumnWidth = 3.57
Do you use Excel to keep track of software subscriptions, or domain registrations, or other things with an expiry date? It’s important to keep track of those dates, so here’s an example of how to monitor expiry dates in Excel, and see what needs to be renewed soon.
Expiry Dates List
Here’s a simple list of expiry dates for Microsoft Office subscriptions. Those have to be renewed every year, so that you don’t arrive at the office one day, and find out that you can’t use Excel. Oh, the horror!
How Many Days to Expiry Date
To make it easy to see which subscriptions are expiring soon, I’ll use conditional formatting to highlight anything that will expire within the next 30 days.
I could make a conditional formatting rule that is based on the Expiry Date column, but I find it easier to check regular numbers, instead of dates.
So, I’ll add a new column, with a formula to calculate the number of days there are before the expiry date. In the screen shot below, you can see that new column – DaysToExpiry.
The formula in that column F subtracts the current date from the Expiry Date: =D3-TODAY()
The results look a bit strange though, when you hit the Enter key, to add that formula.
Fix the Results
Because the formula refers to a date cell, Excel “helps” us, by formatting the result as a date too.
A couple of the rows have expiry dates in the past, and those rows show number signs in the DaysToExpiry column. Excel can’t convert negative numbers to dates, so it shows those number signs instead.
To fix all the results:
Click in the heading of the DaysToExpiry column, to select all the table rows.
On the Home tab of the Excel Ribbon, choose General or Number as the Number Format.
Highlight Upcoming Expiry Dates
To make the upcoming expiry dates stand out, we’ll use conditional formatting. Based on the result in the DaysToExpiry column, we can highlight the expiry dates which are 30 days (or less) from today.
To add the conditional formatting:
Select all the data rows in the expiry date table
On the Home tab of the Excel Ribbon, click Conditional Formatting, then click New Rule
In the New Formatting Rule window, in the “Select a Rule Type” section, click “Use a formula to determine which cells to format”
In the Rule Description section, type this formula in the formula box:
NOTE: The formula uses an absolute reference to column F (DaysToExpiry), and a relative reference for the row number.
Click the Format button, and choose a fill colour to highlight the rows with upcoming Expiry dates.
Click OK to close the Format window, then click OK to close the New Rule window
The table rows with upcoming expiry dates are highlighted with the colour that you selected.
More Expiry Date Warnings
With conditional formatting on the expiry date list, you’ll quickly see which subscriptions need to be renewed soon. Just open the workbook every day, and renew those subscriptions, as soon as they’re highlighted. Then, remember to change the Expiry Date, so you’ll get a warning next year too.
If you want additional warnings, my sample file also shows how to make a hyperlink appear, in a SendEmail column. You could click that to send an email about the expiring subscription, if someone else is supposed to renew it. Or, have a link to the website where you go to renew the subscription.
Expiry Dates Summary
My sample file also has a Summary sheet — it shows the total count of expiry dates, and the number that are expiring soon.
If you don’t want to open the Expiry Dates workbook every day, you could link those summary cells to another workbook – one that you DO open every day.
Download the Sample File
Get the sample Excel file for this tutorial from my Contextures website. Go to the Excel Sample Files page, and in the Conditional Formatting section, look for CF0008 – Expiry Date Warning. The zipped file is in xlsx format, and does not contain any macros.
And while you’re on the Sample Files page, take a look around. There are lots of other files there – you might find some other interesting techniques to try.
Last year, J. Woolley shared his technique to run command files from Excel hyperlinks. He has improved how this works, and now you can get his latest version of the files. It has a SuperLink function too, that is better than Excel’s HYPERLINK function.
Original Run Command Files From Excel
Click here to see the original article, that describes how the first version works, and why J. Woolley created it, to make it easy to run command files from Excel.
New and Improved Version
Since I posted that article, J. Wooley has made significant improvements to this technique. For example:
RUN::COMMAND can be included in a hyperlink cell’s text value or in its ScreenTip (or both)
Multiple RUN::COMMANDs can be processed for a single hyperlink cell
Defining the path for each RUN::COMMAND is now simplified
J. Wooley also developed a new user-defined function (UDF) named SuperLink. This function supports RUN::COMMAND , and it can be used independently too, as a better way to create hyperlinks. (The SuperLink UDF details are described further down in this article.)
Get the Run Command Files
To get started, click this link to get the zipped folder with the Run Command files, and download it to a folder on your computer. RunCommand.zip contains the following files:
It does not populate the worksheet’s Hyperlinks collection.
It does not trigger the worksheet’s FollowHyperlink event.
It returns a “shortcut” that looks like a text string but should not be treated as one.
A cell using the HYPERLINK function has a default ScreenTip that cannot be changed.
The Insert > Link (or Ctrl+K) dialog is not available for a cell containing the HYPERLINK function.
If a cell with a Hyperlink object defined using Ctrl+K is edited to add the HYPERLINK function, that HYPERLINK “shortcut” will be ignored in favor of the original Hyperlink object.
SuperLink User Defined Function
The SuperLink UDF, that J. Woolley created, resolves those HYPERLINK function issues.
On the Test sheet of the TestRunCommand.xlsm workbook, there is a section with examples for the SuperLink function, and an introduction in merged cells A19:D21.
For a detailed explanation of the User Defined Function, read the SuperLink PDF file that is included in the download folder. You can open that file with Test Number 12 link, on the Tests sheet.
To test the SuperLink UDF, use the links that are set up on the Test sheet, in cells B23:D29. Those formulas show how the SuperLink UDF can be used to support application the updated RUN::COMMAND technique.
Questions or Comments
If you have questions or comments about the Run Command Files technique, post in the Comments section below, and J. Woolley will try to help.
Welcome to the new home of my Contextures Excel Blog! It used to be part of my main Excel site, but the blog needed more room. I had a long “to do” list for the blog move, and Excel made it easy to cross off tasks when I finished them.
Does any move ever go smoothly? I don’t think so! Something always gets broken, or left behind, or falls off the truck on the way to the new place.
In this move, comments were lost on blog posts from mid-2014 to 2018. The articles from those years didn’t import correctly, so I had to manually upload them from my original files. Thanks, Windows Live Writer, for storing all those articles! I’ll try to get the comments back though, because they have lots of valuable information.
Links from the old blog should automatically redirect to the same article on this new blog, so I hope you find everything that you need. If you run into any problems, please let me know.
Make a To Do List
I couldn’t have made the blog move without Excel though! It’s a great place to make lists, and I had lots of lists.
It’s satisfying to cross off tasks when you’ve finished them, so you can see your progress. That’s easy to do with a pencil, if your list is on paper. But how can you cross off tasks in Excel?
Cross Off Tasks
Here’s a quick trick that will cross off tasks automatically, without any macros. This might slow down a large list, but is handy for small- or medium-sized lists.
In the screen shot below, you can see a simple task list. There’s a due date in column A, and the task name in column B. When the task is completed, put an “x”, or the date, or anything else, in column C.
As soon as a row has something in column C, the text in that row is crossed out, and the font changes to medium grey colour. That makes it easier to focus on the tasks that still need to be finished.
How It Works
To cross off tasks automatically, I set up a simple conditional formatting rule on the entire table. The rule checks column C, in each row, to see if it is not empty – “” is an empty string.
The formatting for that rule changes the font to Strikethrough, with grey font colour. None of the other formatting (fill colour or borders), is changed.
How can you copy a pivot table custom style to a different workbook? There isn’t a built-in way to do that, but there is a workaround solution. There are instructions that worked in Excel 2013, and earlier versions (way back to Excel 2007). Those stopped working, unfortunately, but there’s an easy way to do this in Excel 2016 too.
Create a Pivot Table Custom Style
Excel comes with many built-in PivotTable Styles, in Light, Medium and Dark colour themes. You can quickly apply any of those styles to the selected pivot table – just open the style palette, and click on the style that you want to apply.
You can’t change those built-in styles, but you can create your own pivot table custom style, based on a built-in style. Then, modify your custom style, with the formatting that you want.
This video shows how to create a custom style from an existing style, and make changes to its formatting.
Better Format for Pivot Table Headings - YouTube
Copy Custom Style in Excel 2013
If you’re using Excel 2013 or earlier, this short video shows how to copy one of your fancy custom styles into a different workbook.
Aside from staring at them closely, how can you compare two cells in Excel? Here are a few functions and formulas that check the contents of two cells, to see if they are the same. We’ll start with a simple check, then move up the formula ladder, for more complex comparisons.
Easy Way to Compare Two Cells
The quickest way to compare two cells is with a formula that uses the equal sign.
If the cell contents are the same, the result is TRUE. (Upper and lower case versions of the same letter are treated as equal).
Ignore Extra Spaces
If you just want to compare two cells, but aren’t concerned about leading spaces, trailing spaces, or extra spaces, use the TRIM function to remove them, for one or both of the cells.
That can help if you’re trying to match text strings to the values in an imported list, such as this VLOOKUP example.
Compare Two Cells Exactly
If you need to compare two cells for contents and upper/lower case, use the EXACT function. As its name indicate, that function can check for an exact match between text strings, including upper and lower case. It doesn’t test the formatting though, so it won’t detect if one cell has some or all of the characters in bold, and the other cell doesn’t.
Sometimes you don’t need a full comparison of two cells – you just need to check the first few characters, or a 3-digit code at the end of a string.
To compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:
To compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters:
You can combine LEFT or RIGHT with TRIM, if you’re not concerned about the space characters:
And combine LEFT or RIGHT with EXACT, to check if upper/lower case match too. This formula will ignore extra spaces, but checks the case:
How Much Do Cells Match?
Finally, here’s a formula from UniMord, who needs to know how much of a match there is between two cells. Are the first 5 characters the same? The first 10? What percent of the string in A2, starting from the left, is matched in cell B2?
Here’s a sample list, where the addresses in column A and B and being compared.
Get the Text Length
The first step in calculating the percent that the cells match is to find the length of the address in column A. This formula is in cell C2:
Get the Match Length
The formula in column D is doing the hard work. It finds how many characters, starting from the left in each cell, are a match. Lower and upper case are not compared.
The INDIRECT function creates a reference to a range of cells, starting from cell A1. The range ends in column A, in the row that matches the length calculated in column C. So, in row 2, that range is A1:A9.
The ROW function returns the row for each of the rows in that range. That’s why we use ROW/INDIRECT, instead of just referring to the length in cell C2.
In this screen shot, I’ve used the F9 key to calculate that part of the formula, and you can see the row numbers.
Then, the LEFT functions return the characters that are 1, 2, 3…characters to the left in each cell. In this screen shot, I’ve calculated both of the LEFT functions, and you can see that there is a match for lengths 1 through 9.
However, if I do the same thing in row 5, only the first character is a match. After that, the characters are different in the two cells.
The equal sign compares the values for characters 1 through 5 in this example, and returns TRUE if they match, and FALSE if they do not match.
The double minus sign converts each TRUE to a 1, and each FALSE to a zero.
Finally, the SUMPRODUCT function adds up those numbers, to give the number of characters, from the left, that match. In row 5, that total is 1
Get the Percent Match
Once the length and match length have been calculated, it’s easy to find the percent matched. This formula is in cell E2, to compare the lengths:
There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5.
Thanks, UniMord, for sharing your formula to compare two cells, character by character.
More Ways to Compare Two Cells
Here are a few more articles that show examples of how to compare two cells – either the full content, or partial content.
Have you seen the articles that blame Excel for all kinds of business errors? In some cases, problems occur because rows were hidden, and that distorted the data analysis. To help avoid those problems, I created a sample file that shows an Excel hidden data warning, if rows or columns are hidden.
Help, Not Prevention
This solution should help you spot hidden rows and columns, but don’t depend solely on this when you’re doing critical work.
Use visual checks for filtered or hidden rows, or hidden columns, and remember to look for hidden sheets too.
As always, our goal is to make things idiot resistant, not idiot proof.
Hidden Row Warning
Here’s how I set up the Excel hidden data warnings.
At the top of the sheet, in cell B2, there’s a formula to check for hidden rows. It counts the missing rows, whether they’re hidden manually, or by a filter.
In this screen shot, no filters have been applied to the table, but rows 12:13 were manually hidden, so the formula result is 2.
Hidden Rows Formula
Here’s the formula that’s in cell B2: =COUNT(Table1[OrderCount]) – AGGREGATE(2,5,Table1[OrderCount])
First, the formula counts all the numbers in the OrderCount column. That column has a simple formula that returns a 1 in each row. =1
Counting those cells should give an accurate count of the number of rows in the table. =COUNT(Table1[OrderCount])
Count the Visible Rows
Next, the AGGREGATE function counts the visible cells in the OrderCount column. AGGREGATE(2,5,Table1[OrderCount])
AGGREGATE uses function type 2 ( COUNT), and is set to ignore hidden rows (option 5).
Number of Hidden Rows
In this example, there are 21 rows in the table (COUNT) and 19 visible rows (AGGREGATE)
To find the number of hidden rows, subtract the visible rows from the total count, and the result is 2 hidden rows.
Hidden Column Warning – Attempt 1
Unfortunately, AGGREGATE doesn’t work for columns, just rows, so how can you tell if columns are hidden?
A hidden column would have zero width, so I used the CELL function to check the cell widths in the top row, cells A1:J1. =IF(CELL(“width”,A1)>1,1,0)
Then, in cell B3, a formula subtracts the sum of those cells, from the count of the cells. =COUNT(A1:J1)-SUM(A1:J1)
In theory, that solution works, but the results didn’t automatically update if columns were hidden or unhidden. In this screen shot
D and E have been unhidden, but are still showing zeros
F, G and H are hidden, but are still calculating as 1
The Hidden Columns total shows 2, instead of 3
To see the correct number of hidden columns, you can press F9 to recalculate.
Hidden Column Warning – Attempt 2
An Excel hidden data warning isn’t too helpful, if you have to remember to recalculate.
But, as a conditional formatting rule in cells A1:J1, it seems to work nicely. I’ve only tested in a small file though, so your results might be different.
With cells A1:J1 selected, I created a new formatting rule, using this formula: =CELL(“width”,B1)=0
A cell turns yellow, if the cell to its right is hidden (0 width).
In this screen shot, columns D:E and H:I are hidden. As a result, cells C1 and G1 have yellow fill colour, based on the conditional formatting rule.
Warning About the Warning
Charles Williams found that conditional formats “are not executed at a calculation unless they are on the visible portion of the screen“.
So, if you try this Excel hidden data warning technique:
Be sure to lock the top row.
Recalculate too, just to be sure that the correct cells are coloured.
Before you do any critical data analysis, do a visual check to see if any rows or columns or entire sheets are hidden.
Remember, as the old saying goes, it’s better to be safe, than to read about your catastrophic errors on the internet.
Download the Excel Hidden Data Warning File
To download the Excel hidden data warning workbook, go to the Conditional Formatting Examples page on my Contextures site. In the Download section, click on the link for the Hidden Data Warning sample.
The zipped file is in xlsx format, and does not contain any macros.
As you know, Excel can do almost anything! Recently, I changed the server that my site and blog are on, and Excel helped with the IP Addresses. And since it’s that time of year, let’s see how Excel can calculate Easter dates for us.
Easter Dates Calculation
It’s Easter this Sunday, but if you’d like to verify that, or find out when Easter will be next year, use one of the Easter date calculations that Jerry Latham shared here, a few years ago.
There are several worksheet formulas, and notes on their limitations.
There are also four User Defined Functions, that use different methods for calculating the Easter date.
In the screen shot below, the following formula is in cell B4:
As I mentioned earlier, I recently had my Contextures site and this blog moved to a different server. I hope that it is faster, and has minimal down time.
The only casualty that I’ve found so far is the comment section on this blog. Unfortunately, comments that were posted during the loooong transition time (March 14th to March 25th), were lost in the shuffle.
Other than that, things seem to be okay, but please let me know if you notice anything missing, or broken.
IP Address Conversion
One of the key steps for moving to a new servers was to create DNS records for the them.
The form had boxes for the IPv4 address and the IPv6 address. Uh-oh!
Fortunately, I found examples of the different formats.