Excel: Paste Special Operations: Quick Arithmetic with a Range Cells

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.

Select F1
Either press Ctrl C on your keyboard or right click, copy
Select all the cells that need this figure added: so B2 to B28
In the Clipboard group of the Home tab, select the dropdown under Paste
Select Paste Special
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.
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

Accessibility Checker

How accessible are your documents? Do you know how to check? Do you know it is easy and not scary at all? I promise. Let’s run an accessibility check a document.

Exercise File

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

This document looks familiar if you have attended a Word Essentials training session; it is a final draft document from our Summer Camp Exercise. While this is a Word document, the good news is the checker will work the same in Word, Excel, and PowerPoint; so once you have learned it in one program, you have learned it in all of them.

Checking Accessibility

To check accessibility on this document:

On a PC:

Go to File
Select the Check for Issues dropdown
Select Check Accessibility

On a Mac:

Go to Tools
Select Check Accessibility

Depending on your version of Microsoft, you may see the Accessibility Checker make an appearance in other views and tabs in the ribbon. Go for it! Microsoft is great about putting features in multiple locations, making them easier to find.

The Results

Once you select the Accessibility Checker, the results will appear on the right. It looks like my checker has found a few errors and a warning…. Some missing Alt Text and some repeated blank characters. What does this mean?

Classifications

Microsoft separates these results into three classifications:

Error. Content that makes the document difficult or impossible to read and understand for people with disabilities
Warning. Content that in most (but not all) cases makes the document difficult to understand for people with disabilities
Tip. Content that people with disabilities can understand but that could be presented in a different way to improve the user’s experience

Quick note, depending on your version of Microsoft, your checker may find different results than mine, so don’t be alarmed if your screen looks different. These checkers are constantly being developed and improved upon… the good news is, things are only getting better and more user friendly!

More Information About Errors and Warnings

For even more information, click on one of the errors, and look at the scrolling pane underneath the errors, titled Additional Information. Included in this pane is a Why Fix section with detailed explanations.

I can see, for instance, that my blank characters, which seem innocuous to me, could be very irritating to somebody accessing my document with a screen reader. I might be better off removing some of them or replacing them with a page break, if that is what I am really hoping to accomplish.

What is Alt Text? This is one you will see quite frequently. Alt text stands for Alternative Text. Screen readers will read alternative text aloud to your readers. So, think about the times you have created a document and conveyed a thought with an image rather than text; somebody accessing your document with a screen reader might want in on some of that knowledge so they can understand what is going on.

How to Fix

You probably know how to get rid of blank spaces mentioned above, but are you unsure how to fix problems like Alt Text?

It is worth reiterating that solutions are only getting simpler: if you are using Microsoft Office 365, the newest version of MS Office, Alt Text is accessible on a right click menu.

That being said, most of us on campus are using Office 2016 or 2013, where Alt Text is absent from a right click option.

Microsoft does offer some guidance for How to fix each error:

In the Accessibility Checker results pane, select the alt text error.
In the same scrolling pane underneath the errors where we found why to fix the errors (above) is a subsection called Steps to Fix (you may have to use the scroll bar to see it).

These instructions are quite helpful, though I have found that they are more accurate for those using 2016 or earlier versions of Microsoft Office, where Alt Text is harder to find.

It looks like my instructions (For MS Office 2016) are telling me to:

Right click on the image
Select Format Picture
Select Layout and Properties icon (looks like a four-headed arrow)
Then enter Alt Text: How would you describe this picture to someone?

More about Accessibility

There is so much more to say about this topic, but I will leave you with a couple more thoughts:

Styles

An excellent way to make your document accessible and utilize many more fabulous features in Microsoft products is by making use of the Styles. We cover styles at great length in Word Essentials training… I hope you are using them! More on these later…

Instructional Design and Access

Instructional Design and Access is the ultimate resource on campus for accessibility in documents. They offer numerous training sessions and excellent guidance. They can be reached at IDA@wichita.edu

Don’t miss their Blackboard and Accessibility Lab in the C-Space in the library every Tuesday and Wednesday from 1:00 to 3:00.

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

Betsy Main

Karen Rogers

Excel: Transpose

“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

