Author: Debra Dalgleish

Excel Roundup 20131209

In this week’s video, you can see that Gwyneth Paltrow uses Excel to plan her holiday spending, just like the rest of us. Her budget might be a bit bigger than ours though! She mentions Excel around the 1:00 mark.

Contextures Posts

Here’s what I posted last week, on my Contextures site and blogs:

Instead of using several clicks to clear pivot table or Excel table filters, add a button that clears them all with one click.

Make it easier for people to enter data in your worksheet, by showing input and error messages on data validation cells.

Finally, for a humorous peek at what other people are saying about Excel, read this week’s collection of Excel tweets, on my Excel Theatre blog.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

Excel Tricks: On the Ask a Manager blog, someone asked, “What’s the coolest Excel trick you know?” Read the comments to see the tips — and there are lots!

Power Pivot: Rob Collie shows a simple, and useful, trick for combating “stale” Slicers in PowerPivot.

Health: On his Freewheel Burning blog, Pete Larson is dismayed to learn that the fancy new heart risk calculator he heard about is just an Excel workbook. Can Excel really help save lives?

Sparklines: This sparkline tip was in a Tableau article, but could apply to your Excel sparklines too.

Excel Crashes: Ryan Kelly had a problem with Excel crashing when he used VBA on his MacBook, and explains how he fixed it.

Excel Help: Has your department stopped calling IT for help with Excel projects, because Excel gurus like you are doing the work, as Simon Murphy found?

What Did You Read?

Did you read any other Excel articles this week? Please share a link in the comments, with a brief description of the article.

_____________________

Periodic Table of Excel Keyboard Shortcuts
Periodic Table of Excel Keyboard Shortcuts

Clear Excel Filters With a Single Click

I use Excel tables in almost every file that I build, these days. They come with a default filter, and it’s easy to sort and filter the table’s data with those drop down lists, and clear them later. But did you know that you can clear Excel filters with a single click?

Continue reading “Clear Excel Filters With a Single Click”

Excel Roundup 20131202

Excel websites have been around for a long time, answering your spreadsheet questions. Last week, Bill Jelen celebrated the 15th anniversary of his Mr. Excel website, and posted this video. In it, Bill gives an updated answer to the first question that he received.

Were you using Excel way back then?

Contextures Posts

Here’s what I posted last week:

  1. It’s easy to remove duplicates in an Excel worksheet list. Just remember to make a backup before you start.
  2. To get a count of distinct (unique) items in a pivot table, use the free Excel add-in – PowerPivot. It’s available in some versions of Excel 2010 and 2013.
  3. If you’re planning a dinner, use Excel to figure out when everything has to go into the oven.
  4. Finally, for a humorous peek at what other people are saying about Excel, read this week’s collection of Excel tweets, on my Excel Theatre blog.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

  1. This could cause problems — Cameron Lackpour shows how multiple workbooks with links to the same source update each other.
  2. On a lighter note, Charles Williams went to the MVP Summit at Microsoft’s Seattle headquarters, and shared a few pictures from that event.
  3. If you haven’t used Microsoft’s Power Query add-in yet, Arshad Ali shows how to get started
  4. It’s too late for Thanksgiving, but you could use this next year. Stephen shows how to cook a turkey with Excel, by calculating the time to thaw and cook the bird. You’ll have to use an oven though – you can’t actually cook the turkey with Excel.
  5. On the MSDN blog, Lukas Steindl uses Power Map in Excel, to analyze tropical storm activity from 1945 to 2012. It also shows the recent Haiyan typhoon

What Did You Read?

If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.

Please include a brief description, and a link to the article.

__________________________________

Cyber Monday Sale Pivot Table Tool

It’s Cyber Monday, and we’re all supposed to spend the day shopping online, so I’m having a sale on my Pivot Table add-in – PivotPower Premium.

You can get it today for 30% off, and the sale ends at midnight (Eastern time) tonight, Dec. 2nd. You don’t need to enter a discount code – the priced will be reduced automatically when you add it to the shopping cart.

See the Key Features

You can see three of the time-saving features in this short video.

  • save and apply your favourite pivot table settings
  • clear old items from a pivot table
  • change all the data fields to the Sum function.

Time-Saving Features

The add-in is easy to install, and it creates a new tab on the Excel Ribbon. You can see some of the other tools in the screen shots below. For example:

  • Click to set Currency or Number format
  • Print a list of pivot tables or cache information
  • Turn Autofit on or off

pppribbonleft2

pppribboncenter2

pppribbonright2

Get the Discount

To see all the details, and buy the add-in at 30% off, please visit this page on my Contextures website: – PivotPower Premium.

