Excel: Create Stunning Map Charts with Geography Data Types

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.

County populations displayed on a map

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.

List of counties and populations

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.

Insert tab, data types group, 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.

Locations identified, signified by a map icon on the left of county names

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.

Additional information about counties can be extracted at upper right

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.

Information extracted, as described above

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.

Insert tab, charts group, Maps 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.

Contextual chart design tab

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.

Kansas county by population map chart

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

10 MORE Secret Commands in Microsoft Office

Almost immediately after I posted my Top 10 Favorite Secret Commands in Microsoft Office, so many more came to mind… So without further ado, here are 10 more commands that go above and beyond our usual well-known commands.

Ctrl + ; to insert date

Ctrl + K = Hyperlink

This command works in all your Microsoft Office programs, and other programs as well (like OU Campus!). Select your text, and rather than going through a right click menu, press Ctrl + K to access your hyperlink options.

Ctrl + K to insert hyperlink

Think about how quick this action can be if you have already copied your hyperlink, you can simply select text, Ctrl + K, Ctrl + V (paste), enter, and you are done.

Ctrl + ; = Insert Date (Excel)

This is different than the =TODAY function we talk about in Excel Advanced Formulas class. This command, Ctrl + ;  does not insert a formula; rather, it inserts today’s date as stagnant text.

Ctrl + ; to insert date

By the way, a similar command is Ctrl + Shift + ; to insert the current time.

Ctrl + 1, 2, 3… = Outlook Navigation

We have talked before about how Outlook has some incredible time-saving commands, and here is one of my favorites. Ctrl + (a number) will take you through the basic Outlook navigation.

Ctrl + a number for Outlook navigation

Here are the basic navigation commands in Outlook:

  • Ctrl + 1 = Mail
  • Ctrl + 2 = Calendar
  • Ctrl + 3 = Contacts
  • Ctrl + 4 = Tasks
  • Ctrl + 5 = Notes
  • Ctrl + 6 = Folders
  • Ctrl + 7 = Shortcuts
  • Ctrl + 8 = Journal

So the next time you are in your mail module, and want to take a look at your calendar, try Ctrl + 2 to quickly access it!

Ctrl + Shift + < or > = Adjust Text Size (Word, PPT)

Do you want to increase a block of text by exactly one font size? In Word or PowerPoint, select your text, then try the commands Ctrl + Shift + > to increase all selected  text by one font size or Ctrl + Shift + < to decrease.

Ctrl + Shift + > to increase size

Ctrl + [Drag] = Duplicate

We have talked about Ctrl + [drag] in Acrobat fillable forms, but did you know if works in Microsoft Office as well?

If you would like to duplicate a block of text in Word: select the text, hold down your Ctrl key, and with your mouse click and drag it to a new location. You will have an exact copy of the selected text.

Ctrl + drag to duplicate

Same story in Excel. Want to copy a block of cells? Select them, hold down the Ctrl key, and with your mouse click and drag them to their new location

Ctrl + Drag to duplicate

One note in Excel, you will want to hover your mouse on the line of selected text, until you see the four headed arrow cursor. Otherwise, Ctrl will simply deselect one of the cells in your group, rather than move them.

Four headed arrow

Ctrl + Shift + C = Copy Formatting

Almost like the Format Painter in command format… you know that Ctrl + C is copy, but did you know that Ctrl + Shift + C copies formatting?

This will work in most of your Microsoft Office programs. Select the text you would like to copy, and press Ctrl + Shift + C.

Ctrl + Shift + C to copy formatting

Select the text where you would like to copy the formatting, and press Ctrl + Shift + V. The Format Painter as a command, how cool!

Ctrl + Shift + V to Paste formatting

Ctrl + Alt + V = Paste Special

Ctrl + V is paste, and we just learned that Ctrl + Shift + V is use when copying formatting… here is another paste option: Ctrl + Alt + V will Paste Special. This will work in most of your Microsoft Office programs.

Frequently used in Excel, but also when transferring Excel data to Word… Select your data and copy (Ctrl + C), select the new location and press Ctrl + Alt + V to access Paste Special options.

Ctrl + Alt + V is paste special

