Excel tips and tutorials
Sometimes the Excel macro recorder creates code that gets you off to a good start. Other times, it’s not so helpful.
This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. One click, and the subtotals disappear, or reappear.
Let’s get the code for that, which I’m sure will be equally clean and simple. Ha!
Microsoft announced that Office for Windows 10 Preview is now available for download, and they’re working on Office 2016
You see the preview in this video, and the Excel demo starts around the 1:00 mark.
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.
If I’m making a workbook with several sheets, I usually add a menu sheet at the front, with buttons that link to the other sheets. In the screen shot below, you can see a typical menu, with buttons for two of the worksheets. Each button has a hyperlink to cell A1 on the named sheet.
They work well, but look a bit big and clunky.
Then, AlexJ sent me his tip for worksheet buttons. Make the buttons small, and let the text flow out to the right side.
The captions are clickable too, so that is an extra advantage.
Here are the steps for creating a compact button and caption:
To see how the buttons work, you can download the sample file from AlexJ’s Sample Files page on my Contextures website.
In the Excel Tips section, look for ET0001 – Compact Buttons With Captions. The zipped file is in xlsx format, and does not contain macros.
To see the steps for creating compact buttons with captions, please watch this video tutorial.
Or watch on YouTube: Save Space With Compact Excel Buttons And Captions
__________________
Happy Groundhog Day! Please feel free to read this roundup over and over again.
Poynter posted a couple of videos from the early days of Lotus 1-2-3, which was released 32 years ago, on Jan. 26, 1983. In this video, you can see how exciting a workday becomes, when new spreadsheet software is installed. Does Excel ever make you feel like dancing?
This Sunday is Super Bowl XLIX – the last one that will start with “XL”. Next year, it will be Super Bowl L – that doesn’t exactly roll off the tongue!
In 2011, I showed how you can use the ROMAN function in Excel, to change a number into a Roman numeral. That year, they were playing for the 45th time, which was XLV.
=ROMAN(A2)
There are other Excel functions that sound like they could be used in a football game. Here are a few, and I’m sure you can think of others:
Okay, that last one was a bit of a stretch, but I’ll allow it. What functions did I miss?
While you wait for the game to start, you can look back at some historic games, with the data that Kevin Lehrbass compiled in an Excel file. You can download Kevin’s workbook, which contains enough data to entertain you for a couple of days, while you try to decide which was the closest game.
Did Kevin think of all the factors that make a football game close, and exciting to watch?
You can even select a couple of Super Bowls, and compare them. Have fun, and enjoy Sunday’s game!
_____________________
Combine data, sort a list with formulas, see Power Queryimprovements, and more, in this week’s Excel Roundup.
Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I’ve done some work with Power Pivot, and was impressed by what could be done with that add-in. But, despite its capabilities, I haven’t been using it for big projects, or client work.
Recently though, I’ve been testing Power Query, and the things that it can do are very exciting. I’m just getting started with this add-in, but was amazed by how easy it is to combine data on two different worksheets.
Once the data is combined, you can filter and sort it, all in one place. Or, create a pivot table from the combined data. It’s much easier, and with better results, than the old methods of working with Multiple Consolidation Ranges.
In this tutorial, from the Lynda.com website, you can learn 5 helpful Excel keyboard shortcuts, for tasks such as toggling the Ribbon on and off, and showing a print preview.
I use the mouse, and buttons on the QAT, for most of these commands, but if you’re a keyboard person, you might like a couple of these shortcuts. Or did you know all of them already?
[Update is no longer available]
Here’s what I posted recently:
Here are a few of the Excel articles that I read recently, that you might find useful.
Here are some upcoming events, courses, recently published books, and other new items, related to Excel.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
If you’ve highlighted cells with conditional formatting, what’s a quick way to delete the rows those cells are in? Someone asked that question on one of my old blog posts last week. That article showed how to use the Find command, to get a list of cells that contain a specific word. Then, delete the rows for those cells.
It’s a handy trick, but won’t work to select cells that are colored with conditional formatting.
Continue reading “Delete Rows With Conditional Formatting Color”
Doug Glancy used Excel’s camera tool, and some VBA, to create a mesmerizing “selfie” of a range which has conditional formatting applied. Doug describes the results:
Kind of like when you’re in a dressing room with the mirrors front and back and you get a reflection of a reflection of a reflection … Unless you’re like me and you just keep buying the same pants online.
I downloaded the sample file, and changed the colours, to match my Contextures logo. I’m not sure if this has too many practical applications, but perhaps you can use it to hypnotize your boss, and get a big raise.
