Follow Contextures Blog - Excel Tips and Tutorials on Feedspot

Continue with Google
Continue with Facebook


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.

Column with Numbers

I'm updating my Excel Weight Tracker files, which were created long before Excel introduced named tables.

Switching to tables has made it easier to show a summary on the dashboard. The old dynamic ranges aren't needed now - we can just refer to table columns instead.

The WeightData table, shown below, has columns where you enter the date and your weight every week.

There is also a Dashboard sheet in the workbook, and I want the latest weight to show on the Dashboard.

What formula would you use to show that latest weight on the Dashboard sheet? My formula is below, and you might use a different solution.

Get the Last Number

The new dates and weights are entered at the end of the table, so I used the LOOKUP function on the Dashboard, to get the last number from the Weight column.

=LOOKUP(9.99999999999999E+307, WeightData[Wt])

What's That Number?

According to Excel's specifications, that strange number, 9.99999999999999E+307, is the largest number that can be typed into an Excel cell.

The LOOKUP function won't find that in the weight column (I hope!), so the formula returns the last number it finds in that column.

To see this formula, and all the others, get one of the Weight Tracker files - either the Pounds/Kilos version, or the Stone version.

Tip – Name That Number

Instead of putting that confusing number into the formula, you could define a name, using that value.

Then, put the "friendly" name into the formula, so you don't scare your co-workers.

To create the name:

  • Copy the number from the formula bar
  • On Excel's Formula tab, click Define Name
  • Type a name, e.g. XL_Max
  • Leave the scope as Workbook
  • Type a comment that explains what the number is
  • Right-click the Refers To box, and click Paste, then click OK

Use the Defined Name

After you define the name, use it in the formula.

  • Select the cell with the LOOKUP formula, and click in the Formula bar.
  • Select the long number, and replace it with the defined name, XL_Max
  • Press Enter, to update the formula

Read more about Excel Names on my Contextures site.

Column with Assorted Items

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.


Excel Lookup Formula for Last Item in Column


Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Set up a Master sheet in your workbook, and add month sheets automatically, based on that Master sheet. The new sheets will be named for the month and year, in yyyy_mm format.

Add Month Sheets Automatically

This video shows the steps for setting up the master sheet and macros, to add month sheets automatically in a workbook. Written details are below the video.

Add Month Sheets Automatically in Excel - YouTube

Create a Master Sheet

The first step is to set up a Master sheet in your workbook. In my sample file, the sheet is named wkst_Master.

It has a named Excel table, tblSales, starting in cell A1.

Master Sheet Freeze Pane

To save time in the new sheets, be sure that the Master sheet is set up exactly the way you want it.

In my sample file, there is a Freeze Pane setting, with the first row frozen at the top.

This keeps the table's heading row visible, when you scroll down on the sheet.

Master Sheet Selected Cell

Another setting that I made on the Master sheet was to select cell A1. It's a minor thing, but it saves you the time of selecting that cell  on each new sheet that's created.

Macro to Add Month Sheets

In the sample file, there is a macro to add month sheets automatically, as needed.

This macro, named AddMonthWkst, is stored on a regular code module, named modSheets, and does the following steps:

  • sets the sheet which will be used as the Master
  • 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.

Sub AddMonthWkst()
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
    wsM.Copy After:=Sheets(1)
    ActiveSheet.Name = strName
End If

Set wsM = Nothing
End Sub
Workbook Open Code

To make that macro run automatically when the workbook opens, there is code in the ThisWorkbook module too.

The code is in the Workbook_Open event, and all it does is run the AddMonthWkst macro.

Private Sub Workbook_Open()
End Sub
Test the Month Sheets Macro

After you add the Workbook_Open code and the AddMonthWkst macro code to your workbook, close the workbook, and then open it again.

If a security warning appears, click Enable Content, to allow the macros to run.

Then, if the workbook doesn't already have a sheet for the current month, a new sheet will be automatically added, named with the year and month.

Get the Sample File

To see how the code works, and add month sheets automatically, get my sample file from my Contextures website.

Go to the Excel Worksheet Macros page, and get the sample file from the Download section.


Add Month Sheets Automatically


Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

Here's a screen shot of the small table that's the source for the pivot table. You can download this sample file from the Pivot Table Summary Functions page on my Contextures website.

