Category: Excel tips

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”

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

Contextures Posts

Here’s what I posted recently:

• Hide pivot table subtotals quickly, using a Ribbon command or a macro.

Other Excel Articles

Here are a few of the Excel articles that I read recently, that you might find useful.

• Here’s another reason to start using Power Query. Gašper Kamenšek shows how to get all the data from a folder.

• Jan Karel Pieterse has updated his comprehensive article on working with circular references in Excel.

• In case you missed any of the new features in Office 365, you can check this list of what was added in January 2015.

• If you don’t mind clicking though the items one at a time, ComputerWorld UK has 10 free apps to boost your Excel and Word 2013 productivity.

• Excel is easier to use if you understand basic math, so your kids might enjoy playing Prodigy, a free online math game. My 7-year-old grandson loves it!

• The Investintech blog shares 9 simple tips for working with Excel data. Of all those tips, I probably use #4 most often. How about you?

• Jordan Goldmeier is presenting at this year’s PASS Business Analytics conference, and has released an hour-long preview webinar for his session – Creating Outstanding Spreadsheet Models.

• Here is another use for pivot tables – Susan Wenograd uses them to visualize search query mismatches, for AdWords reports.

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel.

Excel Course

• Chandoo has a new online course – 50 Ways to Analyze Your Data. It is not for beginners though – you should have at least intermediate or higher level Excel skills for this course.

Excel Summit

• Amsterdam Excel Summit, April 13-15, 2015. Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year’s summit was an outstanding success, and registration will open soon, for this year’s event. Registration is now open, and you can click here to register.

Share Your Events and Articles

If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!

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”

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”

Combine data, sort a list with formulas, see Power Queryimprovements, and more, in this week’s Excel Roundup.

Continue reading “Excel Roundup 20150126”

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]

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 and understand

• Robert Lepper sent me his tip for creating a color spectrum with 2-color gradients in a range of cells, and I added it to my Formatting Tips page.

• For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.

Other Excel Articles

Here are a few of the Excel articles that I read recently, that you might find useful.

• Sohail Anwar, posting on Chandoo’s blog, shares 8 reasons why you must get better at Excel in 2015.

• Miguel Escobar uses Power Query to combine multiple files of different file types.

• Dick Kusleika tries to find the next row in a ListObject when using VBA. It’s not as easy as you’d hope.

• Jon Peltier uses a chart’s secondary axis to show the current donations as a percentage of a fundraising target.

• Charles Williams explains how Excel handles array expressions and constants in single-cell and multi-cell Excel formulas. He’s filling in the gaps in the official documentation. Have a cup or two of coffee, before you dive into this one!

• Here’s a quick Power Pivot tip from Melissa Coates (SQL Chick), so you can see the calculated measures in alphabetical order. That makes it easier to find things in a long list. You can do the same thing for the field list in a regular pivot table.

• Teylyn explains why you need to use True  or False in the last argument of a VLOOKUP formula

• Erika is a freelance writer, and she’s very proud of the budget/cashflow spreadsheet that she uses for her business. Nice shirt and mug too!

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel.

Dashboard Course

• Mynda Treacy has opened registration for her acclaimed Excel Dashboard Course, and you’ll get 20% off, if you sign up by January 22nd. I reviewed this excellent dashboard course when it first opened, and just updated my review, after taking a look at the latest version. You can read my detailed review here. The course is an even better investment now, with many new videos added.
• Mynda is also offering a free introductory webinar on How to Build Excel Dashboards. Get the details on this one-hour session, and sign up for a date and time that is convenient for you.

PASS BA Marathon

• Feb. 3, 2015 — Sign up now, for this free online preview of the upcoming Business Analytics Conference. There will be 6 live webinars, including “Productivity Revolution in Excel”, led by Chandoo (Purna Duggirala) and Avi Singh. Unfortunately, it has nothing to do with bacon, which was my first thought, when I saw the website name, “passbaconference.com”.

Excel Summit

• Amsterdam Excel Summit, April 13-15, 2015. Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year’s summit was an outstanding success, and registration will open soon, for this year’s event. Bookmark this page, for event details and registration: Amsterdam Excel Summit 2015

Share Your Events and Articles

If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!

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.

Continue reading “Excel Roundup 20150112”

Happy New Year! I hope you had a relaxing end to 2014, and great start to 2015.

If you’re working in Excel at the beginning or end of a year, dates can be a problem. Usually, you can enter the day and month, then press Enter, and Excel will add the year.

Most of the time that works well, but if you’re doing December month end reports in January, Excel will add 2015 – the current year. Remember to manually enter the year to the date, if it’s not the current year – 12/31/14. And that means typing 3 extra characters! Who has time for that?

Continue reading “Excel Roundup 20150105”

This is day 42000 in the Excel calendar, so maybe we’ll get the “Answer to The Ultimate Question of Life, the Universe, and Everything” – a thousand times!

Continue reading “Today is Excel Date 42000”

Thank you for reading my Excel Roundup every Monday, and I will be taking a couple of weeks off, to enjoy the holidays. There will be a blog post here this Thursday, and the next roundup will be posted on January 5th.

To help you celebrate, here is a no-macro animated Christmas tree, created in Excel, of course. Just move the scroll bar, to add lights, tinsel, a star, and presents, to the tree. You could use this technique for less festive projects too, like a business dashboard. Read the details on my Contextures website.

Continue reading “Excel Roundup 20141215”