Excel tips and tutorials
Spreadsheets are already fun, but Tableau wants to make them even more exciting, with its newest smartphone app, Elastic. In this video, Tableau’s VP of Mobile & Strategic Growth, Dave Story, opens a spreadsheet file (csv format) from his inbox, and shows how Elastic works.
I don’t see any information about the expected release date or pricing.
Occasionally, for reasons unknown to anyone other than the centuries-old trolls who live in underground tunnels at Microsoft, Excel comments resize themselves. They get really wide, or narrow, and become almost impossible to read.
The cover slide in this presentation, with its 3-D column and pie charts, led me to believe that the content wouldn’t be too useful. However, a different person must have prepared the remaining slides, because they have some useful tips.
The last slide shows that the content is from Mike Alexander’s book, Excel Dashboards & Reports for Dummies, which was published last year.
In this video, Bill Jelen (Mr. Excel) and Mike Girvin (ExcelIsFun) show two different ways to insert an apostrophe at the start of a long column of numbers.
There is an interesting bit of history, starting at the 1:00 mark – Bill shows how that apostrophe, and other characters, affected cell entries in old versions of Lotus 1-2-3.
Or watch on YouTube: Add Leading Apostrophe to Column of Data
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!
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.