The sale lasts until midnight December 2nd, 2013, Eastern Standard time. No discount code is required – the price will be automatically reduced in the shopping cart.

_____________________

Excel Tools Add-in Black Friday Sale

It’s Black Friday in the USA – the busiest shopping day of the year. This event has spread to Canada too, so I’m having a sale on my latest Excel add-in – Contextures Excel Tools.

See the Key Features

You can see three of the time-saving features in this short video.

  • Click to make a backup of the active workbook
  • Protect or unprotect all sheets, with or without a password
  • Create a menu sheet, with a hyperlink to each worksheet in the file

Time-Saving Features

The add-in creates a new tab on the Excel Ribbon, and you can see some of the other tools in the screen shots below. For example:

Get the Add-in

To see all the details, and buy the add-in at 50% off, please visit this page on my Contextures website: Excel Tools add-in.

The sale ends at 11:50 PM tonight, Eastern Standard time – November 29, 2013.

________________________

Excel Timetable for Your Holiday Dinner

Happy Thanksgiving, if you’re celebrating today! Our Canadian Thanksgiving was last month, so I don’t have to worry about organizing a big dinner today.

If you’re responsible for getting the family meal on the table today, you can use my Excel Holiday Dinner Planner.

Excel holiday dinner timetable template
Excel holiday dinner timetable template

Yes, it takes a few minutes to set up, by entering all the food items, and the preparation steps, but it will be worth the effort! You probably don’t vary the holiday menu too much, so you can reuse the worksheet, year after year.

Calculate the Start Time

After you enter all the dinner items, go to the top of the worksheet, and select the time that you want to serve dinner. Automatically, the Excel dinner planner calculates the preparation start time for each item.

Select the dinner start time
Select the dinner start time

Follow the List

With the planner, you’ll have a complete list of dinner items, with preparation start and end times. Follow the list, and you won’t be likely to forget those dinner rolls in the oven, or leave the cranberry sauce in the fridge.

You can print a list, or a Gantt chart version, to show the preparation schedule.

Gantt chart to show dinner preparation schedule
Gantt chart to show dinner preparation schedule

You can find more instructions, and download links, on the Excel Holiday Dinner Planner page on the Contextures website.

Enjoy your Thanksgiving dinner!

Happy Thanksgiving!
Happy Thanksgiving!

_________________

Remove Duplicates in Excel 2013 List

If I need a list of unique items from a long list, I usually use an Advanced Filter, because it leaves the original list alone, and extracts a list of unique records.

The filter can be done in place, or sent to another range, on the same sheet, or a different sheet. It’s easier in newer versions of Excel though – here’s how to quickly remove duplicates in Excel 2013.

Continue reading “Remove Duplicates in Excel 2013 List”

Excel Roundup 20131125

Forget that old-fashioned mouse! Now you can control Excel during a presentation, by using your phone. Well, assuming that you have Excel 2013, and a Windows phone.

There is a link to the Office Remote app download page in the Excel Articles section below.

Contextures Posts

Here’s what I posted last week:

  1. Use MAX and IF with multiple criteria, to find the latest date that a product price was changed, for a specific customer.
  2. Set up a pivot table so it shows missing items, and add temporary data, if necessary.
  3. No, it’s not the best way to present data, but if you have to make a pie chart, keep it simple and easy to read.
  4. Finally, for a humorous peek at what other people are saying about Excel, read this week’s collection of Excel tweets, on my Excel Theatre blog.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

  1. The IT Faculty of the Institute of Chartered Accountants in England & Wales has posted a draft version of their Twenty Principles for Good Spreadsheet Practice, and they would like your feedback.
  2. Microsoft Research just released its Office Remote application, which lets you use your Windows phone to navigate through an Excel 2013 workbook during a presentation.
  3. On the Visual.ly blog, someone shared their map of Australia and a column chart, both created by colouring worksheet cells.
  4. It doesn’t have to be complicated! Learn an easy way to make bullet charts and boxplots in Excel, on the Excel Charts Blog
  5. If you’re using APIs to pull data for Excel apps, be sure to format the numbers correctly. The Office Developer Team shares some sample code.
  6. Instead of using macros, add a hyperlink to a shape, for workbook navigation. Mynda Treacy shows the steps.
  7. When you’re programming an Excel file, it’s nice to have a quick way to open the workbook, with everything unlocked and ready to edit. Scott Lyerly shares his trick for adding a back door to your Excel files
  8. Do you love pivot tables? Data journalist, Nassos Stylianou loves them too, and shares a few tips on the Digitally Focused blog.
  9. If you need to calculate the last day of any month, you can use Chandoo’s simple formula.

What Did You Read?

If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.

Please include a brief description, and a link to the article.

___________________