Select cells D1 through P2 (all the data).
Copy the data to your clipboard, either by pressing ctrl C or right click, Copy.
Select cell A1. This is the beginning of where you will paste your data.
In the Clipboard group of the Home tab, select the Paste dropdown.
Select Paste Special.
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

Outlook: Journal Feature

Power Users will have to pardon me for repeating myself, but I can’t stop shouting about the Journal feature in Outlook. What a cool tool; deceptively simple yet useful in a multitude of ways, this creature is a favorite in Outlook Essentials training.

There are many ways you might choose to organize your phone calls and conversations; OneNote, an old-fashioned spiral notebook, your impeccable memory… but the Journal is by far my favorite go-to for this. It integrates seamlessly into Outlook; it is searchable, compatible with your calendar, and a welcome addition to the Outlook universe.

Have you never used this feature? You are not alone. I rarely meet someone before Outlook Essentials training who has heard of it.

Apologies to Mac Users

Before I jump in too deep, I have sad news for Mac users… there is not a Journal feature in the Mac version of Outlook. This is only within the desktop PC Outlook program. Not fair! I am sorry, Mac friends.

Where is the Journal?

Even for PC users, Outlook does not make the Journal easy to find.

To find the Journal:

In the lower left navigation, go to Mail Module

Click the “…” on the right side of the Navigation

Select Folders

On the left side of the screen select Journal.

Make a Shortcut

Since it is a bit of a trek to find the Journal, you might find it helpful to create a shortcut. There are a couple ways to accomplish this, but I am particularly fond of the Quick Access Toolbar (QAT). The QAT is a series of tiny buttons above the ribbon in Microsoft programs.

To add the Journal to the QAT:

Press the down arrow on the right side and select More Commands

In the dropdown at the top of the popup, change Popular Commands to All Commands

Scroll down until you find the Journal (the entries are alphabetical). Select the Journal on the left pane, press the Add button between the two panes. Then it will appear on the right pane

Press OK.

Now you can navigate to the Journal by pressing your newly created Journal button in the QAT.

Journal Entries

Making a journal entry is remarkably intuitive. Let’s create one. First, navigate to your Journal by pressing the new QAT button.

In the ribbon of the Journal, select the first button, Journal Entry.

In the popup screen notice the ability to enter a subject, an Entry type (e.g. phone, conversation, etc).

You can manually enter a start and end time, or you can make use of the built in Timer.

I particularly like the timer for phone calls.

Once you have created a Journal entry, press Save and Close on the left side of the ribbon.

Searching

Now you have created a searchable entry in Outlook. While you are still in the Journal module, there is a search feature in the upper left. If a couple weeks go by and you can’t remember certain details, now you can search by a word and quickly find all the Journal entries

Calendar Integration

You might want to add a Journal entry to your calendar. Perhaps your phone call was particularly long, or perhaps you would like a record to be visible to people with whom you have shared your calendar. To add a Journal entry to your calendar:

Click on the entry in the Journal entry list
Hold down your mouse, and drag this down to the calendar icon in the lower left navigation.

This will create a calendar event with your Journal details.

Note that you will have to hand enter start and end times for the calendar event.

Thoughts?

So what do you think? Do you have great plans for the Journal in Outlook?

Excel: The Name Box and Named Ranges

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.

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.

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

Then replace it with the word Salary and press enter.

Click onto any other cell to deselect the range.

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.

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.

The end formula looks something like this:

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!

Word: Citations and Bibliography

In Word Essentials training we talk about one time saving option in the References tab, the automatic Table of Contents. If you thought that was cool, you are going to want to go back in time and redo all of your high school and college papers using the Citations and Bibliography features in the References tab.

Exercise File

To follow along, download the exercise file here: CitationsExercise

This document contains a lovely writeup about Microsoft Word that needs a couple citations (indicated by placeholders that read Source 1 and Source 2), and it also needs a Works Cited/Bibliography section. The two concepts go hand in hand, as we will see in the exercise. The yellow text box contains the source information for these citations. But enough back story, let’s jump in.

The Ribbon

For this exercise, we will work from the References Tab, Citations and Bibliography Group

Step 1: Choose a Citation Style

In the Style dropdown, select the desired citation style; e.g. APA, MLA, Chicago.

The style you select will guide the fields that will appear in your Works Cited section

