Why ‘Your Version’ of Excel only Accept Semicolons in Formulas instead of Commas

Today, I learned how Region settings in Windows can truly botch up Excel.

A reader of one of my Pulitzer Prize worthy books sent me an email stating that the example formulas aren’t working.

After a bit of back and forth, he sent me this:

 

Huh? Since when does Excel use semicolons instead of commas as argument separators?

Well, in researching this anomaly, I was surprised to find lots of examples where people state that “their version of Excel” uses semicolons instead of commas.

That makes no sense!

 

Well, it turns out that a Region setting causes Excel to error when entering commas in formulas.

If you’re one of those folks who have been happily entering formulas with semicolons, stop! You can fix this.

Here’s how:

 

1. In Windows, go to the Control Panel (Start->Control Panel).

2. Select Clock, Language and Region.

 

3. Select the option for number format.

 

4. In the Region and Language dialog box, the Additional Settings button.


 

5. Change the List Separator property to a comma, then click the Apply button,

 

Now you can use Excel like everyone else in the world.

Don’t Miss Out – One More Week to Sign Up for Live Power BI Training with Ken Puls

I’m a HUGE proponent of live training.

Webinars and on-line training is fine, but there is nothing like physically being in an environment of like-minded people.

Ken Puls is hosting a Power BI Bootcamp LIVE in Vancouver, British Columbia.

Now, before you click away from this crass commercial message, let me preempt all your questions by answering them here.

 

1. Who’s Ken Puls?

He’s an Excel MVP, author of the wildly popular book on Power Query (M is for Data Monkey), and the go-to person we Excel MVPs hit up when we have an complex Power BI issue. He’s the real deal.

Oh and he’s a Canadian, redhead (weird right?).

He’s kind of a cross between comedian Pete Holmes and Mythbuster Adam Savage.

 

2. Why should you care about Power BI?
Your success and (dare I say) potential to move up in your career, hinges on your ability to tackle ever-more demanding data processes.

Over the last few years, the concept of self-service business intelligence (BI) has taken over the corporate world. As Excel Analysts, we are expected to create our own reports, run our own queries, and conduct our own analyses – all without the need to engage the IT department. These new demands stem from several factors.

  • Too Much Data to Contain: Organizations are realizing that no single enterprise reporting system or BI tool can accommodate all of their users. Pre-defined reports and high-level dashboards may be sufficient for some casual users, but a large portion of today’s users are savvy enough to be considered power users. Power users have a greater understanding data analysis and prefer to perform their own analysis; often within Excel.
  • Changing Analytical Needs: In the past, business intelligence primarily consisted of IT-managed dashboards showing historic data on an agreed upon set of key performance metric. Managers today are demanding more dynamic predictive analysis, the ability to iteratively perform data discovery, and the freedom to take the hard left and right turns on data presentation. These managers often turn to Excel to provide the needed analytics and visualization tools.
  • The Need for Speed: Users are increasingly dissatisfied with the inability of IT to quickly deliver new reporting and metrics. Most traditional BI implementations fail specifically because the need for changes and answers to new questions overwhelmingly outpace the IT department’s ability to deliver them. As a result, users often find ways to work around the perceived IT bottle-neck and ultimately build their own shadow BI solutions in Excel.

The Power BI suite of tools ushers in a new age for us Excel analysts. Excel is truly an integral part of the Microsoft BI stack – able to integrate multiple data sources, define relationships between data sources, process analysis services cubes, and develop interactive dashboards that can be shared on the web.

 

3. What kind of stuff will you learn?
You’ll get a comprehensive review of Power BI features, and the analytical concepts that will help you create better reporting components.

After this 3-day course, you’ll able to:

  • Analyze large amounts of data and report those results in a meaningful way
  • Get better visibility into data from different perspectives
  • Add interactive controls to show various views
  • Automate repetitive tasks and processes
  • Create eye-catching visualizations
  • Create impressive dashboards and What-If analyses
  • Access external data sources to expand your message

 

4. What kind of seedy hotel is this event being held?
You’ll be very comfortable at the Westin Grand Vancouver.

 

5. Canada…weather….dreary right?

This is a picture of Vancouver in February. Not Hawaii, but definitely not the tundra you’re imagining.

 

6. How can I convince my manager to pay for this?

Send that cheap bastard this article from the Harvard (yes I said HARVARD) Business Review:

If You Want Innovation, You Have to Invest in People

