Are you melting yet, in the summer heat? Stay indoors, and keep cool, while you read this Excel Roundup for summer 2018. To get Excel links and articles every week, sign up for my weekly Excel newsletter.
Here are a few of the Excel articles that I’ve published recently.
Drop Downs: Create multiple dependent drop downs in Excel – region, country, area, city. Requires 2 tables, 3 named ranges, no macros
Task List: Use this quick trick to automatically cross off tasks in an Excel To-To list. No macros – just easy formatting.
Hyperlinks: Use enhanced hyperlinks in Excel, to link to chart sheets, or activate a sheet without selecting a cell. Technique by J. Woolley.
Gantt Charts – Jon Peltier shows how to make Gantt charts in Excel — they’re bar charts used for schedules and project management. And if you do lots of work with charts, check out Jon ‘s Chart Add-in.
Bad Charts – On the Flowing Data blog, Nathan Yau explains why people make bad charts, and what you can do about it.
Budget – On the TechRepublic blog, Susan Harkins shows 3 ways to highlight key information in an Excel budget worksheet. There are a couple of budget workbooks on my site, if you’d like more examples.
Excel Help – The Stack Overflow site is a great place to get help with your Excel macros. This year is their 10th anniversary, and Joel Spolsky tells how the site got started. Joel has also worked for Microsoft and designed Excel Basic.
Education – Alfred Thompson wonders if we should do more with spreadsheets in computer science education. He links to a podcast about spreadsheets (and other less important things) -scroll down to the transcript, if you’d rather read than listen.
Excel Skills – If you want to test your Excel skills (or someone else’s), Sumit Bansal posted a list of 100 Excel Interview questions. The topics range from formatting to macros.
History – See an early spreadsheet in this April 1984 episode from the BBC Computer Literacy Project. There are lots of other episodes there too, and old software programs, like Space Invaders, that run in your browser. There’s even an Abacus program, if you’re tired of using Excel!
Slicers – In a short video, Bill Jelen shows how to get the selections from a Slicer, and list them in a cell, as a worksheet title. You’ll need Office 365 for this technique, because it uses the new TEXTJOIN function
Data Analysis – Save time with Chandoo’s 35 shortcuts and tips for data analysis. There are tips for formulas, pivot tables, charts, and more.
Data Model – Susan Harkins shows how to connect tables in an Excel data model, instead of using lookup formulas
Filter History – Doug Glancy calls this technique “History Navigation”, and it would be a nice feature on an Excel dashboard. Type in a year, and a table displays that year’s data. A drop down list stores the years as you enter them, so it’s easy to go back and check one again. Click “Clear History” to start fresh.