Excel Roundup 20160307

Excel Roundup http://blog.contextures.com/

In this week's roundup, Power Map, Excel formula errors, cell references, and more.

Continue reading Excel Roundup 20160307

Excel VLOOKUP Sorting Problem

VLOOKUP Sorting Problem http://blog.contextures.com/

You can use an Excel formula to pull data from a lookup table – for example, enter a product name, and automatically see its price. Be careful though, or things can go horribly wrong, and you'll end up selling things at the wrong price.

In this example, I used the VLOOKUP function to show what can go wrong. The same thing can happen with other functions too, such as an INDEX/MATCH formula. In fact, its more likely to happen there!

Continue reading Excel VLOOKUP Sorting Problem

Excel Roundup 20160229

Excel Roundup http://blog.contextures.com/

Happy Leap Year Day! According to my calculations, the next time that Leap Year Day falls on a Monday will be in 2044. By then, Excel will be 58 years old. Do you think that Excel will still be around, or will it have retired early?

This week, cool tricks with the new TEXTJOIN function, tell a story with your charts, create a calendar for Power Query, and more.

Continue reading Excel Roundup 20160229

Count Unique in Excel Pivot Table

Distinct Count in Pivot Table http://blog.contextures.com/

In a previous article,  Roger Govier showed us a couple of different ways to get a Unique count with a Pivot Table.

Create a Pivot of a Pivot Tables results (fast) Add a calculated column to the source data (much slower)

Now Roger has added another technique that you can use, in Excel 2013 and later. I'll hand things over to Roger, so he can describe the steps.

Continue reading Count Unique in Excel Pivot Table

Excel Roundup 20160222

Weekly Excel Roundup http://blog.contextures.com/

In this week's roundup, Slicer trick, pivot charts, slow workbooks, dashboards, and more.

Continue reading Excel Roundup 20160222

How to Split Date and Time in Excel

How to Split Date and Time in Excel http://blog.contextures.com/

If you download data into Excel, one column might have a combined date and time value. If you're going to create reports from that data, it's usually easier if you have the date and times in separate columns. See how you can do that with a simple formula, or a quick bit of typing.

Continue reading How to Split Date and Time in Excel

Excel Roundup 20160215

Excel Roundup http://blog.contextures.com/

In this week's roundup, Excel skills, make hyperlinks, dashboards, and more.

Continue reading Excel Roundup 20160215

Excel Valentine Cards 2016

Excel Valentine Cards http://blog.contextures.com/

If you dread going into a crowded greeting card store this weekend, to shop for a Valentine's Day Card – you're in luck! You can make a card in Excel instead, and I'll show you two options.

Time spent in Excel is much more pleasant than time spent shopping, right?

Continue reading Excel Valentine Cards 2016

Excel Roundup 20160208

Excel Roundup http://blog.contextures.com/

In this week's roundup, find Excel help, new functions, Excel conferences, and much more.

Continue reading Excel Roundup 20160208

Change Excel VBA Code to Improve Speed

Popup List on Click or Double-Click http://blog.contextures.com/

In some of my files, macros run automatically when you select a cell on the worksheet. For example, if you've bought a copy of my Data Validation Multi-Select Premium (DVMSP) kit, it shows a pop-up list when you click on a cell with a drop down list.

I'll show you how to quickly change that, so the pop-up appears when you double-click, and you could make a similar change in other worksheet code.

Continue reading Change Excel VBA Code to Improve Speed