Excel tips and tutorials
As you know, pivot tables are a quick and easy way to summarize a huge table of data in Excel. With just a few clicks, you can create totals for thousands of rows of data, without writing a single formula. Then, with filters and Slicers, zero in on specific parts of the data. But there’s an underused feature that you might be missing – do you know how to compare pivot table values as percentages?
Continue reading “Compare Pivot Table Values As Percentages”
You can use Excel hyperlinks to navigate through a workbook, go to web pages, open other Excel files, and even open PDF files. However, if you try to use an Excel hyperlink to run command files, you’ll run into problems. Until now! Here’s how J.Woolley uses hyperlinks, with a background macro, to avoid those problems – and a other problems too. He also found a strange hyperlink bug.
While clearing some of the boxes in the basement, I found a stack of old MacUser and MacWorld magazines. A couple of issues had articles on early versions of Excel (it started on the Mac) , so that was a good excuse to stop working, and flip through the magazines. I wasn’t resting, I was researching! Here are some pro tips from Excel 1.0, and a review of the exciting new features in Excel 2.2, and new feature lists from Excel 2007 and beyond.
With the Excel CONVERT function, you can change quantities from one measurement type to another. For example, a hot day in Celsius looks even worse in Fahrenheit! Get my updated workbook for the CONVERT function, with drop down lists for category, units and prefixes.
Continue reading “Excel CONVERT Function With Drop Down Lists”
In one of my sample files, you can select criteria for an Advanced Filter with Slicers. There are 3 Slicers at the top of the sheet, and an output range below that. And now, AlexJ has created a compact version of that technique, with the Slicers hidden until you need them. (He’s on a mission to rid the world of space-hogging Slicers.)
Slicers make it easy to select from a list of items, but they take up valuable space on your worksheet. To get the benefits of a Slicer, without the real estate cost, AlexJ made this Excel Pop Up Selector – it’s hidden until you click the small button on the sheet, and disappears again, after you use it.
If you’ve been using Excel since the Stone Age (like me!), you might be avoiding Power Query, Power BI, and all those other “Power-y” tools that the kids are using now. However, those tools aren’t going away, and pretty soon your employer, or your clients, will expect you to know something about them. Here’s how you can learn more about Power BI, to see what all the fuss is about.
The Quick Access Toolbar (QAT) is at the top of the Excel window, and comes with a few default commands installed. See how to customize the QAT, use shortcuts to run the commands, and how to save the QAT changes, so you can use them again later.
Let’s file today’s blog’s post under “Obscure Excel Problems”. I heard from Y.B., who was using my Data Validation Combo Box, and numbers were being entered as text. The code is supposed to fix them, so why was it failing?
If a cell contains a full address, what formulas would you use to show the street address, city, state and zip code in separate cells? That was the challenge that I gave to my weekly newsletter readers. A few rows of the sample data are show below, and you can download the “Split Address with Excel Formulas” sample file, to see all the data.