Error Values in the Data

Obviously, there are some problems with that data.

  • There is text in cell C4, so the formula in E4 has a #VALUE! error, instead of a numeric result
  • There are three #DIV/0 errors, because Excel can't divide by zero
  • There is a blank cell (E7), where a formula has been deleted
  • Two cells (C4 and E9) contain text, instead of the numbers that should be in those columns
Create a Pivot Table

What happens if you create a simple pivot table based on that error-filled data?

Things looked fine when I created this pivot table, with Region in the Rows area, and Count of Total in the Values area.

The error values have disappeared from the pivot table, and the counts for each Region are showing correctly.

Change to Sum Function

However, things take a turn for the worse if you change the Summary Function.

Instead of a Count of the Total amounts, try these steps to see a Sum.

  • Right-click on one of the numbers in the Count of Total column
  • Click Summarize Values by, and click Sum

As soon as the function changes to Sum, errors appear in the pivot table, for the East region, and in the Grand Total.

Just Like Worksheet Functions

The Pivot Table Sum function is like the worksheet SUM function, and it returns an error value, if there's one in the range being summed.

The Count function in a pivot table is like the worksheet COUNTA function. It counts text, numbers and errors, and does not count blank cells.

Pivot tables also have a Count Numbers function, which is like the worksheet COUNT function. It counts numbers, and does not count blank cells, errors or text.

    Totals in the Source Data

    If you create Count, CountA and Sum totals in the source data, only the Sum totals show errors.

    Similarly, in the pivot table, Count and Count Numbers show totals.

    But Sum shows an error value.

      Exceptions to the Error Rule

      So, Count and Count Numbers shouldn't show error values in their Grand Totals.

      And they don't, if they're all alone in the pivot table.

      But, if you add another Value field, and it has errors, the Count and Count Numbers totals might suddenly show errors too.

      You'll see errors in the subtotals and totals, if these 2 conditions are met:

        • Oher summary functions are included in the pivot table, and those fields
          contain errors
          in the data
        • There are error values in the data used for the Count and Count Number

      No Errors in Data Field

      Here's another look at the pivot table from the top of this post.

      The first 2 count columns have an error in the total, because:

      • the Average of Price contains an error
      • The Total data contains errors

      However, the Count of Date column show a numeric total, because the source data doesn't have any errors in the Date field.

      Get the Sample File

      You can download this sample file from the Pivot Table Summary Functions page on my Contextures website.

      The zipped file is in xlsx format, and does not contain any macros.


      Errors in Pivot Table Totals


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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:

      1. On the Ribbon, click the File tab
      2. Click the Info category
      3. At the right, click the drop down arrow for Properties
      4. Click Advanced Properties

      NOTE: For instructions for earlier versions of Excel, see Picture Preview - Excel 2003 to 2010.

      Preview Picture Setting

      Next, follow these steps to see preview pictures:

      • In the Properties window, click the Summary tab.
      • 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.
      Desktop Thumbnails

      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.

      More on Preview Picture

      Go to my Contextures website for more information on Preview Picture in Excel for Office 365.

      For earlier versions of Excel, see Picture Preview - Excel 2003 to 2010.


      Preview Picture Problems in Excel


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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.

      UserForms for Data Entry

      The Excel UserForms for Data Entry kit shows you how to build your own UserForm, from scratch.

      That's a useful skill to learn, but if you just want a simple form built quickly, the UFB add-in is a real time saver.

      Three Simple Steps

      Select a cell in an Excel Table, and the UFB add-in will help you build a simple UserForm, based on that table.

      There are three steps that the UFB guides you through:

      • Prepare the table
      • Build and customize the field list
      • Create the UserForm

      Watch the Demo

      Here's a very short demo, that shows how easy it is to create a simple UserForm with the UFB add-in.

      Contextures UserForm Builder Quick Demo - YouTube

      Make Changes to the UserForm

      If you're an experienced programmer, you could add more features and code to the basic UserForm, after it's built.

      Or, if you don't know much about UserForms, you can make a few simple changes to the Userform's appearance, such as the height and width, or the font size.

      More Videos

      There's a longer demo video on the UserForm Builder videos page, and a video that shows how to modify the UserForm.

      Those will help you after you download the new version of the UFDE kit, and want to try the UFB add-in.

      UserForms for Data Entry Kit

      Go to my Contextures website, to learn more about the Excel UserForms for Data Entry kit, and the UserForm Builder add-in, and the other bonus files in the kit.

      And if you try the UFB add-in, please let me know if you have any suggestions for improving it!


      Excel UserForm Builder Add-in


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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.

      Automatic Status Messages

      Excel automatically shows some messages in the Status Bar, which you can't turn on or off. For example, if you point to a cell with a comment, the author's name appears in the Status Bar.

      Or, if you apply a filter, the status bar will show the number of visible records, and the total number of records. And sometimes it just shows "Filter Mode".

      Another message you might see in the Status Bar is a Circular Reference warning. If you're on a sheet that has a Circular Reference, the cell address is shown.

      There are other automatic messages too, such as query refreshes, file opening information, and things that are loading, such as the Data Model.

      Add Your Own Messages

      With programming, you can show your own messages in the Status Bar too.

      For example, in a slow-running macro, show the progress, to help people understand what's happening.

      Optional Messages in Status Bar

      There's also optional information on the Status Bar, that you can turn on or off, based on your preferences.

      Some of the items are turned on by default, when you install Excel. This Microsoft article shows the list of options, and which ones are selected by default.

      • 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:

      • Average
      • Count
      • Numerical Count
      • Maximum
      • Minimum
      • Sum

      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.

      Confusing Options

      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?


      Confusing Options on Excel Status Bar


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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.

      Here are the steps for a pivot table, and you can do the same thing for a named Excel table.

      1. Select a cell in any pivot table, and on the Ribbon, click the Design tab.
      2. In the PivotTable Styles gallery, right-click the style you want to duplicate.
      3. 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.
      More PivotTable Style Tips

      There are more PivotTable Style and formatting tips on my Contextures site.

      If you'd like to see what styles are currently in use, download my PivotTable styles macros, to create a list for your workbook.


      Excel Custom Styles Problem


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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.

      Sub AddButtonData()
      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")
      With wsData
        nextRow = .Cells(.Rows.Count, "A") _
          .End(xlUp).Offset(1, 0).Row
      End With
      With wsIn
        BtnText = .Buttons(Application.Caller).Caption
        BtnNum = CLng(BtnText)
        strName = .Range("UserName").Value
      End With
      With wsData
        With .Cells(nextRow, 1)
          .Value = Now
          .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, 2).Value = strName
        .Cells(nextRow, 3).Value = BtnNum
      End With
      End Sub
      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.

      Customization Notes

      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.

      The sample file has a cell where the name is entered, and the macro uses that value. Another option is to get the name from the Office installation, or from the network.

      Get the Sample File

      To download the sample workbook for this example, go to the Excel Sample Files page on my Contextures website.

      In the UserForms, VBA, Add-ins section, look for UF0047 - Click Button to Capture Data.

      The zipped file is in xlsm format, so be sure to enable macros when you open the file, if you want to test the buttons.


      Get Data from Excel Button Clicks


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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!

      Simple Formula

      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.

      SUMPRODUCT Comparisons

      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.

      More SUMPRODUCT Examples

      There's lots that you can do with the under-appreciated SUMPRODUCT function. See more SUMPRODUCT examples on my Contextures website.


      Compare Two Excel Tables with Simple Formula


      Read Full Article
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      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.

      Fancier Buttons

      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.

      Switch Languages

      For another example of linking shape text to worksheet cells, see the cereal box text in my "Switch Languages" blog post.

      First, you select a language from a drop down list.

      That changes the text in the worksheet cells, because INDEX and MATCH formulas find the translations in a lookup table.

      There is an orange rectangle on the worksheet, with text boxes sitting on top of it. Those text boxes are linked to the worksheet cells.

      For example, the text box at the top is linked to cell B4, which shows "Honey Nut Cheerios", when English is selected.

      When the selected language is French, the linked text boxes show the French text from the worksheet cells.


      Excel Button Text from Worksheet Cell


      Read Full Article

      Read for later

      Articles marked as Favorite are saved for later viewing.
      • Show original
      • .
      • Share
      • .
      • Favorite
      • .
      • Email
      • .
      • Add Tags 

      Separate tags by commas
      To access this feature, please upgrade your account.
      Start your free month
      Free Preview