Excel Roundup 20150817

In this week’s roundup, take the pivot table survey, learn new chart tricks, see what’s new in Power BI Desktop, and much more.

We’re on our summer schedule now, so the next roundup will be published on Monday, August 31st.

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

1. Pivot Table Survey

In the latest collection of Excel tweets, on my Excel Theatre blog, someone said, “How do I make a pivot table? I don’t have time to try a bunch of stuff and see what works!” And, at the other end of the pivot table scale, someone else said, “if it doesn’t have a pivot table then I’m not interested.”

Do you use pivot tables? Do you love them or hate them? Here’s a 3-question survey, and I’ll share the results in the next Excel Roundup.

2. Hour and Minute Drop Downs

For a time entry worksheet, you can prevent invalid time entries with drop down lists of hours and minutes. This technique uses data validation, and no macros. However, as Jeff Weir pointed out in the comments, someone could still find a way to enter invalid times.

Remember, data validation can be defeated, even by a simple copy and paste, so don’t depend on it if you’re building nuclear missiles, or anything critical! My goal, usually, is to make a workbook easy to use, and idiot resistant (not idiot proof). How about you?

3. Power BI Desktop Updates

SQL Server MVP Reza Rad gives all the details on what’s new in Power BI Desktop 2.0 GA. It’s an easy-to-read summary of the changes, with lots of screen shots that show the changes.

If you’d rather see the changes in a video, watch this 40-minute overview from the Power BI team.

Rob Collie has written a great article on how an Excel pro can get started with Power BI Desktop, so check that out if you’re not sure where to start. Rob has a few minor complaints about Power BI Desktop, and read the comments to, to see other people’s concerns.

4. Chart Tricks

I found a couple of article with chart tricks this week. Jon Peltier explains how to plot values along the axis of a chart, in a single line, similar to the markings on a thermometer.

On her blog, Stephanie Evergreen shows how to create a slopegraph, which is based on the Excel line chart type.

5. 40 Greatest Excel Tips

Bill Jelen (Mr. Excel) has just finished his latest book, Mr. Excel XL – The 40 Greatest Excel Tips of All Time. Bill sent me an advance copy, and it’s a beautiful book, packed with great tips and colour photos, and a nice touch of humour. He even included 40 of my favourite tweets, from the weekly collections on my Excel Theatre website.

You can hear Bill talking about his book, and many other Excel topics, on John Michaloudis’ latest podcast.

6. Excel Problems

Excel is a great application, but things can go horribly wrong, as I’m sure you know. After working with a nightmare workbook, Melissa P. Esquibel shared the biggest lessons that she learned from the experience.

Becky Collins uses Excel too, and doesn’t mention any nightmares, but does have “huge enormous spreadsheets full of superfluous data”. Don’t we all? Becky shares her top 10 annoyances, and I hate it when Excel forgets what I copied too.

7. Prepare for September

September is just around the corner, and school will be starting again soon, so Tracy Duncan has 8 Excel tips and tricks for teachers and students.

Or maybe you’ll be looking for a new job in the fall. If you are, Lauren Miliotis explains how to use Excel when you’re looking for a new job.

________________

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

You may also like...

2 Responses

  1. Scott Rogerson says:

    I love your ‘blog’. I use quotes because it is a resource that I have used when I needed Excel to do something and I didn’t know how to tell it to do it correctly. I didn’t want to keep asking my former boss because then it was her work not mine. If you work in business today and don’t know how to make a simple table, create a pivot table to simplify the information that is displayed, and use the functions (like DAX) you are either too lazy or…..

    I began working for Salt Lake City with Excel 97 & 2003 experience from college courses I had to take. I was quickly asked to take two large sets of account aging data and “bump them together”. Well I got help the first month using the V Lookup function. I had one two csv files with 45,000 rows each and one of the files had 15 columns the other had 30. All I wanted to do was to find out if the balances matched. If they didn’t, I wanted to know what the difference was and which database was wrong.

    I was overwhelmed. Looking back, I chuckle because it’s pretty easy even doing it manually (sorting, deleting headers, and non-essential rows, oh and saving as an Excel workbook). When I was first doing this, I would always forget to format the notice number as text and if left as general, I would get an error. I ended up having 3 and 4 columns of data. I would use VLookup, go to the other sheet and select cell A1 then finish the arguments, including what row I wanted to return to the other spreadsheet if the notice numbers matched. I returned the amount due.

    Then it was as easy as column e putting it into a table so I didn’t have to drag and then d2-e2.

    What gets me is that my former employer had me build a HUGE spreadsheet report that required hours of monthly upkeep. It wasn’t hard, but it was manual. We had COGNOS 10.2 as well as Cognos TM1. The reports that I built this report from were derived from SQL Server with dropdown dates. I asked numerous times to get the ability to program what I needed once, then hit refresh and everything would update. I was told no. Then I took another job and they were panicking. I have a sample of the Fiscal Year 2016 (half of July 1025) data which I could share to show you the mindless duplication of tasks that was required.

    Anyway, can you sign me up for your blog, and keep up the good work.

    • Thanks Scott — great story! And it is sad to see how much time and effort is wasted in doing things the hard way, and people won’t let you help improve the process. I’ve added your name to the newsletter.

Leave a Reply

Your email address will not be published. Required fields are marked *