Here’s an Excel Lookup Formula challenge to get your brain fired up. Can you solve it without doing a Google search? The problem details are shown below, and you can download the sample workbook. It has the sample data, and there are solutions too, on a different worksheet.
Quick Trick to Add or Move Pivot Table Fields
We’re still recovering from Tuesday’s Spreadsheet Day celebrations, so we’ll keep it simple today. Here’s a quick trick to add or move pivot table fields, just by typing. Watch the really short video, and there are written instructions below, if you prefer those.
Continue reading “Quick Trick to Add or Move Pivot Table Fields”
Happy Spreadsheet Day 2017
Happy Spreadsheet Day 2017! You’ve been waiting all year for this special day to arrive, I’m sure, so what will you do to celebrate today?
How to Fix Excel Data Bars
To make a chart right in the worksheet cells, use Excel Data Bars, built with conditional formatting. See how to add a standard set of Data Bars (Excel 2007 and later), and adjust their settings to make the bars look better.
And remember to mark your calendars – next Tuesday, October 17th, is Spreadsheet Day – alert your family and friends!
Multiple Copies of Pivot Table Sheet
If you need to make a quick report, Excel has a built-in command that makes multiple copies of a pivot table – one for each item in a Report Filter. See how that feature works, its limitations, and a macro that will copy the entire sheet, not just the pivot table.
Compare Pivot Table Values As Percentages
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”
Excel Hyperlinks Run Command Files
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.
Looking Back -Early Versions of Excel
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.
Excel CONVERT Function With Drop Down Lists
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”
Save Space With Excel On Demand Slicers
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.)