Excel tips and tutorials
You can add a UserForm to your Excel file, so it’s easy for people to enter data, without going to the table where the historical data is stored. Usually, I add a worksheet button that opens the form. But, with a simple macro, you can show Excel UserForm automatically, when workbook opens.
Continue reading “Show Excel UserForm Automatically When Workbook Opens”
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.
Continue reading “Excel Lookup Formula for Last Item in Column”
Set up a Master sheet in your Microsoft Excel workbook, and add monthly sheets automatically, based on that template. The new sheets will be named for the month and year, in yyyy_mm format.
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. Continue reading “Errors in Pivot Table Totals”
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.
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.
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.
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.
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.
Continue reading “Compare Two Excel Tables with Simple Formula”
