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

A lot of people prefer a form entry view to a table view for data entry. Forms for data entry are something we are all used to seeing in popular programs like Filemaker and Access, but also for something as basic as filling out an online form… that is data entry too, after all.

There are ways to accomplish this task with macros, but there is an even simpler way to activate a new view involving Tables.

Exercise File

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

ExerciseFormEntry

This looks familiar if you have attended Advanced Formulas training. We use this same data to calculate age from birthday and grade from a grade percent. Notice that columns E, G and H all contain formulas, while the other columns are free entry.

Important: Format as a Table

This data has been formatted as a Table. We talk about tables at great length in Excel Essentials. Remember how I said there are endlessly cool things that will happen in your life when you format data as a table? Here is another one!

Creating a Form Button

Form entry is not a command you can find in the ribbon, but there is a work around for this. You might recall that the Quick Access Toolbar (QAT) contains all the commands you can find on the ribbon and also commands not in the ribbon… let’s find this one.

1. Find the Quick Access Toolbar (QAT) above the ribbon.
2. Select the dropdown arrow at the right side of the QAT.
3. Select More Commands.
4. On the Choose Commands From dropdown, select Commands not in the Ribbon.
5. On the left pane, scroll down and select Form (features are listed alphabetically).
6. Press the Add button between the two panes.
7. Form will appear on the right pane.
8. Press OK

A new button will appear in your QAT for Form entry.

Using the New Button

Let’s test out the button.

1. Place your cursor anywhere inside the table.
2. Press the Form button you just created in your QAT.
   
3. A form entry box will appear.

New: Adding Entries

1. On the right side of the form view, press New
   
2. Enter in your own information (give yourself a good grade!)
3. Notice how the columns that contain formulas (E, G and H) are not enterable.
4. Tab to move between fields
5. Press enter once data is entered.
6. Scroll down to look at the new entry in the table.

Criteria:  Search for a Specific Field

1. On the right side of the form view, press Criteria.
2. Place your cursor in the Last Name field and try searching for Last Name: Green
3. Press Find Next to be taken to the next entry.

Possible Pitfalls

• If you are sharing a workbook with a group of people who would like to do form entry on your table, they will need to add the Form tool to their QAT too. They will still be able to type into the table without the form view, of course.

Thoughts?

What do you think? Do you have any old tables lying around that could use this handy 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

• Hannah Bates

Every once in a while, you find yourself in a situation where you need to perform a basic arithmetic function on a series of cells… maybe you need to take a group of cells and increase or decrease their number by 1, or by a percentage.

You could create a helper column, write a formula into it, auto fill down, then copy and paste the values back into their original spots, but that is a lot of steps for simple addition! You might be tempted to change the values by hand; adding one to each cell is not so hard, right? Well, don’t! There is an even faster way.

I have mentioned before that there is more than meets the eye in Paste Special options. Here is another great example.

Exercise File

To follow along, download today’s exercise here: ExercisePasteSpecialAddition

Scenario

This workbook contains a sheet called Attendance. In this scenario, my supervisor has asked that I add myself and him/her to the staff headcount at the January meetings. So, I would like to quickly add 2 to all the numbers in column B.

Paste Special: Operations

Since we will be using copy and paste for this, we need to have the number we want to add somewhere on the sheet so it can be copied. You can always delete it later: I populated this number in cell F1 for us.

1. Select F1
2. Either press Ctrl C on your keyboard or right click, copy
3. Select all the cells that need this figure added: so B2 to B28
4. In the Clipboard group of the Home tab, select the dropdown under Paste
5. Select Paste Special
6. In the popup screen, under Operation, select Add.
   • By doing this, we are telling Excel that, instead of copying the number 2 to all these cells, we would like to add 2 to the existing contents.
7. All the numbers in the selected cells have now increased by 2.Once you have finished this task, you could theoretically delete the contents of E1 and F1. We did not create formulas in column B, we just added to the existing number, so we are no longer relying on F1.
   

   More Arithmetic Options

   Did you spy the other options in the Operations section of Paste Special? You could also:

   • Multiply
   • Divide
   • Subtract

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

• Lana Anthis
  
• Cindy Sharp
  
• Susie Steinbach

“Show me a person who likes HLOOKUP and I will show you a person who doesn’t know about transpose in Excel,” quipped Mr. Excel (Bill Jelen) at Excelapalooza last fall.

Indeed, so many people are crazy about the VLOOKUP function, but you rarely hear anyone talk about VLOOKUP’s less popular cousin HLOOKUP.  This is partially because people tend to orient their spreadsheets vertically (as they should!). But also because, in the rare instance of a horizontally oriented list, the transpose feature in Excel has you covered. Transpose will allow you to turn a horizontally oriented list into a vertically oriented list, or vice versa.