I quote:

“Before counting on any innovation offering from a vendor to change their fortunes, managers should therefore invest in two kinds of education. First, they need to ensure that the professionals they employ are current in their fields. Every discipline is experiencing accelerated development, and the rapid knowledge obsolescence that goes with it. Cushing Anderson of IDC puts it well: “Knowledge leak is the degradation of skills over time, and it … can kill organizational performance in as little as a couple of years.” While it might have seemed reasonable in an era of slower change to put the onus on the individual to maintain his or her currency, firms today must make it their business to counter this leakage.”

Hee-hee….they said leakage…

 

Listen…I can go on and on, but I can’t explain the importance of a live event like this better than Ken.

Take a moment to check out his post, and please consider attending.

Power BI Bootcamp LIVE in Vancouver, British Columbia

Winners of the Excel 5-Minute Challenge

The Excel 5-Minute Challenge has come to an end and the Excel community has chosen the winners!

Participants recorded themselves creating a functional dashboard/report in 5 minutes or less. Their videos were posted to YouTube, where winners were selected based on the number of “Likes” each video received.

And the Winners are…

 

1st prize:

Balázs Voros came in first place with 186 Likes.

He wins the Xbox One!

 

2nd prize:

Janis Sturis
takes second place with 118 Likes.

He wins the Fitbit Fitness Wristband!

 

3rd prize:

Alex Powers gets the Bronze with 89 Likes.

He wins the Amazon Fire HD 8!

 

3 Honorable Mentions

These fine young men took Honorable Mention with their submissions.

Steve Rider (41 Likes)

MF Wong (35 Likes)

Dinesh Natarajan Mohan (32 Likes)

 

They get to choose from one of these prizes!

 

DataPig Add-ins Gift Pack


 

Mr. Excel Gift Pack

From Bill Jelen


 

Magic of Pivot Table Course

From Ken Puls


 

RefTreeAnalyser Formula Auditing Tool

From Jan Karel Pieterse


 

Excel Dashboard Pro Training Course

From Jordan Goldmeier


 

Contextures Pivot Power Premium Add-in

From Debra Dalgleish


 

Peltier Tech Charts for Excel 3.0 – Advanced Edition

From Jon Peltier


 

Congratulations to all the winners.

And a special Thank You goes out to OZ Du Soleil, Doug Clancy, Senthil Thasma, Frédéric Le Guen, and Aamir Bhatti for submitting thier own videos. You guys are all awesome.

I’ll hold another contest like this one in the next few months. I’ve got a few ideas to lower the barriers to entry to make things more interesting.

Stay tuned.

Vote for the Winner! Excel 5-Minute Excel Challenge

Ok…all the submissions for the 5-Minute Excel Challenge have come in.

I’ve loaded them to YouTube and have opened up voting.

 

I have to say, these videos are different than I expected.

After watching the submissions, I realize now the real charm of these is not necessarily the final product.

It’s the small tricks that you catch as the presenters try to crunch through as efficiently as possible. It’s fascinating to see how different folks tackle similar tasks in various ways.

It’s not Breaking Bad, but for an Excel nerd like me, it’s fairly interesting watching.

 

Now comes time to pick the winners.

I need you for that.

Go to the YouTube Channel and watch the videos and press like on the ones you liked; that’s it!

For those who have submitted a video, it’s time to recruit co-workers and friends to vote for your video.

Because I uploaded these a day late, I’m extending the deadline for voting.

The winners will be the videos with the most likes by Sunday, February 12th, 12:00 am US Central time.

Winners will be announced on Monday, February 13th.

 

Many of the presenters expressed how difficult it is to record 5 minutes straight through. I quote the great Doug Clancy (yoursumbuddy.com/blog)

“It’s hard to do 5 minutes straight without major crew-ups – gives me much more respect for Orson Welles and Aaron Sorkin.”

I’m going to give it a shot and post mine up.

In fact, I think it would be fun to periodically post 5-Minute Excel Challenge videos. I’ll keep the channel up and going to see what becomes of it.

In the meantime, please take the time to vote, and take your hats off to the fine nerds who took on this challenge:

  • Alex Powers
  • MF Wong
  • Aamir Bhatti
  • Doug Clancy
  • Dinesh Natarajan Mohan
  • Oz du Soleil
  • Balázs Voros
  • Steve Rider
  • Frédéric Le Guen
  • Senthil Thasma
  • Janis Sturis