Ctrl + Shift + $ = Currency Format (Excel)

There is nothing wrong with selecting a number format from the ribbon, but if you know you want currency, it could speed things up considerably to select your cells and press Ctrl + Shift + $.

Ctrl + Shift + $ to apply currency format

Ctrl + Shift + ! and beyond= Number Formats (Excel)

While you are looking at the numbers on your keyboard, try out a few more. Ctrl + Shift + ! will give you a standard Number format. Ctrl + Shift + % will give you percentages, and so on. Test out a few, and see if there are number formats you find yourself reaching for regularly.

Ctrl + Shift + ! to apply number format

Alt + F = File Tab

The Alt commands are a powerful tool in Microsoft Office. Alt will take you to the ribbon in your Office programs, and there are so many paths you can learn and memorize from there. Here is a great one to get you started:

Alt + F will take you to the File menu.

File is Alt F

From here, notice the highlighted letters letters on top of popular commands. S for Save, P for Print and so on…

File Menu options

Even if you don’t end up falling in love with all the Alt commands in the ribbon, Alt + F is an easy one to learn and utilize.

Thoughts?

What do you think, will you use these shortcuts to save time in Microsoft Office?

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

Stephanie Versch (Picture coming soon!)

Excel: Remove All Blank Rows in a Document

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.

Blank rows

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…

Auto fill handle

… 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…

Autofill stops at blank rows

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.

Tables stop before blank rows

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).

Select all

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

Find and Select, got to special

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

Blanks selected

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).

Blank rows highlighted

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

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!

Blank rows gone

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!)

Excel: Calculating Scenarios with What If Analysis

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.

What If analysis displaying scenarios

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).

STart date and loss per day

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.

Data tab, What If Analysis, Scenario Manager

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

Add Scenario

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.

Scenario name, changing cells

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.

Scenario values

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.

Scenario list

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

Scenario list, summary button

Accessing Scenarios

You can access scenarios this way:

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

Data tab, what if analysis, 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.

Scenario Manager list

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.

Start date and loss per day affects total expenses and remaining balance

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

Start date and loss per day affects total expenses and 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.

What If analysis displaying 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

Compatibility Mode: How to Re-Activate Your Favorite Features

This happens frequently after a PivotTables session… An attendee excitedly returns to their computer to create a PivotTable from an existing Excel file, only to discover that the PivotTable screen looks a lot different than it did in class, and a lot of their favorite features are grayed out in the ribbon.  This is not just an Excel issue… sometimes this happens Word or PowerPoint: certain features are mysteriously faded in the user’s ribbon. The culprit? Compatibility Mode. Let’s talk about this setting, and how to escape from it, in your Microsoft Office documents.

Grayed out buttons in ribbon

What is Compatibility Mode?

The intentions of Compatibility Mode are in the right place. Its purpose is to make a document as functional as possible with older versions of software. So if you are using Office 365, and your friend is running Office 2010, you could send this Compatibility Mode document to them and rest assured it will look the same to them as it does to you.

For this reason, you will often see data that is exported from other programs; Reporting Services and WSU Reporting, for instance; default to Compatibility Mode. The software programmers aren’t sure what version of Office you will be using, so they err on the safe side, and have the export default to Compatibility Mode.

Another possibility: you are opening an old document. If someone created this document in 2001, and never updated the file format since then (we will talk about this further down), chances are you are in Compatibility Mode by default.

Missing Features

Earlier I mentioned that PivotTables are often the first place people notice missing features in the ribbon…

Grayed out buttons in ribbon

… But this happens in other Excel tabs, PowerPoint, and Word as well.

Grayed out buttons

The features that gray out vary from version to version, but bottom line, newer features are what disappear in Compatibility Mode. When someone is experiencing these ghosts of buttons, this is almost always the culprit.

How to identify Compatibility Mode

How do you know for sure if your document is in Compatibility Mode? There are several ways.

1. You might have some visual cues; the icons look slightly different in Compatibility Mode, shown below, on the left side of the icons.

Icons

2. At the top of your document, next to the title, you might see the words Compatibility Mode

Compatibility Mode