“Why haven’t I seen this transpose button?”

Well, it is not a button per se… (but it should be!). Excel has packed some sneaky features into their paste options. This is just one of them (more on this later).

Exercise File

If you would like to follow along, download today’s exercise here: TransposeExercise

On this sheet is a list of letter grades and GPAs. It is horizontally oriented, and I would prefer to see it vertically displayed.

Transpose: Paste Special

1. Select cells D1 through P2 (all the data).
2. Copy the data to your clipboard, either by pressing ctrl C or right click, Copy.
3. Select cell A1. This is the beginning of where you will paste your data.
4. In the Clipboard group of the Home tab, select the Paste dropdown.
   
5. Select Paste Special.
6. Check the box next to Transpose and press OK.

And there is your data, now vertically oriented!

It is worth reiterating that this is just the beginning of paste options in Excel. More on this at a later date…

I hope this feature saves you some time and frustration with horizontally oriented lists. Sometimes it is these small but mighty hidden check boxes in Excel that make all the difference!

Congratulations, New Power Users!

• Sandy Parker

The Name Box

Most of us don’t give a lot of thought to the humble Name Box in Excel. We glance up at it occasionally to see which cell we have selected, but few people realize that there is a lot more than meets the eye with this feature.

Did you know you that this little box allows you to name a cell or range of cells? I could even rename a cell after myself.

You will be shocked to hear how easy this is… all I did was type over the cell name in the name box and suddenly “A1” became “Ali”.

Exercise Download

Let’s see how this works with a range of cells. For this, follow along with this document: NamedRangesExercise .

This data will look familiar to those of you who have taken Excel Essentials. It is a list of fictional employees, departments and salaries.

…By the way, you might find it useful to resize the name box for today’s exercise. This can easily be done by hovering your mouse to the right of the name box until you see a double headed arrow. Then simply click and drag.

Named Ranges

I would like to name the Salary data so I can easily find and select it.

1. Select Cells E1 through E22
   • Remember: to select a cell in Excel you hover your mouse over the first cell, and you are looking for your cursor to turn into a white cross for Excel to select it. Then click, hold down the mouse and drag down to the bottom of the list.

2. In the Name Box click over the existing cell name (in this case, E1),
   

2. Then replace it with the word Salary and press enter.

Click onto any other cell to deselect the range.

3. Notice there is now a dropdown that appears in the name box now with your newly created Salary moniker.
   • Test it out! Select it from the dropdown… it should select your entire range.

Named ranges allow users to quickly find and select a range of cells. Guess what else named ranges help you do? Formulas!

Formulas with Named Ranges

I would like to quickly figure out a 10% bonus for my employees based on the Salary range.

1.  In F2, type = Salary*.1

• Before you even finish typing salary, you will probably see something like this.

• Press Tab to accept the range or select it with your mouse.

2. The end formula looks something like this:

3. Then use AutoFill to take that formula down to the bottom of the list.
   • Remember the trick we talked about in Advanced Formulas for a quick AutoFill? Hover your cursor in the lower right until you see a black plus sign, then double click to carry the formula down.

Managing Named Ranges

Inevitably, someday you will want to manage your named ranges. They live in the Formulas tab, in a button titled Name Manager.

Select Name Manager to see your full list of named ranges, alter, or delete them.

Have you thought of a way you will use Named Ranges? Tell me about it!

We usually think about formulas in Excel in terms of numbers, but there are also a handful of formulas and functions for text.  The good news is, these functions are not only incredibly useful, they also happen to be user friendly! (we are looking at  you, complicated nested IF formulas)

Perhaps you have inherited a list of names in all lower case or all caps that you want to return to proper case… or vice versa, the list is in proper case and you long for a list in all caps (no judgement). Here is a quick way to transform that list in to the text case you want.

Follow along with the attached document NameList.

Let’s do Proper Case first as our template:

1. Click into cell B2
2. In the Formulas Tab, Click on the “Text” Function button
3. From the drop down list, select PROPER.
   
4. In the Function Arguments Box, Click in the white space next to text.
5. Click on Cell A2 (or type A2)
6. Click OK
7. Double click on the autofill handle to carry the formula all the way down.

Nifty!

Now try lower and upper case.  Follow the same instructions, but instead use the Text formulas for LOWER and UPPER in cells C2 and D2 respectively.

New Power Users:

Congratulations to the newest WSU Microsoft Office Power Users!

• Ellen Abbey
• Ross Powell

See the full gallery at wichita.edu/poweruser