Excel tips and tutorials
In January, you read about the Excel Weight Loss Tracker in which you could enter your current height and weight, and record your weekly weight loss. That version was in pounds.
A couple of people asked about a stone/pound version, so I’ve finally created one — just in time for swimsuit season!
As part of the Excel programming series, I described how to run an Excel macro by adding a button to the Quick Access Toolbar (QAT).
If you use a macro frequently, you can add its icon to the Quick Access Toolbar (QAT). There are written instructions below the video
In this example, there is a macro named ToggleR1C1. that I use frequently. The macro toggles the worksheet reference style from letters (A1 style) to numbers (R1C1 style)
The ToggleR1C1 macro is stored in my Personal Macro Workbook, which is named Personal.xlsb.
To add that macro to the QAT, I followed the steps below.
When the Excel Options window opens, the Customize category, at the left, is automatically selected.
Next, follow these steps to add a picture (icon) to the Macro button
When you’re back in Excel, the macro icon now appears on the QAT.
When you want to run your macro, just click its icon on the QAT.
New I can I click the 8 Ball icon, when I want to toggle the worksheet reference style from letters (A1 style) to numbers (R1C1 style)
Watch this video, to see how to record your own macro, to change between R1C1 style (column numbers) and A1 style (column letters). Then, add a button for the macro on the Quick Access Toolbar.
If you use the Excel Options window frequently, you can add a command to the Quick Access Toolbar, to open that window quickly.
In this video, I show how to turn off the Excel Option Boxes, with a setting in the Excel Options window. Next, I show how to add an Options command to the QAT, so the window opens with a specific tab activated
Visit my Contextures site for more tips for using the Quick Access Toolbar with your macros, such as adding QAT buttons that run macros for a specific workbook only.
___________
In the Excel VBA Edit a Recorded Macro article, you saw the steps for making changes to an Excel macro that you had recorded.
The original range was 50 rows, and I increased both references, to make them each 500 rows.
I’ve uploaded a new video that shows the steps for editing the Excel macro, and stepping through the macro code by using the F8 key.
The full transcript is below the video
I recorded a macro in Excel and now we’d like to make a little change to that macro and test it.
Here’s the workbook where I store the macro
And to get started, there’s a security warning here.
I’m going to click Options and click, Enable this content, and OK
Now the macros will run.
To see that macro, I’ll go to the Developer tab, click Macros
Select my macro in the list, and click Edit
That opens the Visual Basic Editor, and I can see all the workbooks that are open.
Most of these are hidden and just working in the background, but here’s the workbook where I recorded and stored the code.
And those are the sheets, and here’s the module where the code is stored
In this macro, I can see all the steps that were recorded.
The change I want to make is just making this a bit bigger range.
I’ll change it there, and in the next line of code, that also had a 50
I’ve made the change, and to save that, I’ll go up to the toolbar, and click the Save button
Now, to test this revised macro:
It’s highlighting the row in the code that it’s about to run
Here’s the workbook in the background now
The next line of code tells it to filter for Binder orders
And you can see the blank sheet in the background now
We have made a revision to the macro, we saved it, tested the macro, and it works fine.
We can just close our macro workbook now, and know that our revised macro works fine
____________
I’d need wider columns to hold my golf scores, but this Excel template for golf scores might help you keep track of your annual progress.
There’s also an annual summary sheet, that calculates your average score and best score for the year.
The golf scores template uses conditional formatting to highlight the holes where you shot par or below par, so you can see at a glance how things are going.
You enter the course pars on the Summary sheet, and the conditional formatting is on the Scores sheet.
Because conditional formatting won’t allow you to refer to cells on another sheet, I named cell B8 as CoursePar, and use that name in the conditional formatting formulas.
For example, the formula for par is:
To download the Golf Scores template, go to the Excel Template – Golf Scores page on my Contextures website.
he zipped Excel workbook does not contain any macros — Excel formulas so all the work, after you enter the scores.
If you’re organizing a golf tournament, you can organize the tee off times with another one of my Excel Golf templates.
Go to the Golf Tee Off Times page on my Contextures site, to download the Excel workbook.
The video below shows how the Tee Off Times file works.
______________
You can quickly create a named range in Excel, but it doesn’t automatically expand to include new items that are added at the end of the list.
However, a dynamic Excel named list grows automatically, so here’s how to create that type of list.
Continue reading “Dynamic Excel Named List Grows Automatically”
Lots of people visit my Contextures website looking for information on the Excel Count functions.
Counting seems like an easy thing to do in Excel, if you’ve been using the program for a while. But, if you’re just starting out, it might not be so obvious.
Even if you’re an experienced Excel user, there are a few quirks with the Count functions, that you might not have noticed yet.
For example, different count functions treat “empty” cells differently, as you can see in the screenshot below.
To see the Excel Count functions that count numbers only, any data, or blank cells, you can watch this short video.
There are many more tips, examples and videos on the Excel Count functions page on my Contextures site.
To see a quick overview of 7 ways to get a total count of cells in Excel, watch this 77-second video. There are written steps for each count function on the Excel Count functions page.
_______
If you have a long list of items, you could use formulas to count how many times each item occurs in the list.
It would take a few steps, including pulling a list of unique items from the list, then creating a formula to count each item. Is there an easier way?
Continue reading “Count Items in List with Excel Pivot Table”
When you summarize data in a pivot table, it usually shows a sum of the values. (If there are blanks or text values in the field, usually the pivot table shows a count instead.)
In this pivot table, you can see the total labour cost for each Service Type.
If there are two or more fields in the Row or Column area, subtotals are automatically created for all the fields except the last one.
In the screenshot below, the District field was added below the Service Type field. Subtotals were automatically added to Service Type.
Instead of seeing the Sum of the data, you can change the summary function, and show the Average, or any of the other options. You could even put the same field in the Values area of a pivot table multiple times, and use different summary functions in each column.
In this example, the sum of the labour cost is shown in column C, and the Max function is used in column D.
If your pivot table already has lots of columns, you might not want to make it wider, by adding another copy of one of the Value fields.
Instead, you can change the summary function for the Subtotal, so it uses a different function than the Value fields.
Now the Value fields show the sum of labour costs, and the subtotal for each service type shows the highest labour cost that was charged for that service.
You can see the maximums, without adding extra columns to the pivot table.
You can read more about pivot table subtotals, and the steps for changing them, on the Contextures website: Excel Pivot Table Subtotals.
To see the steps for changing the pivot table subtotals, and creating multiple subtotals, you can watch this short video.
_____________
If Excel data is on different sheets, you can create a pivot table from multiple sheets, by using multiple consolidation ranges. My video, further down this page, shows you the steps.
Of course, it’s better if the data is all on one sheet. But, if you don’t have that option, the multiple consolidation ranges will pull all the data into one pivot table.
Continue reading “How to Create Excel Pivot Table from Multiple Sheets”
Instead of copying and pasting, you can quickly copy data from a web browser to Excel, by dragging and dropping.
To see the steps in action, you can watch this 30 second video. The full transcript is below the video.
If you want to copy something from a web browser, into Excel, you can just drag and drop
On my website, I’m just going to scroll down a little, and select some text, and links, and graphics
Then, just point anywhere, even if it’s the clicking hand
Instead of clicking, we’re going to just drag over here to Excel
Let go, and it copies the links, the text, the graphics, and you’re ready to go!
___________