3. Go to your Save As screen (File, Save As). What do you see in the dropdown under the title? If it says Excel 97-2003 Workbook, you are in Compatibility Mode.

Save As screen file type drop down

In fact, stay in the Save As screen, and I will show you how to fix this.

How to Escape From This Setting (and get your favorite features back)

In your Save As screen (shortcut: F12), Compatibility Mode reads as Excel 97-2003 as the File type (dropdown under the document title).

Save As screen file type drop down

To fix this issue, click on the dropdown under the title, and select the first option for Type: Excel Workbook in this case (Word Document for Word and PowerPoint Presentation for PowerPoint).

Save As screen file type drop down

Important: you must close the file and reopen it to see the new options appear.

That’s it! You have your features back! I hope this helps with some of your legacy documents or documents exported from various sources.

Thoughts?

What do you think? Have you been plagued by the trials and tribulations of Compatibility Mode?

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

Excel: Merging Data with Power Query (Bonus Video!)

Power Query in Excel is an incredibly powerful tool. A while back, I posted a brief introduction to this feature. I wanted to expound upon that information with another great use for Power Query: merging data. This is a common situation: a lot of us have data files we are pulling from different sources that contain unique information. Combining this data into one powerhouse sheet can be a great time saver. Let’s take a look at how to do this.

Power query screen

Video

Hey, I am trying something different! Thank you to those of you who completed our ITS Applications Training Survey last week. A ton of you said you wanted videos and exercises, so let’s try it out today. Below is a link to a YouTube video, and read on for the exercise files. I will also include written instructions, as per usual.

 

Exercise Files

Here are a couple downloads for you:

MergeExercise:  Start Here

MergeExerciseSolved: to check your work

Two Disclaimers

  • For this Byte, I am assuming you already have some basic knowledge about Excel, like Tables and PivotTables. If you would like to learn more about either of these, I would love for you to attend a session! Look for sessions in myTraining.
  • Also, you will likely need to be using the Windows version of Excel, preferably 365, though this should also be possible in 2016.

About This Workbook

This spreadsheet contains three sheets: each with unique information about our key players in the name column.

  • The first sheet contains their number and location,
  • The second contains their favorite animal, and
  • The third their favorite candy. Our goal is to bring all this data together in one powerhouse worksheet.

Date

Import Data as Queries

1. In the first sheet, place your cursor in the middle of the data. Press Ctrl + T to turn it into a table. You can also do this from the dropdown in the Home tab.

2. Make sure “My table has headers” is checked and all the data is selected, and press OK. Repeat this step with each sheet of data, so you have a total of 3 tables in the worksheet.

table setting

3. Select one of the tables. Go to the Data tab in the ribbon, Get & Transform data group, and select From Table/Range. If you don’t see this button in the Get & Transform Data group, check the Get Data dropdown for it.

Data tab

…you will be taken to the Power Query editor screen…

Power query screen4. In Applied Steps on the right, click the X next to changed type to remove it.

x next to changed type

5. In the upper left select the dropdown for Close & Load To…

close and load to

6. Select Only Create Connection and press OK.

only create connection

7. Repeat Steps 3 – 6 for all the tables you created. You will see a list of tables appear on the right side of the screen under Queries and Connections.

Queries and connections

Merge the First Two Queries

1.  In the Data tab, go to the Get Data dropdown, hover your mouse over Combine Queries, and select Merge.

Combine data, merge

2. In the Merge screen, select your first table in the upper dropdown, and your second table in the second dropdown.

select tables

3. Select the two common columns: in this case, it is the Name column. Click on the Name column under each table. Press OK.

Select name column

4. In the Power Query screen, find the double headed arrow at the top of the last column, from the merged table. Click on this button to expand information.

double headed arrow to expand

5. Select what you would like to expand. In this case, we are unchecking the name box, and just keeping Animal selected. Press OK.

select animal only

This will expand the Animal column from the second table. Note that it applies its own naming format to the column title. You can rename the column, if desired. Double click on the title to change the name.

CHange name of column

Keep this Power Query screen open for the next step.

Merging Subsequent Queries

We now have 2 merged queries. We can add the last one from within the Power Query screen.

  1. In the Power Query ribbon, Combine group, select Merge Queries.

Merge

2. In the Merge screen, your Merged query will appear at the top by default. In the second dropdown, select the final table. Just like you did in the previous section, select the Name columns in both queries, and press OK.Merge

3. Just as you did in the previous section, the final table appears at the top with a double headed arrow. Click on the double headed arrow to expand. And leave only Candy selected. Press OK.

only candy selected

4. Select Close and Load To… but this time keep the default selected: Table on a New Worksheet. Press OK.

Close & Load Screen

Final Table

Our merged query is visible as a table on a new worksheet.

Final Table

A Couple Notes

It helps if you think about your final table like a Pivot Table:

  • If you need to change something about the data, be sure to change the data source (one of the data sheets), not the merged query.
  • When you do change the data source, be sure to refresh the query. A simple way to do this is to right click on the merged table and select Refresh.

Right click refresh

Thoughts?

What do you think? Did you like the video? I hope this was helpful. Feel free to send me an email anytime.

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

  • La’Rell Marion (picture coming soon!)

Top 10 Secret Key Commands in Microsoft Office

Whenever I mention one of these shortcuts in a session, I see people taking out their pencils to write them down. Most of us know some commonly used keyboard commands in Microsoft Office; Ctrl + Z to undo, Ctrl + C to copy, and so on… but I have some lesser-known favorites to share with you today. Also, take a moment to check out this article, which contains some more general shortcuts.

Header by pressing alt + ctrl + 1

 

F12 = Save As

If you have taken any of my Essentials sessions, you know this one, one of my absolute favorites. Remember in the old days when you used to be able to select Save As from the menu, and you would be taken directly to a dialog box with everything you wanted in one place? If you are like me, you found that box to be way faster than clicking through the options one by one in the new File/Backstage menu.

So just press F12. It does everything you want, and works in all your favorite Microsoft Office programs.

Save As screen

F7 = Spell Check

Want a quick spell check? No need to visit the ribbon, just press F7.

Word Spell Check

Especially useful in Excel, where we don’t get the red squiggly lines underneath misspelled words like we do in Word.

Excel Spell Check

F7 will also work in PowerPoint

PowerPoint Spell Check

Alt + Q = Find Features

This is a big one. Sometimes you know that a feature exists in Microsoft Office, but you are just not sure where to find it. Press Alt + Q to search for it.

Forget how to get to the Accessibility Checker? Press Alt + Q and search for it…

Find Features

While I still think it is a good idea to know where features live in the ribbon, this is an excellent tool to find what you are looking for in a hurry.

Ctrl + Y  =  Redo

Companion to the famous Ctrl + Z (undo), Ctrl + Y gets a lot less fanfare, and I have no idea why. It is a great cheat if you don’t know a key command for something. Ctrl +Y will simply recreate the last action.

Let’s say you want to insert a row into Excel in a variety of different places. Maybe you don’t know the key command (Shift Ctrl + +), so you insert one row… with the old-fashioned right click , Insert.

Right click menu, insert

To insert another row,  go to the new location, and hit Ctrl +Y. Click on the next location, Ctrl +Y, and so on. This will be much faster than performing the old “Right click, Insert” method 20 or so times.

New row with Ctrl + Y

It also works for formatting. A couple weeks ago, I was editing a Word document with quite a few pictures that I wanted to have identical style formatting. I selected one picture, applied a style format, then selected the remaining pictures, pressed Ctrl + Y and voila! All the same formatting was applied.

picture formatting. Ctrl + Y to reapply

Note: Ctrl + Y will only apply the last action, so if you were making several format changes to an item, you might be better off copying formatting with the Format Painter. Please attend an Excel, Word, or OneNote Essentials session to learn more about this amazing feature!

Ctrl + Home/Ctrl +End = Go to the Beginning or End

Need to get back to the top of a document, or conversely, need to find the end? Try Ctrl + Home or Ctrl + End to navigate.

Ctrl + Home will take you back to the top of your document, and Ctrl + End will take you to the bottom.

End of a row

Note: this will require that you are using a full keyboard… sometimes the Home and End buttons are not present on smaller keyboards or laptops.

Ctrl + Shift+ (arrow) = select an entire column or row

If you have attended Excel Advanced Formulas you know this one. Ctrl + Shift + Down Arrow will select all the data in a column. This also works with a right arrow to select all the data in a row. And in Word, it will select an entire row of data at a time.

All data selected in column with ctrl + shift + down arrow

Outlook: Ctrl + Shift + M = Create Email

I mentioned in a previous article that Outlook has some amazing time saving shortcuts. This one and the next one are my two favorites.

Regardless of the Outlook module you are working in, Ctrl + Shift + M will create a new email message. No need to return to the mail module.

Below I was in my Calendar module, and started an email with this command.

Create Email

Outlook: Alt + S = Send Email

Once you are done typing your email, don’t touch that mouse… Alt + S will send the email.

There have been occasions where someone was standing behind me as I used this command and the last in Outlook, and they exclaim, “How did you do that??” The wizardry of key commands!

Word: Alt + Ctrl + 1 (or 2, or 3) = Format as Heading

We are all utilizing headers after attending Word Essentials, right? Well, here is a quick way to set a line to Heading 1, 2 or 3: Alt + Ctrl + 1 (or 2, or 3).

Header by pressing alt + ctrl + 1

Excel: Ctrl + T = Convert Range to Table

After you attended Excel Essentials, you ran back to your computer to convert all your ranges to tables, right? The functionality we see for tables goes even beyond what we see in Essentials and Pivot Tables sessions. It is just a good practice to start utilizing them.

Instead of using the Table feature from the Home tab of the ribbon, place your cursor in the middle of your data and press: Ctrl + T.

Ctrl + T converts to Table

Of course, you can still make adjustments via the contextual Table Tool tab.

Table Tools Tab

Thoughts?

What do you think, are any of these one of your favorite shortcuts? Or do you have a different favorite secret shortcut to share?

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

  • Kaleb Basham
  • Joan Wilson

Microsoft PowerUp! Videos Posted: Check Out What’s New in Office 365

Almost 150 people attended the special Microsoft PowerUp! Sessions…  If you missed them, or even if you attended and wanted to revisit some of the special content, videos are available now!

The purpose of these sessions was to give everyone a quick update on some of the highlights now available (or coming soon, depending on your update schedule) in Microsoft Office 365. Keep an eye out for future sessions as more updates are released…

PowerPoint Updates

Word Updates

Excel Updates

Bonus Content: Sandy

This is Sandy (Power User!), with a very kind testimonial. 😊😊

 

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

 

Erin LeBegue (not pictured)

Chris Leonard

Chris Leonard

 

Tyra Miles

Tyra Miles

 

Marsha Compton

Marsha Compton

Excel: Filter a Pie PivotChart and Maintain Percent of Whole (A Different Take on Progress Charts)

A fantastic question came up during last week’s open lab. Is it possible to create a PivotChart pie chart that can be filtered to show only the percent of the item selected?  Here is one solution for that question (and spoiler alert, it is technically not a PivotChart).

Donut Filtered to show percent remaining

A few things before we get started…

  • First, thank you to Marsha for having this question and inspiring this Byte!
  • Second, this article assumes you have some knowledge of Tables, PivotTables, and Charts in Excel. If you don’t, please come attend a session… times are listed in myTraining.
  • Lastly, this is basically a different take on the idea of a progress pie chart… and I am by no means the creative genius behind this overall idea! If you have a minute, look at some of the tutorials out there for the different use cases for these. They are mighty handy.

Background

Normally when you select a slicer or filter a Pie chart by one item, each item shows as 100%. Here is an example:

Below is a visual of a PivotChart Pie Chart…

Pie Chart PivotChart

Looks great… but when I add my slicers and filter by one item, it always shows as 100% of the total, because it is 100% of what is displayed.

As described above, showing 100%

What if we want to use those  slicers, but also maintain the visual of the item’s percent of the grand total… in other words, in this case, to still show as 36% of the whole? Some finagling is in order.

Exercise

Rather than an exercise, I am going to try something different. Here is the final document… and below will be the steps I took to create it:  Example Document

In the document, one sheet is the source data, and the other sheet is the final chart with slicers that will adjust the pie chart accordingly.

