Excel tips and tutorials
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!
___________
There’s a sample Excel workbook on my Contextures website that uses a bit of Excel VBA to automatically add new items to an Excel data validation drop down list.
For example, if the drop down list shows Apple, Banana and Peach, you can type Lemon in the data validation cell.
Then, as soon as you press the Enter key, Lemon is added to the named range that the data validation list is based on.
The source list is sorted too, so that Lemon appears between Banana and Peach.
Someone emailed me last week, and asked if I would explain how the Excel VBA code works.
It rained (and even snowed a little) on Friday, so it was a good day to stay in, and work on a new page for the website.
If you’re interested in setting up a similar file, you can wander over to my Contextures website, and read Excel Data Validation – Add New Items.
Here’s a short video that demonstrates the file, and briefly explains how the code works.
You can watch this, if it’s too early in the day to read about Excel VBA code.
If you’d rather just play with the file, and figure it our for yourself, you can download the workbook from my Contextures website.
Go to the sample workbooks page, and in the data validation section, look for DV0021 – Update Multiple Validation Lists
It’s in Excel 2003 format, and contains macros. You can enable macros if you want to test the code.
___________
When you print an Excel worksheet, you can also print all the comments that have been added to the sheet.
You can print the Excel comments the way they appear as pop-ups on the worksheet, or print all the comments at the end of the worksheet.
If you’re using an older version of Excel, there are details here — Printing Comments in Excel 2003.
When printing Excel comments as displayed, you can either show all the comments, or just show one or more comments that you want to print.
To show all the comments, on the Ribbon’s Review tab, click Show All Comments.
To show a specific comment, select a cell that contains a comment. Then, on the Ribbon’s Review tab, click Show/Hide Comment.
If necessary, rearrange the comments, so they don’t overlap, or cover the data.
Then, on the Ribbon’s Page Layout tab, click the More button for Sheet Options
In the Page Setup dialog box, on the Sheet tab, select As Displayed on Sheet from the Comments drop down.
If you want to see how the comments will look when printed, click Print Preview
Click OK to close the Page Setup dialog box.
Instead of printing the Excel comments as displayed, you can print them at the end of the worksheet.
On the Ribbon’s Page Layout tab, click the More button for Sheet Options
In the Page Setup dialog box, on the Sheet tab, select As Displayed on Sheet from the Comments drop down.
If you want to see how the comments will look when printed, click Print Preview. The comments and their addresses will appear on a separate sheet, at the end of the worksheet’s data.
Click OK to close the Page Setup dialog box.
This Excel Quick Tips video shows how to display all the worksheet comments, and then print the worksheet with the comments displayed.
____________
“In the back of my mind, I always knew that charts would help me,” says the market researcher in this promotional video from 1982. Wow, did you really have to wait weeks for your data processing department to make your charts, back in the old days?
It’s easy to hide rows and columns in an Excel worksheet, and you or your boss or co-worker might do that when setting up an Excel file.
Occasionally though, you might have trouble unhiding Excel row and columns. There are written steps and a video below, that show how to fix the problem
