Excel Roundup 20150302

Weekly Excel Roundup http://blog.contextures.com/

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.

Continue reading Excel Roundup 20150302

Resize Excel Comments With Macro

Resize Excel Comments With Macro http://blog.contextures.com/

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.

Continue reading Resize Excel Comments With Macro

Excel Roundup 20150223

Weekly Excel Roundup http://blog.contextures.com/

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.

Continue reading Excel Roundup 20150223

Excel Roundup 20150216

Weekly Excel Roundup http://blog.contextures.com/

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.

Continue reading Excel Roundup 20150216

Hide Pivot Table Subtotals

Remove pivot table subtotals VBA http://blog.contextures.com/

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!

Continue reading Hide Pivot Table Subtotals

Excel Roundup 20150209

Weekly Excel Roundup http://blog.contextures.com/

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.

Continue reading Excel Roundup 20150209

Save Space With Compact Worksheet Buttons

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 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.

The buttons are wide enough to

Continue reading Save Space With Compact Worksheet Buttons

Excel Roundup 20150202

Weekly Excel Roundup http://blog.contextures.com/

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?

Continue reading Excel Roundup 20150202

Football Fun in Excel

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)

Other Football Functions

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:

CONVERT RECEIVE YIELD

Continue reading Football Fun in Excel

Excel Roundup 20150126

Weekly Excel Roundup http://blog.contextures.com/

Here is a short video, by Dennis Taylor from Lynda.com, that shows some keyboard shortcuts for formatting on a worksheet. For example:

Ctrl + Shift + - (hyphen) to remove borders in selected cells Ctrl +5 to change text to Strikethrough

He also shows how to use the ISFORMULA function, new in Excel 2013, with conditional formatting, to dynamically highlight cells that contain formulas. You can get written instructions for that technique on my website.

Continue reading Excel Roundup 20150126