Step 2: Insert Citations

Remove the bold placeholder text that reads Source 1, and place your cursor at the end of the sentence where the placeholder text had been. Wherever your cursor is flashing is where the citation will appear.
From the References tab, Citations & Bibliography group, select Insert Citation, Add New Source
For Type of Source select Article in a Periodical. Complete the fields that appear in the prompts by using the information in the gold text box. Press OK.
- Notice the Citation inserts where your cursor was:
Now replace Source 2 placeholder text the same way, only select Journal as the Type of Source.
- There are a couple additional fields you might want to add: Volume and Number.
- Even though that doesn’t appear in the Fields list, you can check the box next to Show All Bibliography Fields and scroll down to see more fields and enter this additional information.

Step 3: Insert a Bibliography or Works Cited

Now the easy part!

Either remove the text box or click underneath it. The Works Cited will insert wherever your cursor is flashing.
Go to the Citations & Bibliography group and select from the list of options.
A Works Cited list will appear where your cursor was.

Making Adjustments: Managing Sources

I see a typo from my data entry, so I want to change my source and update my Works Cited.

To edit or manage sources:

Go to References tab, Citations & Bibliography group, select Manage Sources.
Select the source to be edited and select Edit to make changes (optional).
If any sources are edited or added, the Works Cited will need to be updated, just like the automatic Table of Contents we created in Word Essentials. The process is the same: right click over the Works Cited and select Update Field.

By the way, this is just the beginning of what you can do working with citations. There are also ways to transfer sources from old documents to new documents, to change citation styles mid paper, and so much more… but I promised a bite sized piece of information, so I must stop myself here!

I think you should go back to school and take a class or two just so you can play with this amazing feature in Word. I know a place where you can take some classes…

Outlook Formatting Woes: Replying to Email Sent from a Phone

You have probably experienced this scenario:

On your desktop Outlook you open an email that a coworker sends you from her phone.

You notice that the formatting looks sparse, but you don’t think much about this until you start to compose your reply. Your signature looks different than usual, you can’t format text, or change fonts… even doing something simple, like making a word bold or italic, is impossible as a good portion of the ribbon is greyed out and unselectable. What is going on here?

When your coworker emailed you from her phone (or tablet), most likely the formatting of the entire email changed over to Plain Text. This means minimal to no formatting and no images, even for your responses to her email.

How to Fix

Want to hear the good news? You can fix this in two clicks.

Important: first, be sure that you are replying in a popped out message pane. We talk about this Pop Out view a lot in Outlook Essentials training, and how it opens up a whole new world of opportunities in the Outlook ribbon. This button lives right above the Send button in the Reply preview pane.

Okay, are you ready for the two steps?

Go to the Format Text Tab
In the Format group, change the selection from Plain Text to HTML by pressing the HTML button.

Go back to your Home and Insert tabs, and you will see that all of your options have returned. Happy day! Heads up though, if your signature is set to automatic, you may need to reinsert your signature to bring back the formatting in the signature.

I hope this tip prevents some frustration for you in the future. No exercise files today, unless you would like me to send you an unformatted email from my phone to test out this setting… if so, you know where to find me!

Excel Text Formulas

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:

Click into cell B2
In the Formulas Tab, Click on the “Text” Function button
From the drop down list, select PROPER.
In the Function Arguments Box, Click in the white space next to text.
Click on Cell A2 (or type A2)
Click OK
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

Exercise File

Scenario

Paste Special: Operations

More Arithmetic Options

Congratulations, Power Users!

Exercise File

Checking Accessibility

The Results

Classifications

More Information About Errors and Warnings

How to Fix

More about Accessibility

Styles

Instructional Design and Access

Congratulations, Power Users!

Exercise File

Transpose: Paste Special

Congratulations, New Power Users!

Apologies to Mac Users

Where is the Journal?

Make a Shortcut

Journal Entries

Searching

Calendar Integration

Thoughts?

The Name Box

Exercise Download

Named Ranges

Formulas with Named Ranges

Managing Named Ranges

Exercise File

The Ribbon

Step 1: Choose a Citation Style

Step 2: Insert Citations

Step 3: Insert a Bibliography or Works Cited

Making Adjustments: Managing Sources

How to Fix

New Power Users: