Here is a question that has been particularly popular recently. How do you create a map charting figures by county, city or zip code in Excel? There are a variety of ways to tackle this question, but today I want to show you how to use data types in conjunction with the map chart type to create a chart by county in Excel.

This Byte is inspired by two different faculty members who emailed me with this question… you know who you are, and thank you for the inspiration!

Starting Place: The List

Your starting place should include clearly named locations and associated figures. In my case, I picked a few Kansas counties (sorry if I left out yours!), and their populations.  The numbers could have been any figure you are tracking, of course.

Step One: Convert to Geography Data Type

The first step is to make sure Excel can identify the locations listed in your document. This can be an optional step, but skipping it may mean that Excel can’t identify one or more of your locations and the map therefore won’t cooperate, so best practice is to start here.

1. Select the data to be identified (counties, in this case).

2. Go to the Data tab, Data Types group and select Geography.

3. Excel will attempt to identify the locations. If all goes well, a little map icon will appear to the left of the county name.

By the way, notice the little box at the upper right of your selection. Click on this to extract other pertinent information about your location. This is not part of your chart, but a cool trick in Excel worth mentioning.

Here are a few extracted fields, so you can see how they look: image, largest city, area. Notice how Excel creates a new column for each one.

Here is a bit of inspiration: imagine that you have a list of zip codes and figures and you need to create a map by county. Simply convert the zip codes to geography data types, extract county, and off you go. Pretty handy, right?

Step 2: Create a Chart & Customize

Now that Excel has identified our data, we are ready to create a chart.

1. Select the data to be charted. In this case, county and population columns.
2. Go to the Insert tab, Charts Group
3. Select the Map dropdown, Filled Map option.

4. Excel will create a map with your data. If you don’t care for the default colors and appearance, don’t forget you can customize all your charts with the contextual Chart Design tab in the ribbon.

Shameless Training Plugs:  If you would like to learn more about the Chart Design contextual tab, please attend one of my Excel Pivot Tables, Charts and Pictures sessions (now offered remotely). Also, don’t forget you can easily change your theme colors on the Page Layout Tab in the ribbon. We cover this one in Excel Essentials if you are interested in learning more.

It may take a few tries to get a map you are happy with. Don’t be discouraged! The results are fantastic.

One More Word on Geography Data Types

In the example above we mapped by county, but you may find yourself needing to map by zip code, state, country… the Geography data types can recognize all of these and more. Experiment with this powerful tool and you may be surprised what you find.

Thoughts?

What do you think, do you have any geographic data that is calling out for a better visualization? I can’t wait to hear how you use this feature!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

• Debbie Neill
• Kelsey Unruh

There are two things that are the kryptonite in an otherwise perfect Excel document: merged cells and blank rows. We have already tackled merged cells, so today I want to focus on blank rows. People commonly insert blank rows for stylistic reasons, but here’s the thing: don’t. It may feel like you are making your document more readable, but really you are making it less functional. Let’s take a look.

The Trouble with Blank Rows

If you are plagued by blank rows in your documents, you may be experiencing a variety of issues. Look at the example below… this is the concert promoter example we use in our PivotTables session.

In this scenario, however, the concert promoter has decided that it would help him/her visually to separate out each event with a blank row.

So, what’s the problem? For one, when Excel arrives at a blank row, it thinks you have finished your thought. This manifests in many inconveniences. For instance:

AutoFill Issues

What if we decide to insert a formula in a subsequent column? We learn about utilizing AutoFill with formulas in Advanced Formulas, so we hover our mouse in the lower right until we see the AutoFill Handle and double click…

… Only to find that Excel stops at the blank row. In a document with 5,000 rows and numerous blank rows, you probably don’t want to have to manually autofill every time you insert a new formula…

PivotTables and Tables

How about creation of Tables and PivotTables? We know to put our cursor in the middle of the data and let Excel select all the information that will create our PivotTable or Table, but look what happens when we have a blank row… the Table selection will stop just shy of that first blank row. And even if you override this, you are stuck with numerous “blank” results. Not fun.

Other Examples

Sort and Filter, chart creation, and many more features will just not work properly with blank rows. When you really want to work with your data, you will discover so many more examples. Usually people call me at this point, thinking they are “bad at Excel,” when really they are doing everything right to create a PivotTable, or sort data, it is just those blank rows getting in their way.

So now that you want to remove all the blank rows in your document, what should you do?

