Formulas and PivotTables. Many of us have gotten caught up creating formulas outside of a PivotTable by referencing cells within a pivot table, only to be disappointed when we update our PivotTable and cells shift back and forth. There are several ways of dealing with this issue. One way is to create a Calculated Field within your PivotTable. This is something we cover briefly in Excel PivotTables sessions, but it is definitely worth a review.

Exercise File

If you would like to follow along, here is an exercise file:

This document contains two sheets: one sheet contains the PivotTable, and the other contains the source data, showcasing a couple years of dessert sales. Right now, you have income and expense information in the values of the PivotTable. Since we have those figures handy in our data, it would be nice to figure out net income.

For this Byte, I am assuming you are comfortable with PivotTables in Excel. If you are not, please come attend my PivotTables, Charts, and Pictures sessions! Sessions are listed in myTraining and are updated regularly.

PivotTable Contextual Tabs

We are going to be working a lot with the PivotTable Tools contextual tabs. Like any other contextual tab in Microsoft, remember that you need to click on the element to see the corresponding contextual tab(s).

When you click on the PivotTable, you should see two contextual tabs for PivotTable Tools: Analyze and Design.

Be sure you can see these tabs before moving on to the next step.

Inserting a Calculated Field

Remember, our end goal is to create a Net Income field that will subtract the Expenses from the Revenue.

1. With the PivotTable Tools activated, go to the Analyze tab, Calculations Group.

2. Select the dropdown for Fields, Items & Sets and select Calculated Field.

3. In the popup screen that appears, you will see all the PivotTable fields listed at the bottom of the screen. At the top is an opportunity to name the field and a space for a formula.

4. Click in to the Name box and change Field1 to Net Income.

5. Click in to the Formula box, and double click on Revenue in the Fields area. Type a “-” on your number pad, and double click on Expense. We are building a formula that will read: = Revenue – Expense.

6. Press Add.

7. Press OK, and look at the field you just created incorporating a formula from two other fields. Pretty cool!

Notes

• PivotTable Fields: You will see this calculated field appear in the Values section of your PivotTable Fields.

• Calculations from Calculations: If you are creating another Calculated Field, you will see the Net Income field we just created as a field option for use in future formulas.

Thoughts?

What do you think? Can you incorporate Calculated Fields into your PivotTables? I would love to hear from you!

Open Labs

Did you know that Applications Training has open labs every other week? Whether you have a Banner question or a Microsoft question, feel free to stop by! Open lab times are listed in myTraining under the title Open Lab Assistance on Banner 9, Time Entry, Reporting, & Microsoft/Adobe.

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

Julie Scott

Who doesn’t love slicers? What an awesome way to sort and filter data. We have talked about how, in addition to being a time saver, slicers can make it easier to share your data with others who are not as comfortable working in Excel. Not everyone understands, “filter the data,” but we all can understand “push this button!”

At the same time, in the advanced Excel sessions, we talk about how you often want to protect a workbook from accidental changes, particularly after you have spent a lot of time creating elaborate formulas. Unfortunately, protection also renders your slicers useless… Here is a great compromise: you can protect a document and leave your slicers fully functional by incorporating a very simple method. Let’s take a look.

Before we do, big thank you to Erin for having this question in last week’s Excel Essentials session and inspiring this byte!

Exercise File

If you would like to follow along, you can download the exercise file here:

This is a list of fictional students in a fictional class with their fictional grades. (This is fictional data, friends). In this scenario, it looks like you decided to turn this data into a table, because of course you did! And you added some slicers for ease of use so you can quickly filter and only view students with A’s or Freshmen with A’s, etc.

By the way, if you are not fully comfortable with tables or slicers in Excel, please come attend one of my Excel Essentials sessions! I would love to see you.

Disclaimer

• With protection options, be very careful when applying passwords. If you lose the password, unfortunately, we cannot get it back for you!
• You can also lock a document without applying a password, which is handy to know if you are worried less about dishonesty and worried more about accidental typing. This is the route we are going to take today.

Unlock Slicers

1. Right click on a slicer and select Size and Properties.

2. On the right side of the screen a Format Slicer menu will appear. Click the triangle next to Properties.

3. Uncheck the box next to Locked.

4. Follow the same steps for the second slicer.

Protect Sheet

1. In the ribbon, go to the Review tab, Protect group and select Protect Sheet.

2. This is where you could set up a password, but for today, let’s leave that area blank.

3. Scroll down in the list and check Use AutoFilter. Click OK.

Now… notice that you cannot make a change to a specific cell (error message below), but the slicers should be fully functional allowing users to filter the data.

Unprotect Sheet

Don’t forget, when you want to edit, you can Unprotect the same place you Protect a sheet.

In our case, this was on the Review tab, Protect group. It can also be found in Backstage View.

If we had created a password, this is also where we would have been prompted to enter it to unlock the sheet.

Thoughts?

I would love to hear your thoughts on this! Have you been frustrated by not being able to protect a worksheet and user slicers? Will this save you any trouble?

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

In honor of the wizardry of technology, and because everyone should be having a blast during summer break, I made a fun exercise for us in Excel… a crystal ball… via the magic of Excel Macros.

To give credit where credit is due, this was inspired by an exercise Mr. Excel showed us at Excelapalooza last fall.

Exercise File

Here it is… ask it anything! Crystal Ball Document

Allow Macros

This magic involves macros, so if you want to use this crystal ball, you might be prompted to “allow macros” or “Enable Content.” If so, go ahead and allow them. And more on this later…

How to Use it

Alright, this is about a sophisticated as a Magic 8 ball, which is to say, you will think of a yes or no question, then press the “Ask Me Anything” button on the Crystal ball.

Your answer will appear in cell B2, next to the “Your Answer” writing. Keep pressing the button for different answers (there are some good ones in there).

Look Behind the Curtain

How did I do this? Let’s take a peek behind the curtain.

Unprotect Workbook

This is a protected workbook. Go to the Review tab and select Protect Workbook to toggle off the protection. I did not apply a password. This will allow you to discover a sheet I have sneakily hidden.

Unhide Sheet

Right click on the tab of the sheet (where you see the sheet name) and select Unhide.

Hey there is a hidden sheet in this workbook! This looks a lot like the results we have been seeing when we ask the crystal ball for answers. But where do these results come in to play?

Formula in B2

Go back to the Crystal Ball sheet and click on cell B2, where the results appear. Look up in the formula bar… it looks like there is a pretty nifty nested formula that is selecting a random number between 1 and 14 and matching it up to possible results.

If you want to learn more about nested formulas, come to one of my Excel Advanced Formulas sessions! Those of you who have attended before, I could have also used VLOOKUP on this cell, but for some reason I was feeling old fashioned and went with the old Match/Index one-two punch.

Macro

Okay, the hidden sheet makes sense, and the formula is logical, but what does this button have to do with anything? How exactly does this work? The answer is… Macros!

What are Macros?

Macros are basically a shortcut you create that will perform a series of commands. If you get tired of copy/pasting as values in Excel, you could make a macro button that would do that for you. I once talked to someone who consistently wanted to print only the first page of a document, so we made a macro that would do just that.This saved her a lot of clicks throughout the day.

Basically, any combination of commands, formulas, you name it, you can create a macro for it. Macros use a special code called VBA (Visual Basic for Applications). If you want to take a look at the super simple macro behind this button, press Alt + F11 on your keyboard.

Now, don’t let this scare you off, macros can also be recorded via a macro recording tool in Excel. You don’t have to learn VBA to create macros, though it can really help.

I Thought Macros Were Something Bad…

I hear this a lot. Sometimes you open a document and receive a stern warning about macros… you may have even received a message like this when you downloaded this exercise. Because macros carry coding for actions, they can hide malicious code from ne’er-do-wells. Think of it this way, macros are a tool, and any tool can be used for good or bad purposes. So if you download something from a source you are unsure of, and you want to be extra careful, don’t allow macros to run, though know this will possibly reduce functionality. But if you made the macros yourself, or it is sent to you from a trusted friend (like me), you are probably fine.

Are You Interested in a Macros Session?

This Byte was a super fly-by narrative about macros. A couple of you have told me you are interested in learning more about macros… if you would like to attend a session like this, let me know! If there is a lot of interest, maybe I can put something together. You can either comment on this post or send me an email. I always love to hear from you anyway.

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

Slicers are hugely popular in both Excel Essentials and Excel Pivot Tables training. We go for standard slicers in both of those sessions, but there is another type that is particularly fun to play with if you are working with dates; the Timeline Slicer.

Exercise File

Download today’s Exercise File to follow along:

This is… what else… some dessert sales! Desserts make their way into a lot of my training exercises (wonder why?).

The Dessert Sales sheet shows entries in checkbook style: date, dessert type, and then income and expense on each day.

The Dessert Pivot sheet is referencing data on the Dessert Sales sheet. You must have a pivot table with dates in order to use the Timeline Slicer tool. If you are not comfortable with Pivot Tables, check out my session Excel: Pivot Tables, Charts and Pictures, listed in MyTraining.

Create the Slicer

1. Go to the Dessert Pivot sheet
2. Click on the pivot table to activate Pivot Table Tools contextual tabs
   
3. Click on the Pivot Table Tools Analyze tab
4. In the Filter group select Insert Timeline
   
5. Select Date and press OK

Look at this nifty Timeline slicer you just created!

Working with the Timeline Slicer

Just like your standard slicers, you can click on a month to narrow down information in the pivot table.

Notice you also have the ability to select a range of months by hovering your mouse between two months and clicking and dragging.

To clear the filter, click on the clear filter button at the upper right of the timeline slicer.

Timeline Tools

Click on the timeline to select it and notice this activates a Timeline Tools contextual tab.  

Among other options, you have the ability to adjust the size of the Timeline and change the color to another available theme color.

Troubleshooting

Oh no, I sliced by a month and then accidentally deleted my slicer! How do I unslice my data?

Don’t forget that whenever you create a slicer, you are creating a shortcut to filter. Notice how a filter appears at the top of the row labels.

To clear this filter click on the filter button and select Clear Filter from “Months”.

Thoughts?

What do you think? How will you use the Timeline tool with your workbooks?

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

Glenn Gunnels

Jeremy Webster

I have been including this as a bonus exercise in our Pivot Table sessions, but it is too good not to share as a Byte. I learned about this magical feature (wizardry) from a session taught by Mr. Excel last fall. This is the beginning of the cool AI features to come with Microsoft Office… it is an amazing tool to help you better illustrate location oriented data, or better yet, location data on a timeline. Let’s check it out.

Availability

This feature is available to those of you using the Windows version of Microsoft Office 2016 or later.  WSU faculty and staff, if you have an older version of Office and would like to upgrade, please contact the Help Desk at 4357.  If you are a Mac user and want to try out this feature, please come to one of our open labs listed in myTraining! We would love to have you, and you will be able to export the data we create for use in presentations.

Exercise File

Follow along with the exercise file: GeographicInfo

This is a list of fictional student registrations: student hometowns, class, and the date they registered.

Data Layout

Notice how this list is laid out vertically: each column is a new piece of information and each new row is a separate entry. This is exactly how data should be laid out in Excel: for pivot tables, for graphing, and also for geographic data.

Oh, and remember… friends don’t let friends merge cells. It can really mess with features that create visual elements with data… But I digress.

 Creating the Map

Just like with your pivot table data, you do not need to preselect all this information.

1. Place your cursor anywhere inside the data. I selected cell B5 randomly.
2. Go to the Insert tab, and in a group called Tours select 3D Map.

Note: if this is your first time using this feature, you may be asked if you want to enable this feature. You do. This is going to give Excel permission to access these maps online.

Another Note: it may take a few moments for the feature to run… so don’t be alarmed if you see the famous spinning wheel.

3. A new screen will load with a map view. You can move the globe around with your mouse, or zoom in with the + and – buttons at the lower right.

Adjusting the Map

We want to see more information than just data points, though. Notice that the right side of the screen has an almost pivot table feel: with different groups of customizable information. Let’s insert a couple fields:

1. Press the + next to Category and select Class
2. Press the + next to Time and select Date

Now rotate your globe, and notice that there is dimension to the entry points…

but that is not all you have just done…

3. Notice there is a play button at the bottom of your screen. Press this button to see a moving timeline of registrations.

Isn’t this awesome??

Export Options

Take a look at the ribbon in the Map view. Notice the second option allows you to Create Video or Capture Screen. Maybe you would like to incorporate this live timeline into a PowerPoint, or maybe you just need a quick picture of the map. These options will allow you to do just that.

Saving Your Maps

This file is going to save with your excel file once you exit the map view. To access it again after closing out of map view, revisit the Insert tab, and select 3D Maps. Instead of starting over, this also gives you the option to revisit the “tour” you created. You might choose to create several!

Thoughts?

I would love to hear how you end up using 3D maps in your work space! Let me know. 😊

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

• Jose Intriago Suarez

There are two types of people in the Excel world: people who rename their sheets by double clicking, and people who rename their sheets by right clicking. A lot of us are double clickers, and therefore have never noticed the plethora of options for organizing sheets available on the right click menu. There are some very useful organization tools available to you on this menu… Let’s take a look.

Exercise File

You could really follow along on any old Excel spreadsheet, but if you would like one to experiment upon, here is the exercise from the Form Entry blog entry: ExcelSampleWorkBook

This workbook contains a list of fictional course grades that work with in some of the Excel trainings.

Right Click Menu

To try out the options below, hover your mouse over the sheet name (Fictional Course Grades) and right click. A menu will appear to select the options lined out below.

Rename

This is the one most of you know about already. You could also rename a sheet by double clicking on it.

Move or Copy

Okay, this is such a useful time saver. If you have a sheet you would like to duplicate or move to another location, rather than selecting the entire contents of a sheet and copy/pasting it, consider this option.

When you select Move or Copy a pop up will appear with some more options.

• The dropdown at the top will allow you to select any open Excel Workbook to move or copy your sheet to. So if you have other Excel workbooks open right now, you will see a list of all of them here.
• The center section allows you to choose where in the workbook you would like the sheet to go (in front of or behind existing sheets).
• The Create a copy box is something you would want to check if you don’t simply want to move the sheet, you would also like it to remain in its original location.

Check this box and press OK. Notice you now have two sheets with the same content in the same workbook.

This is especially handy if you are wanting to create multiple versions of tables, charts, or data lists that are similar enough in layout that you don’t want to reinvent the wheel every time.

Color Code Sheets

On to many people’s favorite organizational technique: color coding!

Select the Tab Color option. This will bring up the full list of theme colors and a full color wheel under More Colors.

I selected the orange standard color. Notice how it will look a little bit different depending on if you have selected the color coded sheet. Typically the color appears a little bit faded when selected. I created a Sheet 3, and selected it in the image below… you can see how it looks different from the first two sheets.

Hide Sheets

In Excel Essentials we talk about hiding rows and columns. In Advanced Formulas we talk about how sometimes you want to put formulas on one sheet that reference another. Here is a great feature that brings the two concepts together. Maybe you want to put a list of data on a hidden sheet, and only show the summary pivot table on another. Hiding a sheet allows you to do that.

Just remember, just like with hidden rows and columns, anyone will have the ability to unhide them. To unhide a sheet, right click on top of the existing sheet, and select Unhide.

 

We talk about protection options in Advanced Formulas, which can address some of the privacy issues with hiding sheets and rows, but protection comes with some large caveats, so if you are interested in learning more about protection, please come to an Excel Advanced Formulas session. Plus, it is just fun to learn about formulas, so come anyway!

…or of course feel free to email or call me and we can chat about it. 😊

Thoughts?

What do you think? Will any of these organization tips help you with your Excel workbooks?

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

• Brenda Achey (as Flash Phil)

 

 

We talk about calculations with dates in Advanced Formulas training (hooray for DateDif!). Working with time in Excel can be a bit trickier. I have seen people give up when formulas don’t work initially, but sometimes all that is needed is a change in formatting. Let’s see how we can work with time in a common example.

Exercise File

To follow along, download the exercise file here: TimeCalcExercise

• In this file is a list of times that you attended some fictional February meetings. You want to figure out how much time you spent in meetings last month.
• Note that there are two sheets in this workbook: February Meetings, which is your challenge, and Solution, so you can check your work as we go.

Number Formats

You are going to see that the formulas themselves are going to be very simple: basic addition and subtraction. The real key with times is the Number Format.

We are going to be using several Custom number formats. In the custom time formats, h stands for hours, and m for minutes.

• h:mm AM/PM: 12 hour time, indicated by AM/PM, for example 11:00 PM
• h:mm: 24 hour time, not including AM/PM, for example 11:00.
  
• [h]:mm: Elapsed time, beyond a 24 hour clock. With this you are adding total number of hours, not adding hours to a clock of revolving time.

Start and End Time

The first thing we should do is fix up the start and end time formatting so Excel will know what we are trying to calculate. I created a couple helper columns in Columns C and D called Extract Start and Extract End. Columns A and B are a common format that happens when you export time from other programs.

 

Extract Start and End Time with Flash Fill

There are a number of ways you might chose to do this, but as a fan of Flash Fill, that is the route I would like to take.

1. Create a pattern: click into C2 and type 9:00 AM (from A2)
2. Select C3 an type 11:00 AM (from A3)

3. Select C4. Go to the Home tab, Editing Group, Fill Dropdown an select Flash Fill

This should complete the rest of Column C. If it doesn’t, double check that you have established a pattern that matches what you are seeing in Column A.

Complete Column D the same way, extracting the end time from Column B.

Check Number Format

Let’s check the number format for columns C and D.

1. Select C2 through D32.
2. In the Home tab, Number group, click the More arrow in the lower right.
3. The Type should display as h:mm AM/PM. If it doesn’t you can type it in manually, as it is listed below. This means you are view time in a way most of us are familiar with: for example, 12:30 PM.

We are going to revisit this number format area again soon, so stay tuned.

Calculating Time Spent in Each Meeting

Let’s calculate the time spent in each meeting, and then we can move on to calculate a grand total.

Subtracting Time

Column E, Total Time, is meant to calculate the time spent in each meeting. This is going to be a simple subtraction.

1. Select Cell E2.
2. Type =D2-C2 . You can also select cells D2 and C2 as you are creating the formularather than typing the names of the cells.
3. Press enter to calculate.

4. Auto Fill the rest of the column, either by clicking and dragging or double clicking on the auto fill handle.

Adjust Number Formats

Let’s check the number format of Column E. Excel may have assumed that you wanted to use the same number formatting as C and D, but remember that includes AM and PM, which isn’t relevant for Column E.

 

1. Select Cells E2 through E32.
2. In the Home Tab, Numbers group, press the More button at the lower right.
3. This time, you want the number format to be simply h:mm. So no AM/PM necessary. Make sure you are on the Custom category.

   Click into the Type field and type h:mm (or find it in the type list).

Calculating Grand Total

1. In Cell A33, type Grand Total.
2. Select Cell E33: This is where we are going to calculate the total time.
3. In the Home tab, Editing group, select the AutoSum dropdown and select Sum to add all the times in column E.

Grand Total Formatting

Something is not quite right about our Grand Total.

This is all about number formatting. This Sum is displaying in the same format as the rest of column E: h:mm. This means Excel is adding the time on a 24 hour clock, which is not what we intended.

Let’s make one more number formatting adjustment.

 

1. Go back to your Number Format options, just like above.

 

2. In the Type, type square brackets [ ] around the “h.” This will send the message to Excel that you want a total elapsed time… beyond the 24 hour clock. So it will look like [h]:mm

Now the total elapsed time is showing in hours and minutes.

Perfect! So it looks like you have spent almost 36 hours in these fictional meetings last month.

Solution Sheet

Did you get lost somewhere along the way? Double check your answer with the Solution sheet in the exercise file.

Thoughts?

Do you have a use for calculating time in your day to day spreadsheets? Do tell!

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

Catherine Lawless

Jackie Boyles

Juanita Fonseca-Rodriguez

Krista Searle