Excel Roundup 20131223

What happened in the world of spreadsheets last week? Here’s the latest roundup.

Contextures Posts

Here’s what I posted last week:

To narrow down the choices for data entry worksheets, you can remove used items from a data validation drop down list. That’s a big help in some sheets, where you shouldn’t choose the same item twice.

An updated version of my data validation combo box technique, that lets you add new items on the fly.

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 Card: If you haven’t sent your Christmas cards yet, you could use this Excel greeting card, from Jordan Goldmeier.

Totals: Apparently it was sporadic totals week, and nobody told me! To see how to achieve this, you can watch videos by Chandoo, Mr. Excel and Kevin Lehrbass. Pick just one, or watch all three! There is a screen shot of sporadic totals below, if you’re not sure what they are.

REST API: Microsoft’s Excel team shows how you can get started using the Excel Services REST API in the cloud.

Excel Books: Simon Murphy wonders if he should write a book about Excel development. What do you think? Maybe he could start with a chapter or two, sold as a mini-book on his website, and see how that goes.

Excel Bug: Charles Williams has found a Status Bar Calculate bug in Excel 2013, and he hopes it’s fixed soon.

Excel Front End: Scott Lyerly is looking for your input on his Excel as a front end solution. What do you think about his idea of keeping SQL statements in a database table, and using those to update the code in an Excel file?

sporadictotals01

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.

__________________________________

Add New Items to Data Validation Combo Box

There are a few versions of the data validation combo box technique on my website. The files use programming to make a combo box appear when you click, or double-click, on a cell that has a drop down list. The latest version shows how to add new items to data validation combo box lists.

Continue reading “Add New Items to Data Validation Combo Box”

Remove Used Items in Excel Drop Down

There is a new sample file on my Contextures web site, which lets you pick players for each inning in a baseball game.

You could tweak the file a bit, and use it to assign employees to workstations each day of the week, or anything similar.

After assigning employees, remove used items in Excel drop down list, to prevent them from being assigned twice.

Continue reading “Remove Used Items in Excel Drop Down”

Excel Roundup 20131216

It’s almost Christmas, so here’s an Excel-themed version of Santa Baby for your entertainment, sung by Santa Monica Excel Consultant Szilvia Juhasz.

Contextures Posts

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

Grouping: Instead of seeing dates grouped by year and month in a filter drop down, you can change a setting to ungroup them. I understand the benefits of that automatic grouping, but I’d rather see the individual dates most times.

Pivot Settings: When you add new items to a pivot table, change a setting to make them appear in alphabetical order, and show them even if a filter is applied.

I Hear Voices: If you accidentally turn on the Voice Narrator in Excel, here’s how to turn it off, so Excel stops talking to you.

  1. Tweets: 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: Did you learn any cool Excel tricks this year? Chandoo shares his favourite new trick, and read the comments to see what other people have learned.

Books: I’ve used Excel for all kinds of things, including keeping track of chapters when writing a non-fiction book. Lisa Alber explains how she uses Excel to connect the chapters in her novel.

Pivot Tables: Doug Glancy shares his technique and VBA code for using conditional formatting on a pivot table.

Excel Reports: If you’re creating reports in Excel, Charley Kyd shows how the SUMIFS, INDEX and MATCH functions can help you pull the data for the report.

Risks: Simon Murphy asks the interesting question, “Has EuroSpRIG increased spreadsheet risk?”

Excel Charts: Jon Peltier gives step by step instructions for building a multiple width overlapping column chart. You can see the final result in the screen shot below.

AndyErrBar4

What Excel News 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.

__________________________________

Ungroup Dates in Excel Filter Drop Down

By default, when you turn on an AutoFilter, dates are grouped in the drop down list. In the screen shot below, the dates have been rolled up to the years, 2013 and 2014. Here’s how you can ungroup dates in Excel filter drop down lists, in the active workbook.

Continue reading “Ungroup Dates in Excel Filter Drop Down”

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.

__________________________________