Removing All Blank Rows

Of course, you could highlight each row by hand and delete them one by one, but an example that came up last week was a person with a 2000 row document that contained numerous blank rows sprinkled throughout. If this is the case for you, here is how you can remove all blank rows in your document in one fell swoop:

1. Select all the data in your document. There are several ways to do this, but one quick was is to click on the Select All button: the triangle above your first row (pictured below).

2. On your Home tab, Editing group, select the Find & Select dropdown. Select Go To Special

3. In the Go To Special screen, select the radio button next to Blanks. Press OK.

4. This will highlight all your blank rows. Quick note: if you see too much content selected, hold down your Ctrl key and click to deselect. For instance, sometimes this feature will select the blank column to the right of your data, and you can deselect the entire column of data this way (Ctrl + select column).

5. With these blank rows selected, go to the Home tab, Cells group. Select the Delete dropdown, and select Delete Sheet Rows.

All the selected blank rows will be deleted. So much better! You can now enjoy a happy and carefree life, now that you are free from the restrictions of blank rows!

Thoughts?

Will this save you some trouble in any of your documents? I would love to hear how you use this!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Faye O’Reilly
(Picture coming soon!)

There are many reasons you might be running through a list of possible scenarios. If you would like to transform your data into a visualization of a variety of possibilities, you might be interested in the What If Analysis feature in Excel.

Video

Here is an accompanying video for this blog post. I hope this is helpful!

A Fictional Applicable Situation

Think of a business considering a start date, weighing the cost of a delayed open. They have two possible amounts for how much they stand to lose per day if they don’t open: $250 loss per day and $500 loss per day. They also have three possible proposed open/start dates: 6/1/20, 7/1/20, and 8/1/20. The owner would like to know a variety of outcomes, given different possible start dates and different possible income loss per day to delay opening.

Of course, this is a vastly over simplified version of a business accounting model, but will hopefully serve to show you how this feature works.

Document Formulas

The two cells that will prompt changes in our What If Scenario are the Start Date, of which there are three possibilities (June, July, August), and Loss/Day, of which there are two possibilities ($250 and $500).

The total Expenses and Balance in reserve will change as the date and loss  figures change.

Creating Scenarios

To create What If scenarios:

1. Go to the Data tab, and select the dropdown for What-If Analysis. Select Scenario Manager.

2. If you have not created any scenarios, this area will be blank. Press Add.

3. Name your scenario something that will help you identify it later. I identified mine with the elements that would be changing in each scenario: the start date and the loss per day.

4. Click into the blank space next to Changing Cells and select a cell that you would like to create a scenario for. In my case, I selected two cells (for date and loss per day) by holding down the Ctrl key and selecting each of them. Press OK.

5. For my first scenario, I wanted to see what would happen if B1 were the date 6/1/2020, and B3 were $500. I entered those figures accordingly, then pressed Add.

6. Create as many scenarios as you would like. I made six possible scenarios and for the different possible loss amounts and start dates.

7. Select a scenario and press show to run the What If Analysis.

There is also an option to generate a summary on a new sheet with all the possible scenarios. Press Summary to generate this.

Accessing Scenarios

You can access scenarios this way:

1. Go to the Data tab, What-If Analysis dropdown, and select Scenario Manager.

Each of these listed scenarios represents a potential start date and a different loss per day amount.

2. To examine a scenario, either double click on one of them, or select one and press Show.

3. The various figures change as you select different possible scenarios: a July start date, with $250 loss per day means the total expenses were $17,000 and remaining balance $33,000.

An August start date with $500 loss per day leaves a $500 remaining balance.

Extras: Macros and Charts

You can customize your What If scenario with charts and macros. Look at this setup below, incorporating a Donut Chart (like this one) to accompany the data, and some text boxes linked to macros to run the What If Scenarios.

Pretty handy for a presentation or for distributing to others without having to send instructions for running the What If scenarios.

Downloads

I made a couple versions of this document for you to examine that incorporate the What If Analysis:

Original Version

Macro Enabled Version with Chart and Extras

Warning: you may choose to set up formulas a variety of ways, but the formulas in these documents were set up for forecasting, so are not intended to be access to far in the future. Long story short, you may get an error if you are opening these documents too many months from now; this is due to the formulas, not the What If feature.

Thoughts?

What do you think? Has this sparked any ideas? I hope this feature helps you out as you are making plans for different future possibilities.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser