Excel tips and tutorials
If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality.
Last year, Excel MVP Kirill Lapin (aka KL) shared his brilliant code to create a Union query and build a fully functional pivot table from data on different worksheets.
Continue reading “Macro Creates Excel Pivot Table From Multiple Files”
If you’ve added comments to an Excel worksheet, you have a couple of built-in options for printing the comments.
Printing the comments on the worksheet is okay if there are only a couple of comments, and you can arrange them so they don’t cover the data.
For more than a couple of comments, the list at the end of the worksheet is a better choice.
However, with the built-in list printing option, you just get the cell address and comment, printed in a long, single column.
Instead of using the built-in list of printed comments, you can use a macro to create your own list of comments on a separate worksheet, and print that list.
It’s also a great way to review all the comments on a worksheet, and use sorting or filtering to focus on specific comments.
Shown below is the Excel VBA code to create a list of comments from the active sheet, written by Dave Peterson.
For more comment programming examples, including Dave’s code to list all the comments in the entire workbook, see Excel Comments VBA.
The ShowComments macro adds a new sheet to the workbook, and lists all the comments, the comment author name, and the comment cell’s value, address and name (if any).
At the end of the macro, the first row is formatted in bold font, and the column widths are autofit.
Sub ShowComments()
'posted by Dave Peterson
Application.ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set curwks = ActiveSheet
On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If
Set newwks = Worksheets.Add
newwks.Range("A1:E1").Value = _
Array("Address", "Name", "Value", "Author", "Comment")
i = 1
For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Address
.Cells(i, 2).Value = mycell.Name.Name
.Cells(i, 3).Value = mycell.Value
.Cells(i, 4).Value = mycell.Comment.Author
.Cells(i, 5).Value = mycell.Comment.Text
End With
Next mycell
With newwks
.Rows(1).Font.Bold = True
.Cells.EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub
_____________
Last week, I posted Bob Ryan’s Excel macro for formatting a pivot table in Classic style. Bob’s macro formats the first pivot table indexed on the active sheet.
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
Ideally, you’d only have one pivot table on a worksheet, to prevent problems with overlapping, and Bob’s code would work very well. However, as you know, life in Excel isn’t always ideal!
Let’s look at a few scenarios, and how to modify the macro to deal with them.
In the blog post comments, Yard suggested a variation on the code, so the macro would run on the selected pivot table, to accommodate worksheets with multiple pivot tables.
If a cell in a pivot table isn’t selected, an “Oops” message would be displayed.
On Error Resume Next
Set PT = ActiveCell.PivotCell.PivotTable
On Error GoTo 0
If PT Is Nothing Then
MsgBox "No PivotTable selected", vbInformation, "Oops..."
Exit Sub
End If
Thanks, Yard, for your sample code. On a multiple pivot table sheet, the user can control which pivot table is formatted.
Taking that idea a bit further, let’s assume you have a worksheet with several pivot table on it. With Yard’s code, shown above, you could select a cell in one of those pivot tables, and run the macro to format that pivot table only.
But, what if you wanted to format all the pivot tables on that sheet? It would take a while to select each pivot table, and run the macro. Instead, you could modify the code, so it formats all the pivot tables on the active sheet.
For Each PT in ActiveSheet.PivotTables
'the formatting code goes here
Next PT
Finally, what can you do if there’s more than one worksheet with pivot tables? You don’t want to waste time selecting each worksheet, and running the macro to format all the pivot tables on that sheet.
To loop through the worksheet, you could modify the code, so it formats all the pivot tables on each worksheet in the active workbook.
Dim ws as Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each PT in ws.PivotTables
'the formatting code goes here
Next PT
Next ws
______________
If you’re filling in an order form in Excel, you can use the VLOOKUP function to find the selling price for each item in the sales order. For example, in the screen shot below, the order form is on the Orders worksheet, and a VLOOKUP formula in column D pulls the cost from a pricing table on the Prices worksheet.
It’s easy to protect a worksheet in Excel, but it’s not so obvious how you leave some of the cells unprotected, to allow changes on a protected worksheet. You can follow this tutorial to learn how to do that, and maybe you’ll even see the weird dialog box heading that I show below.
Do unwanted files open automatically when Excel starts? Perhaps something changed in your computer, and Excel files are opening automatically, and you want to get rid of them. Keep reading, to see where those files might be located, and how to stop them from opening.
Continue reading “Unwanted Files Open Automatically When Excel Starts”
My friend and client, Bob Ryan, from Simply Learning Excel, has just published a hands-on, no fluff, Excel book — Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less.
To celebrate the book launch, I asked Bob to share one of his favourite Excel tips with you, and you can read Bob’s answer below.
Bob Ryan’s top Excel tip:
Some time ago, I was getting ready to train a group of people about PivotTables. As I was documenting the steps, I started feeling more and more annoyed at the number of steps it took to create the kind of PivotTable I typically use.
So, I wrote a macro to automate the steps. (I also submitted a suggestion to Microsoft to allow users to create a customized standard/default PivotTable, but I don’t see it in Excel 2010.)
I wanted to share this macro, but since my website is generally geared to folks who don’t know about or need macros (yet), I asked Debra if I could be a guest writer, and she kindly agreed.
A final note: While I appreciate Debra’s willingness to share this information on her site, the content really belongs to her because most of this information came from her books, website, and/or her personally. I hope you find this useful.
Once you insert a PivotTable and enter a field(s) into the Values area, the code does the following to PivotTable(1) on the active sheet:
You can view the pivot table formatting macro code, and download Bob’s Format Pivot Table Macro sample file.
The file is zipped, and in Excel 2007 format. Because it contains a macro, you’ll have to enable macros when you open the file.
Thanks, Bob, for sharing your pivot table macro!
To see how much time you can save by using a macro to format a pivot table, watch this video.
It took me a couple of minutes to manually format the Excel pivot table, and change some of the pivot table options, and just a couple of seconds to do all the same steps with Bob’s macro.
Note: If you record your own pivot table formatting macro, follow Bob’s example to add variables, so the macro works on any pivot table, no matter what the field names are, or where it’s located.
Robert Ryan, MBA, CPA is a long-time passionate user of Excel, the author of “Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less,” a unique step-by step book designed for basic to intermediate users, and the host of “Ask the Author… LIVE!™” where Bob answers questions from readers of his book in live WebEx sessions at no extra cost.
_________________
This week, there were a couple of Excel conditional formatting questions in the blog comments.
I’ll answer the questions here, so they’re easier to find. Maybe you’ve encountered similar conditional formatting problems, and this will help.
The first question is from Ron, who wants to change the font colour, instead of the fill colour:
I am trying conditional format a range of three cells where the FONT of the highest value in the range will be in Red follow by Blue and Green. I know it can be done using cell fill but, with a large spread sheet, it will look like dog’s breakfast. Any help would be appreciated. Also can this be done across worksheets (i.e. three different worksheet in the same file)
Ron didn’t say that the three cells were adjacent, in the same row, but I made that assumption in my example, shown below.
To find the highest, second highest, and lowest values for cells C2:E2 with a worksheet formula, you could do the following:
The column references are absolute, so that all 3 cells in the row will refer to the same range.
You can use the same formulas in conditional formatting, compared to the active cell, and add the font formatting for each formula.
To set up the conditional formatting for the highest value:
Now the highest values are highlighted in each row.
To set up the conditional formatting for the second highest value:
To set up the conditional formatting for the second highest value:
Now the highest value in each row is highlighted in red, the second highest is blue, and the lowest is green.
To answer the second part of Ron’s question — conditional formatting won’t let you refer to cells on a different worksheet, or in a different workbook.
However, you can refer to a workbook level named range that’s on a different worksheet, but that wouldn’t help in the example shown above.
For example, you could highlight all the cells on Sheet1 that are higher than the maximum allowed, if the maximum is on Sheet2, in a range named MaxAmt.
=C2>MaxAmt
And here’s another example of referring to a named range in conditional formatting.
The next conditional formatting question came from Guido:
need conditional formatting based on the multiple of the number oin another cell. How to do that?
For ex: in the formatted cell can only be the multiple of the number in the other cell, otherwise it colours for ex red
To check this on the worksheet, you could use the MOD function:
=MOD(D2,C2)
The MOD function returns the remainder after a number is divided by divisor. If the result is zero, then D2 is a multiple of C2.
Shown below is the sample data for this conditional formatting example.
To add the conditional formatting, and highlight the non-multiples in column D:
Now the non-multiples in column D highlighted in red.
There are more Excel conditional formatting examples on the Contextures website.
______________
In Monday’s blog, you saw how to make simple dependent data validation drop down lists. After creating the drop downs, you added some flexibility by using the IF function in the data validation formula. See a couple of problems that can occur when you refer to other cells in your data validation, and those cells are blank.
Continue reading “Ignore Blank Problems in Excel Data Validation”
You can use data validation to create drop down lists in Excel. With a bit of Excel magic, you can create dependent drop down lists, so the selection in one drop down controls what appears in the next drop down. You’ll see different drop down lists in the same cell!
Continue reading “Different Drop Down Lists in Same Excel Cell”