Click on a few slicer buttons to test it out. Pretty cool!

Donut Filtered to show percent remaining

Starting Point

We originally started with source data (on the Source Data sheet in the document) that consisted of two columns: item and a number.

Starting List, animal names and random numbers

Format as a Table

This first step might be optional, depending on your use case. Since data would be fluctuating in our case (e.g. new rows of data added), it was best to format as a table.

We learn about tables in Excel Essentials, and the advantages of formatting as tables in Excel Pivot Tables, so please come to a session if you are interested in learning more!

1. You can either go to the Home tab, Format as Table, or use the command Ctrl + T.

Format as Table circled in ribbon

2. Pick a design if you would like (in this case, yellow for WSU).Original data, formatted as a table

Create a Helper Column for Percent

We need a cheat column that would keep a percent constant when filtered, so a helper column was in order. You have probably noticed that formulas look different in a table… you still want to build them in the first cell of a column to carry them down.

We want the column to take the figures in column B and divide them by the total. The formula ends up looking like this: =[@Number]/SUM([Number])

Helper Column with formula for percent as described

This looks complicated, but it is very automatic. Here were my keystrokes/mouse clicks:

[click into the first cell of Column C], Type  = [click on B1] / SUM [Select B2 through B7].

Create a Pivot Table

  1. Click on the table to activate Table Design tab.
  2. On Table Design tab, Select Summarize with PivotTable.Table design tab, summarize with pivot table circled
  3. Location of Pivot Table: on a new sheet, titled Pivot.
  4. Build the table with Item as rows, Helper Column as Values.Pivot Table5. Insert Slicer for Item (on the PivotTable Analyze tab).

Create Helper Cells with GETPIVOTDATA

So here is the twist… Instead of creating a PivotChart, we are actually going to create a chart from helper cells that reference information from the PivotTable.

1. The first helper cell references the Grand Total of the PivotTable:

[Click in B1], type  =  [click on the Grand Total cell of the PivotTable].

Notice how this appears in the formula bar as a GETPIVOTDATA formula in the formula bar. This cell will always return whatever Pivot Grand Total is Displayed.

Helper cell with GetPivotData formula

2. The second helper cell, in B2, contains the formula =1-B1. So it will always read the difference of 100% from whatever the GETPIVOTDATA result shows.Formula in a different helper cell as described

Create Chart from Helper Cells

Finally, we can make the chart!

1. Select the two Helper Cells in B1 and B2.

2. Go to the Insert tab, and select the Pie Chart dropdown.

3. Select the Doughnut (it could be any of the pies, but this is the route I took for reasons you will see).

Insert Pie Chart, donut4. If desired, you can pick special colors by right clicking on any data point and selecting Fill. I picked some WSU colors.

Right click menu on chert

Finishing Touches

The number you see in the center of the doughnut is actually a title. Remember how we learn in Excel Pivot Tables, Charts and Pictures training that a chart title can reference a specific cell? This will come in handy here.

1. Title: Click on the line of the title and press = , then click on B1

So this Title will always reference this cell, which will always reference the grand total displayed on the PivotTable.

This means you will always have a number corresponding to the colors on the chart.

Header with formula directing bavk to helper cell

2. Text Box: This may be optional depending on your situation, but I inserted a Text Box at the top of the chart to read Percent of Total: Insert tab, Text Box.

3. Position Slicers: Final step was moving the Slicers and resizing them so they fit neatly on top of the Pivot Table. This was only cosmetic, and is also optional. Really there are infinite possibilities for how you could play around with final formatting with a chart like this.

Donut Filtered to show percent remaining

A Simple Alternative

It is worth mentioning that, depending on your situation, you may be able to skip the PivotTable step by incorporating a total row in your Table. This would apply if you didn’t need to sum multiple entries of specific items.

You would still need to incorporate helper columns, but instead of linking to the Grand Total of the PivotTable, they could link to the Total Row sum.

So many possibilities!

Thoughts?

What do you think? Has this sparked any ideas? Have you used pie/doughnut charts for this type of scenario before?

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

 

Robin Mishler

Robin Mishler

Megan Nold (not pictured)

Kylie Sharpe