Excel 5-Minute Challenge Update

The Excel 5-Minute Challenge is underway, and I’ve received one video so far.

Don’t know about the Excel 5-Minute Challenge? Click here to find out.

It would be a shame for all these prizes to go unclaimed.

 

Pep-talk Time:

Take chance and pull together a quick video. You’ll be happy.

See this lady? She’s soooo happy. Why?

Because she just submitted her 5-Minute Challenge video.

She says “My life has changed since submitting my video. I’m no longer considering suicide, and I’m more fulfilled than ever. I am Excel and you can too!”

 

Need more motivation?

Well, here’s a new offering from Jordan Goldmeier (Excel.tv).

Jordan and his team have stood up their first annual Excel Dashboard Pro Course.

This course includes 60+ lessons covering everything from Fundamental Dashboard Techniques to Building Professional Interactive Excel Dashboards.

Right now, Jordan is giving away (FREE!) the first three videos in this series.

  • 3 Awesome Benefits of Building Dashboards With Excel
  • Rookie Dashboard Mistakes That Even the Experts Miss
  • Excel Dashboard Questions Answered

To get the free videos and to check out more details about the full Excel Dashboard Pro Course click the image below.

 

And remember, you’ve got one more week to submit your Excel 5-Minute Challenge entry.

Quickly Wrap Formulas in IFERROR without VBA

Here’s a quick tip for quickly wrapping all formulas in IFERROR without VBA.

 

1. Select the cells that contain the formulas you want wrapped in IFERROR.

 

2. Press F5 on your keyboard to activate the GoTo dialog box, then click Special.

 

3. Select Formulas and press the OK button

 

4. In the Formula bar, enter the IFERROR function you’d like to use, then press CTRL+ENTER. Pressing Ctrl+Enter is important here. Make sure you don’t press just the Enter key.


 

5. Marvel at your efficiency.

 

