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

Combine Data on Two Worksheets

Combine tables with Power Query http://blog.contextures.com/

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

Continue reading Combine Data on Two Worksheets

Excel Roundup 20150119

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

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?

Contextures Posts

Here’s what I posted recently:

You can delete worksheet rows based on a specific Conditional Formatting color Give your Excel Pivot Table a makeover so the data is easier to read

Continue reading Excel Roundup 20150119