Kylie Sharpe

Excel: Don’t Merge Those Cells! Here is an Alternative.

Friends don’t let friends merge cells! This is something you hear often among Excel enthusiasts. People usually merge cells in an attempt to make a spreadsheet look nicer. That being said… not only is the beauty of a spreadsheet less important than its functionality, which is definitely adversely affected by merged cells… but there is actually a way to alter the appearance identically to merging cells without all of the many disadvantages that come with merged cells. Let’s check it out.

Excel screen with merged cells

Exercise

If you would like to follow along with my demonstration below, here is an Exercise file: MergedCells

This is a fictional list of students and grades, with some merged cells at the top.

Merged Cells

The first row of data contains 3 sets of merged cells: A1 with B1; C1 with D1; and E1 with F1. If you select any of these, notice in your Alignment group that Merge and Center is selected.

Excel screen with merged cells

Perhaps someone did this in an attempt to make their spreadsheet look less cluttered? Let’s see why this might have been a bad idea.

Why Merged Cells are Problematic

There are actually quite a few ways that merged cells can be problematic. Here are just a few.

Sort and Filter

Let’s say I would like to use the custom Filter buttons we play with in Excel Essentials.  You want to filter by all students with an A.

1. Click anywhere in the top row, and on the right side of the Home tab, select Sort and Filter, and Filter.

Sort and Filter2. Now, Go to the dropdown created next to Grade.

Sort and filter by grade

Normally I would have the opportunity to filter by letter grade, but because E1 and F1 were merged cells, Excel instead only offers for you to filter by grade %. Not very helpful.

PivotTables

Maybe instead, we can make a PivotTable from the data, and pivot by the letter grade? Go to the Insert tab, and select PivotTable.

Pivot Table errors

What is this? We are receiving an error because we don’t have true column labels (header row) when cells are merged like they are in our top row.. this means that Excel doesn’t know what our categories are to create a PivotTable.

(Shameless plug: come to an Excel: Pivot Tables training if you would like to learn more.)

Formulas

This is probably the biggest one for me. Let’s say I want to count the number of 22 year old students in my class. No problem! Let’s do a CountIf formula.

In I1 I entered =Countif( … then I tried to select my range, column D…. look what happens:

Formula attempting to select full column

Excel doesn’t want to allow me to include column D alone… it wants to include Column C as well. How annoying! We could probably find our way around this formula issue, but even then, I guarantee these merged cells will get in your way with a future formula.

Macros

It is worth mentioning that there are macros that can be interfered with when you use merged cells; it depends on what type of macro you are building.

All in all, merged cells are just not worth the trouble.

Another Option: Center Across Selection

If you are truly attached to the look of merged cells, there is another option. It is called Center Across Selection.

  1. First, let’s undo the merged cells. Select the merged areas, then go to the Home tab, Alignment group, select the dropdown for Merge and Center, and select Unmerge cells.

Unmerge Cells

2. Select A1 and B1, and Right Click on top of them. Select Format Cells.

right click, format cells screen

3. In the popup screen, go to the Alignment tab, and click on the dropdown next to Horizontal. Select Center Across Selection. Click OK.

Alignment tab, center across

4. Repeat this step with C1 and D1 selected, then E1 and F1 selected. Appearance wise, it will look just like merged cells.

Center across looks the same as merge and center

This still will be somewhat limiting; for instance, you may still have difficulty with a PivotTable unless you convert this to a Table first, but you will not experience nearly as many drawbacks as merged cells.

A Question for You

Whether you choose to center across or merge cells, I think it is an important question to ask yourself, why are you wanting to do this? Is it truly necessary? When at all possible, I would recommend avoiding either of these practices. I understand the desire to beautify a workbook, but clearly labeled columns with long lines of uninterrupted data are the truly beautiful spreadsheets. Their beauty is in their functionality; and when functionality is lost, nobody will really care much about how the top row looks. Just a thought, from someone who has “unmerged” many cells in many peoples’ spreadsheets over the years.

Thoughts?

What do you think? Has this convinced you to unmerge and never merge again? Either way, I will be here to help you.

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

Sheree Smith
(First Power User of the decade!)

Sheree Smith