Excel tips and tutorials
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.
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.
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.
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.
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.
__________________________________
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.
There are days when we can hear the voices in our heads, whispering encouraging things like, “Oh go ahead – another cookie won’t hurt!” Or, “Wow, that’s an amazing formula you just built in that worksheet! You should take a break now.”
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.
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.
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?
Did you read any other Excel articles this week? Please share a link in the comments, with a brief description of the article.
_____________________
With data validation, you can control what is entered in a cell, and prevent invalid data. I usually use drop downs, to create a list of options for people to choose from.
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?
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?
Here’s what I posted last week:
Here are a few of the Excel articles that I read last week, that you might find useful:
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.
__________________________________
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.
You can see three of the time-saving features in this short video.
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:
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.
_____________________
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.
You can see three of the time-saving features in this short video.
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:
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.
________________________