Of course, this handy trick only works if all your formulas are performing similar calculations (referencing the same relative cells). If want to wrap formulas that are performing different operations, you’ll need to use some VBA. Here’s a quick and dirty macro that wraps selected formulas in IFERROR.

  1. Sub Add_IFERROR()
  2. Dim R As Range
  3.  
  4. For Each R In Selection.SpecialCells(xlCellTypeFormulas)
  5.  
  6.      If Left(R.Formula, 8) <> "=IFERROR" Then
  7.           R.Formula = "=IFERROR(" & Mid(R.Formula, 2) & ","""")"
  8.      End If
  9. Next R
  10.  
  11. End Sub

New Contest for 2017: The Excel 5 Minute Challenge

Happy New Year everyone! I’m back from a few long months of moving into a new house. Now that I’m all settled in, I’d like to start this New Year with a bang.

By that, I mean a new Excel Contest!

The idea for this contest came from this YouTube video posted by John Michaloudis (MyExcelOnline). In his video, he builds a dashboard in 3 minutes, giving us an interesting way to see fundamental techniques in action. Neato!

The goal of this contest is to see the various techniques the Excel community uses to quickly pull together reporting. I’m hoping to showcase the versatility, flexibility, and ease of creating reports and dashboards with Excel.

 

The 5-Minute Excel Challenge


The task is simple. Start with some prepared data and create a fully functional dashboard/report in 5 minutes or less.

To participate in this contest, you can submit a video of you building out a report or dashboard. All videos will be loaded to an official 5-Minute Challenge YouTube Playlist.

 

Prizes

All submitted videos will be judged by the Excel community.

The number of “Likes” on each video posted will determine the winning entries.

So what can you win?

 

1st prize:

Xbox One

 

2nd prize:

Fitbit Fitness Wristband

 

 

3rd prize:

Amazon Fire HD 8

 

 

3 Honorable Mentions:

A bunch of leading Excel MVPs have graciously offered several top prizes for all three Honorable Mention winners.

Your choice of one of two of these gift-packs:

 

DataPig Add-ins Gift Pack


 

Mr. Excel Gift Pack

From Bill Jelen


 

Magic of Pivot Table Course

From Ken Puls


 

RefTreeAnalyser Formula Auditing Tool

From Jan Karel Pieterse


 

Excel Dashboard Pro Training Course

From Jordan Goldmeier


 

Contextures Pivot Power Premium Add-in

From Debra Dalgleish


 

Peltier Tech Charts for Excel 3.0 – Advanced Edition

From Jon Peltier


 

 

Contest Rules

  • Your video must be no more than 5 minutes long (it can be less than 5 minutes).
  • Your video must be real-time. No edits! No fast-forwarding. No tricks.
  • Your video must end with a reasonably useful working Excel dashboard or report.
  • You can start with staged/prepared data on your worksheet or in a PowerPivot data model. However, your presentation layer/canvas must be empty.
  • All work must be done real-time. No copy and pasting pre-cooked VBA, Charts, PivotTables etc. However, you can copy and paste objects created during the video.
  • You can use all tools available to you: Formulas, Macros, VBA, Power Pivot, Power Query, Power BI, and even Add-ins. I want to showcase the versatility of Excel, and how Excel can be used at all levels of skill. If you’re an expert at Power BI, show that off. If you’re a charting wizard, show that off! If you’re the king of Pivot Tables, show that off!
  • You don’t have to narrate your video, but it will definitely help your chances of winning likes from the Excel community.
  • You can submit as many videos as you’d like (no limits on the number of submissions).

 

How do you make a Video?

There are any number of free screen capture tools out there.

Just enter “Free Screen Capture Sofware” into your favorite search engine.

Here is a link to SourceForge that’ll get you started.

 

How do you Submit your Videos?

Just upload your video to OneDrive, Google Drive, Dropbox, or any content sharing service.

Then send me an email with the subject line “Excel 5 Minute Challenge”. Include a link to the actual video file (no links back to your website). If you want to advertise your site, then give me that info and I’ll put it in the video description when I post your submission.

Email to Mha105@yahoo.com

I will then upload your entry to the official 5-Minute Challenge YouTube Playlist.

I will send you a reply when I’ve posted your entry.

If you’re video does not meet the contest rules above, or if I can’t get to your video, I’ll let you know.

 

Contest Deadline

All entries must be submitted to me by Sunday, February 5th
(11:59pm US Central time).

I will announce winners on Friday, February 10th (6:00pm US Central time).

 

Winner Selection

Winners will be selected based on the number of “Likes” each video receives.

In the event of a tie, I’ll make the final determination based on the video content.

 

Disclaimers

  • Prizes are subject to availability and shipping costs to your area. I’ll use Amazon to distribute prizes, so if I can reasonably get the prize to you from there, great. In other words, if you, for example, live in Papua New Guinea and the shipping cost is more than the value of the prize, then I’ll replace the prize with cash value.
  • You can only win one prize (regardless of how many submissions you enter).
  • By submitting a video, you agree that none of the data you present is confidential or under NDA.

I’m very much looking forward to seeing all the cool things our Excel community can create!

Creating Map Visualizations within Standard PivotTables

I was puttering around on the internet when I came across Daniel Ferry’s old post on Location mapping in Excel. The idea is that you can use latitudes and longitudes to plot points on a chart. Cool trick.

Then I wondered if you could achieve a similar result with a PivotTable. Well…it turns out you can.

This is a screenshot of a map visualization I pulled together using a normal a PivotTable.
With slicers, this PivotTable becomes a dynamic location intelligence component for your dashboards.

Nifty!

The steps to create this kind of map are pretty straight forward:

1. Get some data with Latitudes and Longitudes.

2. Create two new columns to round the latitudes and longitudes. This essentially reduces the number of unique values so that your PivotTable doesn’t choke.

 

3. Create a new PivotTable, placing your Rounded latitude field in the Rows area and the Rounded longitude field in the Columns area.

 

4. Once your PivotTable is created, sort your Rows so that they run from Largest to Smallest.

 

5. Go into the PivotTable Options and remove all GrandTotals. Also be sure to clear the “Autofit columns… check box.

 

6. Click inside the PivotTable and select Conditional Formatting. In the Rules Manager dialog box, select the third option under the Apply Rules To section. For Rule Type, choose to Format all cells based on their values. Finally, choose the 3-Color Scale format Style. Adjust the formatting as needed. Your Rule manager should look something like this.

 

7. At this point, all there is left to do is clean up.

  • Change the number formatting of the values so that no values show (select Custom and enter ;;;
    in the Type field).
  • Hide the Row and Column fields of the PivotTable
  • Adjust the sheet row and column widths to get the aspect ratio right.

 

8. Add a Slicer and make all your nerd friends jealous.

 

Now obviously, this techniques doesn’t produce a strictly accurate map. I mean, come on, the first step is to round the latitudes and longitudes. That being said, it does generate a directionally correct map profile that looks pretty cool.

You can play with a sample file by downloading it.

You kids have fun.

Easy File Search with Tags and the Document Location Widget

I’m back from a long hiatus from blogging. While I was gone, I had a project that involved helping a client tag hundreds of Excel files for easier searching.

The Basics of Using Document Tags

For those of you that don’t know, you can go into the properties of an Excel file…

File->Info

…then enter keywords that can be used by the built in Windows Explorer search feature.

Specifically, enter your keywords in the Tags input box under Properties (to the right of the Info screen)

In this example, I’ve tagged an Excel workbook called MyImportantBook.xlsx with Bacon and Pork.

 

Once your file is tagged, you can open Windows Explorer and enter a search term. Any files tagged with your search term will pop up. You can then double-click the file to open it.

 

So if you’re constantly searching for files, consider tagging them in the Document Properties to help in searching.

 

Using VBA to tag Documents

If you’re feeling especially geeky, you can set up an Excel Table called FileTags that holds your tags.

Be sure your name the Table FileTags.

Then you can run this code to automatically add the tags you define in the table.

  1. Sub CreateDocumentTags()
  2.   Dim lstKeywords As ListObject
  3.   Dim strTags As String
  4.   Dim docTags As DocumentProperty
  5.  
  6.   'capture values from the FileTags table
  7.  Set lstKeywords = ThisWorkbook.Sheets(Range("FileTags").Parent.Name).ListObjects("FileTags")
  8.   For i = 1 To lstKeywords.ListRows.Count
  9.   strTags = strTags & lstKeywords.DataBodyRange(i, 1).Value & ", "
  10.   Next i
  11.  
  12.   'set document tag with the captured values
  13.  Set docTags = ThisWorkbook.BuiltinDocumentProperties("Keywords")
  14.   docTags.Value = Left(strTags, Len(strTags) - 2)
  15.  
  16. End Sub

 

Playing with the Mysterious Document Location widget.

If you’re bored, and want to delve into quirky territory, you can play around with the Document Location widget.

You’ll first have to add it to the Quick Access Toolbar.

File->Options->Quick Access Toolbar.

You can find the Document Location widget under Commands Not in the Ribbon.

 

I have to be honest. This thing is practically worthless.

As far as I can tell, it’s meant to show you the full path of the currently open document.

Unfortunately, you can’t resize the widget so it’s not as useful as you would think.

 

The redeeming attribute of this widget is that it basically works like the Windows Explorer address bar.

So you can enter basically any path or URL to automatically open a new window with the content you request.

For instance, you can open dailydoseofexcel.com directly from Excel by simply entering the URL.

 

Like I said…practically worthless (I mean the widget…not dailydoseofexcel)

BUT….

Interestingly enough, you can use the Document Location widget to enter a file search on the fly.

Simply enter:

search-ms:query=bacon&location:HOMEPATH

Your search term goes after the query argument.

Then you can specify HOMEPATH to tell windows only look in the folders under the userid you’re logged into.

Excel will raise a warning.

Like all other warnings Excel gives me, I promptly ignore it and click YES.

 

Windows Explorer will pop into action, showing you all the files that match your search term (files that contain your search term or are tagged with your search term).

 

For the location argument, you can use any of the Windows Common File Names. For instance, this search will be limited to the Documents folder:

search-ms:query=pork&location:Documents

This search will be limited to the Application Data folder:

search-ms:query=template&location:APPDATA

 

This is fun to play with, but I’ve yet to use this trick in any meaningful way.

Another thing I should mention is that I have not been able to get this trick to work with more than one search term at a time. There is probably more robust syntax that needs to be used, but it’s probably just easier to enter your search directly into Windows Explorer.

 

Wow…what a useless trick for my first blog post after 3 months.

Getting Rid of Ugly Page Break Lines

Have you ever sent an Excel report out to your audience just to find out that it prints on 20 pages? To avoid that embarrassment, I routinely adjust the page breaks on my reports so that users don’t have to kill 10 trees each time they print.

Unfortunately, each time I adjust page breaks, Excel tries to do me a favor and adds an annoying line to my report (supposedly to remind me of where my page breaks are).

Here’s an example. Let’s adjust the page breaks on this sheet.

 

All I’m doing is clicking Page Break Preview, and then Read more