How can you get the last number in an Excel column? I needed to do that in one of my sample files, so here's the formula that I used. Then, someone asked me how to get the last item (text or number) from a column, so there's a formula for that too.
In the previous formula, the LOOKUP function get the last number from a column. But how would you write a formula to get the last text or number in a column, which has assorted items, including blank cells and errors?
The Sample Data
Here's the sample data, and the result in cell D2 should be "yes". That's the last entry which either either a number or text.
What lookup formula would you use to get that result? My solution is below the screen shot.
With values (and blank cells) in cells B1:B6, I used this LOOKUP formula to get the last entry that's text or a number:
Check for Empty Strings
Here's how the formula works.
First, the formula tests each entry to see if it's "not equal to" an empty string (<>"").
The result of that test is TRUE (1) or FALSE (0) for each cell in the range.
Divide with the Result
Then, the number 1 is divided by each TRUE/FALSE.
TRUE is equal to 1, and 1/1=1
FALSE is equal to zero, and 1/0 results in a #DIV/0! error
Any cells that contain an error will also return an error in this calculation
Find the Lookup Value
The lookup value is 2, which won't be found, because the highest number is 1.
Since it can't find a 2, the formula finds the position of the last number 1, and returns the value ("yes") at that location, in the lookup range.
Get the Sample File
To see the first LOOKUP formula, which returns the last number in a column, get my my Excel Weight Tracker files. There are versions for pounds/kilos, or for stone.
calculates the current year and month name, in yyyy_mm format
checks for a sheet with that year/month name
if not found, it creates a new sheet, based on Wkst_Master
names new sheet with current year and month
Macro Code to Add Month Sheets
Here is the code for the AddMonthWkst macro.
NOTE: You can change the master sheet name, and the date formatting, to match what you need in your workbook.
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean
On Error Resume Next
Set wsM = Sheets("Wkst_Master")
strName = Format(Date, "yyyy_mm")
bCheck = Len(Sheets(strName).Name) > 0
If bCheck = False Then
'add new sheet after Instructions
ActiveSheet.Name = strName
Set wsM = Nothing
Pivot tables are a quick and easy way to summarize a table full of data, without fancy formulas. Occasionally though, things can go wrong. Today we'll figure out why you might see errors in pivot table totals or subtotals, when all the item amounts look fine.
Why Are There Errors?
Here's a simple pivot table with error values in the Grand Total row.
It's understandable that the "Average of Price" total shows a #DIV/0! error, because that error also appears for the East region, in that column.
But why are there errors in the totals for the Count and Count Numbers columns? There aren't any errors in the amounts that are being totalled there.
Check the Source Data
Some pivot table mysteries can be solved if you take a look at the source data, so let's start there.
In the good old days of Excel, you could change a workbook setting, and see a preview picture of the file contents, before you opened it. That feature was available in Excel's Open window, but has disappeared in newer versions of Excel. Here's how to see Preview Pictures, and a warning about using them.
Preview Picture Example
If you're not sure what a preview picture looks like, here's a screen shot from one of my sample Excel files. The file list is at the left, and the Preview Picture for the selected file is shown at the right.
The preview shows the page that was active when the file was saved last. I must have been checking out that pivot table, to see how the sales were going!
Excel Property Settings
To change the Preview Picture setting in Excel, you need to open the properties dialog box. Here are the steps to do that, in Excel for Office 365.
To open the Property dialog box, follow these steps:
On the Ribbon, click the File tab
Click the Info category
At the right, click the drop down arrow for Properties
At the bottom of that tab, there is a check box – "Save Thumbnails for All Excel Documents".
Check that box, to turn on this feature
All Excel Files Affected
As you can guess from the check box description, the setting in that check box (on or off) affects all Excel files, not just the active workbook.
In earlier versions of Excel, you could set the Preview Picture setting for specific workbooks. The Properties window had a "Save preview picture" check box, and that setting affected the active workbook only.
Opening Files Within Excel
There are problems with the Preview Picture feature though, if you're trying to open Excel files using the File | Open command.
To see the problem, follow these steps to activate the Preview Pane within Excel:
On the Excel Ribbon, click the File tab
Then, at the left, click Open, and click the Browse button
Click the Preview Pane button at the top right of the window
Then, click on an Excel file in the files list at the left, to see if its preview appears at the right.
HINT: It does not!
Instead of showing a Preview Picture, the Preview Pane usually shows this error message:
"This file can't be previewed because of an error in the Microsoft Excel previewer"
Apparently, the Excel, Word and PowerPoint programs can't show you a Preview Picture of their own files! See this Microsoft support article for a bit more information.
Where to See Preview Pictures
Even though Excel won't show you preview pictures, you can see them in Windows Explorer.
Open Windows Explorer
In the Ribbon at the top, click the View tab
At the left end of the Ribbon, in the Panes group, click on Preview Pane, to turn that pane on.
Then, click on an Excel file in the file list, to see a screen shot of its contents in the Preview pane.
If the "Save Thumbnails for All Excel Documents" is turned on, some of your desktop shortcuts might show a custom thumbnail.
They don't usually change back to normal icons though, if you turn that setting off.
You'll probably have to delete the shortcuts, and create new ones.
Picture Preview Warning
If you turn on the "Save Thumbnails for All Excel Documents" setting, and then use the Picture Preview pane in Windows Explorer, you might notice some problems.
When you click on an Excel file, to see the preview, an instance of Excel opens in the background. You can see that Excel instance, if you open the Task Manager, and check the list of Background Processes.
While that instance of Excel is running in the background, you might see a "File in Use" message when you open Excel.
For example, "PERSONAL.XLSB is locked for editing"
To avoid that type of message
Close the Preview Pane in Windows Explorer
Then, close and re-open Windows Explorer
The background instance of Excel should close automatically when you close the Explorer window. But if it doesn't, you could use Task Manager to close it.
If you've purchased a copy of my UserForms for Data Entry kit, there's a new bonus for you -- the UserForm Builder (UFB) add-in. Watch the video below, to see how it works, and download the latest version of the kit, to try it out.
The Excel Status Bar shows you messages about what's going on, while you work. Some of those messages are optional, and you can turn them on or off. Here's how you can customize the Excel Status Bar, and see my notes on a couple of the options that were confusing.
What's in the Status Bar
This week, I checked the Status Bar, to see what the Num Lock setting was, but it wasn't visible. So, while adjusting my Status Bar settings, I found a few odd things, and decided to share them with you.
By sharing my findings, as inconsequential as they are, I can justify the time that I wasted (invested?) in exploring the Status Bar options.
P.S. I have an English/French keyboard, with keys in odd places, and keeping hitting Num Lock accidentally. I hope the Status Bar setting will help me notice when it's off.
To see the list of Status Bar options, right-click on the Status Bar.
Then, click on any option, to toggle it on or off.
Status Bar Calculations
Some of the Status Bar options are for quick calculations:
If you select two or more cells that contain data, the Status bar show a quick calculation for the function options that you've turned on.
If the selected cells contain text only, a Count is shown.
If at least one cell contains numeric data, the other functions are shown.
NOTE: The Status Bar Count calculations is like the worksheet COUNTA function – it counts all types of data.
See Current Status
When you right-click on the Status Bar, to see the list of options, some show a current status, to the right of the option name.
To the right of some option names, you can see current status for that setting. The list also shows the calculation amounts for the functions that are shown on the Status Bar.
See More Totals
While the Status Bar options list is showing, you can force the totals for other functions, without adding them the Status Bar
Check a function option, to see its total
Immediately uncheck the function
WARNING: For functions that are not in the Status bar, the forced totals will not change automatically, if you select different data. Check/uncheck the functions again, to see updated amounts.
Most of the Status Bar options are easy to figure out, but there were a couple that confused me at first.
View Shortcuts – Oh! Does this show helpful keyboard shortcuts, like the tooltips for the Ribbon commands show? No. It does not. It shows or hides the icons for the workbook views – Normal, Page Break Preview, or Page Layout.
Page Number – I thought this would show which page you were on in Page Break Preview, but that didn't work. You'll only see page number in the Status Bar if you're in Page Layout view. I never use that view, do you?
Flash Fill – There are 2 options for the Flash Fill feature:
Flash Fill Changed Cells
Flash Fill Blank Cells
The Changed Cells message was easy to see – it appears after you use the Flash Fill feature.
Here's a screen shot of the infamous month name Flash Fill (Mayuary?), and the Status Bar shows the number of changed cells.
The Blank Cells option was confusing though. How could you have blank cells in a Flash Fill?
After a bit of experimenting, I finally saw that message, and here's what I did:
Use the Flash Fill feature, and press Enter, to accept the suggestions.
Immediately, clear one or more of the cells that were filled
You can see the number of blank cells in the Status Bar.
What's on Your Status Bar?
So, what's on your Status Bar? Did you customize it, or do you just go with the default settings?
Do you show all the functions? Num Lock? Cap Lock? Other good stuff?
When you create an Excel Table, or a Pivot Table, a default style is applied. You can change to a different built-in style, or create custom styles, with your own formatting. There are details below, and an Excel custom styles problem that you might run into.
Create a Custom Style
The easiest way to create a custom style is to make a duplicate of a built-in style, that's similar to what you need.
Select a cell in any pivot table, and on the Ribbon, click the Design tab.
In the PivotTable Styles gallery, right-click the style you want to duplicate.
In the context menu, click Duplicate.
Modify the New Custom Style
After you duplicate an existing built-in style, type a name for your custom style. Then, change the formatting for one or more of the table elements.
This video shows how to create a custom style, and modify it.
Better Format for Pivot Table Headings - YouTube
Custom Style Problem
Usually, things go smoothly if you try to modify a custom style, or delete a custom style.
There's one situation when you'll run into a custom style problem though, and thanks to UniMord for letting me know about this!
If any sheet in the workbook is protected, you can't modify or delete a Custom Style.
When you choose the Modify command, nothing happens
When you choose Delete, a confirmation message appears, but if you click OK, nothing happens
To modify or delete any custom styles, you'll need to unprotect all the sheets first.
To see which sheets are protected, click the Ribbon's File tab, and click Info.
NOTE: Using the Protect Workbook command does not prevent the custom styles from being modified or deleted. Only the Protect Worksheet command blocks these actions.
Copy Custom Style to Other Workbook
After you create custom styles, you might want to use them in other workbooks. There's no built-in command that lets you do that.
In Excel 2013 and earlier, you can copy a pivot table that is formatted with a custom style, and paste it into a different workbook. That automatically creates a copy of the custom style in the other workbook.
In Excel 2016 and later, that trick doesn't work. Instead, you can copy the pivot table's worksheet into the other workbook, and the custom style will be copied. (see warning in next section)
This video shows the steps in Excel 2013 and earlier.
Copy a Custom PivotTable Style - YouTube
Sheet Copy Warning
Unfortunately, copying the pivot table sheet can also copy defined names from the original workbook. If the original file has lots of define names, use these extra steps:
First, copy the pivot table sheet into a new blank workbook
On the Excel Ribbon's Formulas tab, click Name Manager
To select all the names, click the first name, then press Shift and click the last name
Click the Delete button, to delete all the names
Close the Name Manager
Move the pivot table sheet into the other workbook, where you want to add the custom style.
Here's a simple example that shows how you can get data from Excel button clicks. There are 3 buttons on one sheet, and if you click one, the date and time are recorded on another sheet, along with your name and the button number.
Sheet with Buttons
This example could be used during a short test, with the participant clicking a button at specific intervals, to indicate their current stress level.
In this sample file, there is a sheet named Input, shown below. The participant's name goes at the top of the sheet, in the underlined cell.
Then, during the test, click the buttons that match your stress level, and Excel records the information.
Sheet with Collected Data
On another sheet, named TestData, the records are stored.
A – Date and time of button click
B - Name from Input Sheet
C - Button number that was clicked
Set Up the Input Sheet
In this example, I inserted 3 buttons from the Form Controls, on the Developer Tab.
NOTE: After you add a button, the Assign Macro window opens. Click Cancel, to close that window – you can assign a macro later.
After you add each button, right-click on it, and click Edit Text
Then, type a number on each button – these buttons are 1, 2 and 3.
Create a Name Cell
Near the top of the Input sheet, there's a cell with a bottom border, and "Name:" is in the cell to the left of it.
The underlined cell is named "UserName", and the macro will get the value from that cell. There are instructions for naming ranges on my Contextures site.
Add Input Instructions
Above the buttons, I added a question – "What is your current stress level?".
Then, I formatted that text in 18 pt bold font, so it stands our on the worksheet.
Below the buttons, I added text to show that buttons at the left represent low stress, and buttons at the right are for high stress.
Low ============> High
Macro for Buttons
There's one macro in the workbook, and it adds the data to the TestData sheet. All 3 buttons will run the same macro.
Here's the macro code, and it is stored in a regular code module in the workbook.
Dim wsData As Worksheet
Dim wsIn As Worksheet
Dim nextRow As Long
Dim BtnText As String
Dim BtnNum As Long
Dim strName As String
Set wsIn = Worksheets("Input")
Set wsData = Worksheets("TestData")
nextRow = .Cells(.Rows.Count, "A") _
BtnText = .Buttons(Application.Caller).Caption
BtnNum = CLng(BtnText)
strName = .Range("UserName").Value
With .Cells(nextRow, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Cells(nextRow, 2).Value = strName
.Cells(nextRow, 3).Value = BtnNum
What the Macro Does
Here's a quick overview of what the AddButtonData macro does.
First, the macro sets variables for the Input sheet and the TestData sheet.
Then, it finds the next available row in column A, on the TestData sheet.
From the Input sheet,
the macro uses Application.Caller, to figure out which button was clicked, and gets the caption from that button.
That caption text is converted to a number
The name comes from the "UserName" cell.
On the TestData sheet
The current date and time (Now) is added in column A, and formatted.
The name is added to column B
Button number goes into column C
Assign Macro to Buttons
The final step is to assign that macro to each of the three buttons.
Right-click on a button, and in the popup menu, click Assign Macro
In the list of macros, click AddButtonData, and click OK
Repeat those steps for the remaining buttons.
Test the Buttons
To test the buttons, follow these steps:
First, put your name in the underlined cell.
Then, click one of the buttons
Wait a couple of seconds, and click a different button
To see the data that was collected, go to the TestData sheet. You should see your name there, along with the date/time information, and the button numbers.
The sample file only has 3 buttons, and you could add more, if needed. Just put a unique number on each button, so it can be identified.
Instead of using Form Control buttons, you could use shapes, such as rounded rectangles. That would give you a wider range of colours, instead of grey, grey or grey.
Do you ever need to compare two Excel Tables? Here's a simple formula that quickly shows if there are any differences, between tables that have the same number of columns and rows.
Compare Two Tables
This simple, and very short, formula will compare two Excel Tables, that should be identical, but might have differences. Maybe one or two numbers have changed, but those little differences aren't easy to see.
With this formula, you won't have to manually comparing the tables, row by row. And you won't need to create columns full of formulas to look for differences.
Thanks to UniMord for this tip!
Just enter this formula, somewhere on the worksheet, using the names of your tables, where I have Table1 and Table2.
The formula result is TRUE, if the tables are exactly the same. If there are any differences, the result is FALSE.
First, the SUMPRODUCT function multiplies the array of table value comparisons, and returns the sum.
Next, the formula tests to see if the sum is equal to zero.
Why Subtract 1?
Do you know why there is "-1" in the formula?
This formula compares each cell in the two tables. and we can see the array of results in the Formula Bar.
If you highlight just this part of the formula -- (Table1=Table2) -- in the Formula Bar, and press F9 to calculate, you'll see all the results as TRUE or FALSE
Convert to Numbers
However, if you also include the -1, and the press F9 again, that arithmetic operation changes the results to numbers.
If all the comparisons are TRUE (1),
all the numbers will be zero (1-1)
the total will be zero
If any comparisons are FALSE (0)
those differences will show as -1 (0-1)
the total will not be zero.
How Many Differences?
To see how many differences there are between the two tables, you can make a slight change to the formula.
Instead of testing if the sum is equal to zero, multiply the sum by -1.
In this example, there are four differences between the two tables.
If you have buttons or shapes on an Excel worksheet, you can get their caption text from a worksheet cell, so the text changes, based on a formula. See how to add the button, create its text, then link the button to cell text instead.
Add a Worksheet Button
In this example, the workbook has a macro to show the total amount of an order. There's a button on the worksheet, and you click that to run the macro.
If you want to add a button, there are commands on the Developer tab, in the Insert menu on the Controls group.
The button in the Form Controls section is easier to use than the ActiveX controls button, and cause fewer problems, from my experience.
The Form Controls button has an "Assign Macro" command that appears automatically, after you create it. Just choose a macro from the list, and the button is ready to use.
Those Developer tab buttons are okay (if you like grey), but I like to use an Excel shape instead. Shapes give you more formatting options, so you can make your button stand out on the worksheet.
In the Insert tab of the Excel Ribbon, click Shapes, then choose one of the shapes, and click on the worksheet, where you want to add it.
With the shape selected, you can change its height and width, other formatting options, like the fill colour and outline colour. I like to change the Shape Effects too, and give it a round Bevel, so it looks more "button=y".
Then, to make the shape run a macro, right-click on the shape, and assign a macro to run when you click it.
Add Text to the Button
To add a caption to a shape "button", just select it, and start typing.
For this button, I typed "Run the Macro"
Format the Button Text
After you add the text, with the button still selected, use the Formatting commands on the Excel Ribbon to make the text look better.
I usually centre the text vertically and horizontally, and choose a bigger font size. Change the font colour too, if necessary, to contrast with the shape's fill colour.
Change the Button Text
Instead of using static button text though, sometimes it's nice to have a caption that changes, based on the situation on the worksheet.
In this example, a quantity is entered in cell C1, and customers get a discount if the quantity is greater than 40.
The formula in the cell C11, named TotalPrice, calculates the total price of the order.
=OrderQty * VLOOKUP(C1,B8:C9,2,TRUE)
Formula for Button Text
In cell E2, I've added another formula, to check the quantity, and show text based on that amount.
=IF(C1<=40,"See price", "See discounted price")
If the quantity is 40 or less, cell E2 will show "See price". If the quantity is over 40, the result in cell E2 will mention the discount – "See discounted price"
Link Button Text to a Cell
Instead of showing the static text, "Run the Macro", on the button, here's how to use the dynamic text from cell E2:
Click on the button to select it
Click in the Formula Bar, and type an equal sign: =
Click on cell E2, which has the text for the button, and press Enter
NOTE: You might have to reapply some of the formatting after you link the button to the cell.
Now, it the quantity is changed, the button will show the applicable text in its caption.
See the Steps
This animated gif gives you a quick look at the steps.