Excel tips and tutorials
Last week, AlexJ sent me a tip for making better worksheet buttons, and I’ll be using it from now on, instead of my old method. I’ll show you both button types, and maybe you’ll switch too. Big Bold Buttons If I’m making a workbook with several sheets, I usually add a menu sheet at the … Continue reading “Save Space With Compact Worksheet Buttons”
Did you ever print and distribute pivot table reports in Excel, only to discover – too late – that they weren’t showing the correct information? To help save you from wasted time and paper, and possible embarrassment, AlexJ is sharing his Report Diagnostic Display technique today. You might remember some of his other handy tips, … Continue reading “Excel Report Diagnostic Display”
Thanks to AlexJ for suggesting a great use for the REPT function – setting a minimum row height. He uses this technique to add a bit of spacing in his tables, so they’re easier to read. You can watch the steps in this video (or watch it on YouTube), and the step-by-step instructions are below … Continue reading “Set Row Height With REPT Function”
Last week, I was preparing reports for a client, and needed to find a quick and easy way to show or hide details in a long list. After a bit of thought, and experimenting, I found a way to do this – without macros. You could use this technique on a table or pivot table, … Continue reading “Hide Table Details With No Macros”
I use Excel tables in almost every file that I build, these days. They come with a default filter, and it’s easy to sort and filter the table’s data with those drop down lists, and clear them later. But did you know that you can clear Excel filters with a single click?
Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table. You couldn’t use Slicers to filter a table in Excel 2010 though.
A couple of months ago, I shared an example with a scroll bar that selects the dates for an Excel report. There is a pivot table on a hidden sheet, and a summary report uses GetPivotData formulas to pull data from that pivot table.
Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. Note: In earlier versions, you can use programming to change the report filters in multiples pivot tables.
With Slicers in Excel 2010, you can easily filter several pivot tables with a single click. In the screen shot below, the Slicers are filtering the Severity and Priority fields in the pivot table. However, there is a problem with Drill to Detail with Excel Slicer Filters, in some version.
On Monday, AlexJ showed us how to create a short or long drop down list in Excel. With his technique, users can see just the top customers, or all customers. That technique didn’t require macros — it was driven by a formula in the data validation source. Long or Short List Macro Today, Alex shares … Continue reading “Dynamic Excel Drop Down List”
