Excel Roundup 2020-12-31

Happy New Year! To wrap up the old year (goodbye 2020!), here’s a roundup of Excel articles and links. I hope you find a few interesting things to read, and new Excel features to try.

Contextures Articles

First, here are a few of the Excel articles and sample files that I’ve published recently, and one of my latest Excel videos – Combine Text and Formatted Numbers in Excel

There is a full written transcript at the end of the page.

Dependent Drop Downs: Drop downs make it easy to enter data in a table. Dependent drop downs are based on your selections in other drop downs. My latest example has 3 levels of dependent drop downs, and it uses the new Excel functions, FILTER and SORT

Excel Error: Have you ever seen this Excel pivot table refresh error, with Data Model data? Thanks to UniMord, who told me about this strange error, and how he fixed the problem.

UserForms: See how to use a check box, option buttons and a listbox, on an Excel UserForm. Make selections, and the data is added to cells on the worksheet. Take a look at the code, to see how it works, then add similar features to your own projects. Look for sample file UF0051 – UserForm Controls Demo.

Data and Dashboards

Clean Your Data — Sometimes, the worst part of working with data is cleaning it up. To help save time, use these data cleanup techniques, from the Investintech blog.

Dashboard Setup: Mynda Treacy has a Power BI dashboard tutorial. There’s a step-by-step video, and you can download the sample workbook, to follow along. Mynda also has a Plan vs Actual Excel dashboard tutorial, with a video and sample file.

Dashboard Tips — There are lots of great tips and examples in this Dashboard Design Essentials article by Spencer Baucke (via David Napoli)

Slicers: Robert Mundigl shows how to use table Slicers on an Excel dashboard, with a simple example file to download. You can download the fancy dashboard workbook too, to see how it works.

Functions

Spill Functions: Thanks to Dermot Balson, who sent me his Excel workbook with examples for all the new functions (dynamic arrays) in Excel for Office 365. To get the sample file, go to the Excel Spill Functions page on my Contextures site.

TEXTJOIN: In Excel 365 and Excel 2019, use TEXTJOIN to combine text from multiple cells, quickly and easily. It’s much easier than using CONCATENATE or the ampersand (&). To see a couple of easy TEXTJOIN examples, watch my new “Get Started” video.

XLOOKUP: Have you tried Excel’s new XLOOKUP function yet? Chandoo has 13 examples that show you how XLOOKUP works.

LET: In this video, Mynda Treacy shows clear examples for the new Excel LET function (currently in beta), with a handy troubleshooting tip at the end. You can define names in a formula, assign a value to each name, then use those names in the calculation.

Excel Tips

Excel Tips: Jon Acampora shares his tips for naming Excel files, to keep things organizced

Mouse Wheel: On my Pivot Table blog, see more details about last week’s tip for using the mouse wheel to quickly show and hide Excel pivot table details.

Speed: See what Microsoft has done to improve Excel’s performance recently, and settings you can change on your computer.

Learning Resources

Excel Tips — Watch Excelling in Excel, a mini course from the University of Edinburgh. There 3 videos – data organization, tips and tricks, data visualisation

Excel Skills: Take a look at the free 4-week online Excel course, from the University of Colorado – Everyday Excel Part 2. It covers advanced data management, Excel for financial applications, and more.

Spreadsheets – The Essential Spreadsheets guide, on the University of York site, shows how to do things in Excel and Google Sheets, with written steps, videos and sample files. It’s interesting to see the comparisons!

Resources: There’s a helpful collection of Excel learning material on the Dalhousie University library site. You’ll find workbooks for beginners and experts, and exercises for working with data in Excel.

Programming: If you’d like to get started with Excel programming, check out this free 3-part course from the University of Colorado Boulder – Excel/VBA for Creative Problem Solving. (Level – Programming)
_____________________

Video Transcript

In this video, you’ll see how to combine text and numbers and then format those numbers if necessary, so they show us proper dates or currency or other formats. This is Debra Dalgleish from Contextures.com

Simple Formula

On this worksheet, we have a column with text and a column with numbers.

In each text cell, I’ve ended with a space character. So when I combine the text and the number, there’ll be a space character between them.

I’m going to create a very simple formula.

-Start with an equal sign, then click on the first cell.

-Type an ampersand (shift 7).

-Then click on the number, press Enter.

And it nicely combined the text and the number.

Now if I point to the fill handle, and double click to copy that down, all the others are combined, but there’s no formatting.

Add Formatting

We’re going to start with an equal sign and click on the text cell, then our ampersand.

And I want it to show the number, but formatted.
To format the number, I’m going to use the TEXT function.
It needs a value, which is our number here, then a comma.

Then how do we want this formatted? We’re going to put something inside double quotes. We’re going to give it a text string to use, as the guide for formatting.

I would like to see the full weekday name, and to do that, I’m going to type dddd, another double quote, and close the bracket.

Press Enter, and it says, Today is Wednesday.

Another Date Format

Now in this cell, instead of seeing the weekday name, maybe I’d like to see this day-month format. So I’ll copy this cell down to give us a starting point.

And then in our formula, instead of dddd, I would like d-mmm, press Enter. And there’s the date formatted just the way we have it here

Add the Year

Now, I could copy this down to the next cell, or maybe I’d like to include the year in that one — d-mmm-yyyy

I don’t need it in the next cell. I could have it on another sheet. And I could use that in my text function instead of typing the format each time.

Click on that cell and press Enter.

More Formats

Now in this cell, I’d like currency. So in this cell I’ll type the currency format.

So the number signs mean there may or may not be a thousands or hundreds or tens, but I always want a first number and I want a comma separator.

If I copy this down, it should pick up that format.

And for the final one here, we’d like fractions. Use # ?/? and copy that down. And there’s our hours with fractions.

Get the Workbook

Thanks for watching this video. You can go to my website, contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.

End Of Transcript

_____________________