Microsoft Office 365 at WSU will feature biannual updates, so you will start seeing new buttons and features appear in your ribbon periodically (perhaps you have noticed a few already). So, we are testing out something new…
Microsoft PowerUp!
The sessions will be called Microsoft PowerUp!
PowerUp is intended to showcase new features in 365, and will contain new information not covered in previous Microsoft Office sessions.
The sessions will be quick. These are demos, not full training sessions, so you can plan for about an hour.
Two January sessions will be offered: January 8th and 15th.
Sessions are listed in myTraining, so sign up now and we will save you a spot and a free guide!
Working with color themes in Microsoft Office can open up a whole world of possibilities for customizing your documents. Most people don’t realize how much they are already interacting with themes in Office, or how much control they can have with just a couple clicks. Let’s check it out. But first, a special thank you to Sheree for having some excellent color palette questions in an Excel Essentials session a couple weeks ago and inspiring this Byte.
Exercise File
You can follow along on one of your existing documents, or if you would like a starting place, here is a Word document you can start with:
This is a Word document with a few visual elements that incorporate theme colors. These are all things that will be effected by altering the color theme.
Themes and Color Palettes
What does it mean to apply a color theme? In most of your Office programs, you are already using a color theme, whether you realize it or not. The default is the Office color theme. You see your theme colors in everything from the color options for your fonts, to your default headers, to tables and charts… and more.
In the test file you downloaded, you are seeing it in the headers, the chart, the icons and the table…
One of the easiest ways to check your current palette is to visit the Home tab, Font group, and click on the dropdwon arrow next to the Font Color.
Notice how there are Theme colors, and Standard Colors. The Theme colors display your current color palette. There are gradations underneath each main theme color…. lighter and darker versions of each of the theme colors to create contrast.
Change the Color Theme
Changing your color theme is simple!
1. Go to the Design tab, DocumentFormatting group. (By the way, this is also where you can go to change your default font settings for the document).
2. Select the Colors dropdown to see a full list of themes. Hover your mouse over each color palette to see a preview.
3. Select a color theme by clicking on it.
Because theme colors are utilized in so many ways in this document, we really see a change in appearance!
Custom Color Palettes
If you are artistically inclined, you might be interested in creating your own custom color themes.
1. In the Design tab, Document Formatting group, click on the Colors dropdown again, but instead of selecting a predefined color set, select Customize Colors…
2. Click on the dropdown beside any of the accent colors to make an alteration. Typically Accent 1 is the color you will see the most in a document.
3. You have the ability to select colors from a color wheel, or select More Colors to enter an exact RGB color.
4. When you are finished, name the color palette and click Save.
5. The new color set will appear in a new section at the top of the Colors dropdown called Custom.
You will be able to access this color palette anytime you create a new document on this computer.
Accessing Custom Color Themes in other Microsoft Programs
Once you have created a color theme that you like, you may want to access it in other programs. Microsoft knows this, so has made them accessible to you in many of your Office programs. They are in slightly different locations though, so let’s take a look.
PowerPoint
In PowerPoint, you often see color themes even more prominently than in Word. Here is one popular theme called Berlin, that features a red and yellow color set.
1. In PowerPoint, visit the Design tab, Variants group.
2. Select the dropdown arrow in the lower right:
3. Here is where you will see your color theme options, plus the new custom color you just created in Word. Neat!
This definitely changes the look of this document…
Excel
In Excel, Color themes affect features like tables, charts, shapes, and fonts. The default in Excel is the Office color set as well. These colors should look familiar:
1. In Excel, visit the Page Layout tab, Themes group.
2. Select the Colors dropdown.
3. Here is your custom color theme again… no need to reinvent the wheel.
Outlook
Outlook? Did you read that right? Yep! Outlook also has the same themes and color sets you know and love from your other Office programs.
1. Open up a new email.
2. Visit the Options tab, Themes group.
3. Click on the Colors dropdown, and there you are…
Thoughts?
What do you think? Do you think you will utilize custom color themes in your Office documents? By the way, if you create a cool WSU themed color theme, I would love it if you would share it with me!
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
Are you lost in a sea of emails? It might be useful to make certain emails stand out with a special color or formatting. Perhaps a fillable form that comes to your inbox with a specific subject could be green, or all emails from your boss could show up as red. Customizing your inbox with … Continue reading “Outlook: Special View Settings with Conditional Formatting”
Are you lost in a sea of emails? It might be useful to make certain emails stand out with a special color or formatting. Perhaps a fillable form that comes to your inbox with a specific subject could be green, or all emails from your boss could show up as red. Customizing your inbox with conditional formatting can be a huge time saving tool to help draw attention to certain emails in your inbox at glance. Let’s take a look. Before we get started, I would like to thank Michelle for having an excellent question in last week’s Outlook Advanced class that inspired this Byte.
About Conditional Formatting
Just like we experienced in Excel, conditional formatting in Outlook will look for certain conditions and apply a specific appearance to them. Here are a few caveats before we jump in:
This feature is currently only available for the PC desktop application of Outlook (apologies to Mac users).
Conditional Formatting applies to one folder at a time. This means that you can create separate rules for each folder. It also means that you should be cautious of which folder is selected before jumping into the view setting.
Changes you make with this setting are at your computer application level only: it is a view setting within the Outlook desktop application. This means that other instances of Outlook (e.g. the online application, or the desktop application running on an additional computer) on will not adopt these view settings.
If you would like to learn more about Conditional Formatting, please attend one of my Outlook Advanced sessions (listed in myTraining).
Create a Conditional Formatting Rule
1. Start by selecting the folder where you would like to apply the rule. This may just be your Inbox if you are not a big fan of folders.
2. Go to the View tab, Current View group, and select View Settings.
3. Select Conditional Formatting.
Here are all your current Conditional Formatting rules. That’s right, you already have some rules… like the way an unread message uses blue font, for instance… I would not recommend changing existing default rules.
3. On the right select Add.
4. At the bottom of the popup, you will be prompted to name your rule. Use a name that is relevant to you.
From here, there are two settings we have to create: Font and Condition. Just like with Excel, it is easy to get carried away with creating one setting and forget to do the other…
Font Settings
Let’s set the appearance first.
1. To the left of your title, select Font.
2. A popup screen appears with various font options along the top: font type, style and size…
… and of course the funnest part, in the lower left, Color!
3. Make your font and color selections, then press OK once.
Condition Settings
This second part is easy to forget. Sometimes we spend a lot of time crafting the perfect appearance, and then forget to tell Outlook what the Condition is where the appearance should be applied.
1. Below where you selected Font, select Condition.
Take a look at your options… you have the ability to format based on:
Specific words in a subject field
Emails from or to specific people
Situations where you are the only person in the To: line
… and we will see some more examples shortly.
2. For this scenario, I am going to set a condition based on emails From a specific person. Click on the From button to browse the directory. Double click on the person’s name, then press OK.
This option works best if you browse for a name, rather than typing in the full email.
3. Let’s take a look at the More Choices tab, because things get even more interesting here. Not only are more options present here, but we could layer these on top of the previous selection… here are conditions based on:
Category
Only unread or read items
Items with attachments
Importance level
Items that are flagged
So think about this, you could set your formatting to color code emails from a specific person that are unread; or emails that have specific words in the title that also have attachments. So many possibilities!
4. Once you have this set how you would like, press OK. Then OK again to get back to your Outlook folder.
If all went well, you should be able to see the effects of your lovely new rule. I picked purple for mine…
Thoughts?
What do you think about Conditional Formatting in Outlook? Do you think you will apply some of these rules to your Outlook folders? I would love to hear from 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
We all occasionally find ourselves navigating the rough terrain of ugly data. Power Query is an excellent tool to have in your back pocket for this eventuality. But here is the deal, Power Query is just that… powerful, and because of this, potentially complicated. In fact, I know a lot of people who have been interested in learning about this beast, only to get quickly overwhelmed in the complexities. So here is my aim with this Byte: a simple introduction to Power Query. Let’s check it out.
What is Power Query?
Power Query is a feature in the PC version of Excel 2016 or later, also known as Get & Transform. There are a multitude of uses for this feature, but it really shines to take on ugly data like this:
… and transform it into something more workable.
Furthermore, since the result is a query that is connected to your data, it can be updated with a simple click of a button.
Exercise File
If you would like to follow along with my steps below, here is an Exercise File:
You will need the PC desktop version of Excel 2016, 365, or 2019 to use this feature.
For this Byte I am assuming you have worked with Tables and Pivot Tables in Excel before. If you haven’t, please come to my Excel Essentials and Excel Pivot Tables Sessions and learn about them!
Needless to say, this is all fictional data.
The Scenario
The scenario is we have some ugly data that we have exported from a different source. The source has thrown lots of data into one column of a spreadsheet. Monthly, we are asked to create a pivot table of fees owed by class and area code, but this will be rough going, given how the data looks:
Format as a Table
I have mentioned before that formatting your data as a table has many advantages, and here is another excellent example. There are many data sources you can use for Power Query, but let’s start here for now.
1. Click on any cell inside the data. Do not preselect the entire column.
2. In the ribbon, select Format as Table, and select any style.
3. Make sure that all the data is encompassed in your range, make sure My table has headers is checked, and press OK.
More details about tables are discussed in the Excel Essentials training, so check out one of those sessions if you have not already.
Create a Query
1. Go to the Data tab, and in the Get & Transform Data group, select From Table/Range.
(note: you can also access this from the Get Data dropdown if it is not readily visible)
2. You will be taken to the Power Query Editor.
This is a little like an alien abduction from your comfortable Excel home… the look and feel are kind of the same as the rest of Excel, but also kind of different. Power Query uses a different type of code than the rest of Excel, so some things will not be intuitive. I don’t want to get too far in the weeds, but for now note that:
There is no undo, but you can always delete a step on the right,under Applied Steps, where coincidentally, every step of the query will appear forevermore.
There is a ribbon with Transform and Add Column option. Although options will look similar on both tabs, items on the Transform tab will change an existing column, and items on the Add Column tabwill create a new one
A lot of options are also accessible on a right click menu.
Transform the Data
In the query, there is only one column with a lot of ugly data, but I notice most of the data is separated by a colon “:”. Let’s begin by splitting up the columns by this delimiter.
1. Click at the top of the column to select all of the data in the column.
2. In the ribbon, or on a right click menu, select Split Column –> By Delimiter.
3. Make sure Colon is selected, and Each ocurrence of the delimiter is selected. Press OK.
Your data should split into separate columns at each occurrence of a colon.
4. Let’s work on splitting the phone number. Select the phone number column, and Split Column–> By Number of Characters.
5. We want to split after 3 characters, Once, as far left as possible. Click OK.
Cool, now we have a separate column for the area code!
6. At the top of each column, double click on the title and give each column a name: Name, Area, Phone, Grade, Class, Fees.
Load the Transformed Data
1. You have the option to name your query on the right side of the screen, under Query Settings –> Properties. If you will be doing multiple queries, this might not be a bad idea.
2. After this, the final step is to press the Close & Load button in the Home tab.
Like magic! Our data loaded to a new tab in a new table.
Good News Part One: Data is Easier to Work With
Okay, this data is going to be so much easier to work with. Remember, my original goal was to make a pivot table showing fees owed by area code. Now that this data is in a table, in a few clicks, I have exactly the information I need. Beautiful!
Good News Part Two: Updating Data
We could have fixed up that data in a variety of other ways in Excel (hello, Flash Fill!), but the especially cool part about Power Query is the ability to refresh data in the future with one click. Remember I mentioned this was a monthly report I had to create… so here is what I would do next month… rather than recreate all the steps to make the data readable again:
1. Load the new data into the source table.
2. Right click on the query table and select Refresh
All the query steps we did in the previous section will happen automatically with my new data.
More Types of Queries
This example involved a query from a table within a document, but you can query tons of different sources: Excel documents in seperate locations, Access databases, Azure databases, online sources… seriously, check out all the options on the Data tab, Get Data dropdown.
Interested in a Power Query Session?
This was a very basic introduction to Power Query. There is a lot more to cover about this amazing feature. WSU friends, I would like to poll the audience here… I think it would be fun to offer a Power Query Session to delve more deeply into its various abilities, but would like to hear from you all if this is something that would interest you. If you are interested, please send me an email!
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
Charts can be incredibly challenging. They represent where the right brain and left brain meet… where computations and numbers collide with art and color. To be able to convey true meaning with graphics is a very special skill; here are 5 tips to help you along the way.
1. Determine Your Message
Two important questions to ask yourself before you dive in to chart creation:
Who is your audience?
What is your message for them?
Cognitive Overload: Be Kind to Your Audience
One mistake a lot of us make is overloading the audience with too much information. This concept is called cognitive overload. Sometimes we zoom out in an attempt to show lots of data… and end up burying the story we are trying to tell in the process. The audience loses patience, and the entire message is lost.
Consider this unfortunate pie chart… what story was the creator trying to tell? We are going to find some better solutions for this data shortly.
Audience Motivations
Some other important questions to ask are:
What is important to your audience?
What motivates them?
What is their level of knowledge on the topic?
All of these questions should influence the way you determine your message. We are going to revisit this first point frequently throughout this article. Even though it seems like the simplest of concepts, it is often the most forgotten.
2. Select the Right Chart Type
If the picture above gave you minor palpitations, part of the problem may be that someone picked the wrong chart to convey this information. There was a lot of data to comprehend here, more than a pie chart could feasibly tolerate. The chart below uses the exact same data set formatted as a column chart. Is this easier to understand?
Alright, the message is still pretty unclear, but we are moving in the right direction.
Here are a few general rules for selecting chart types:
Pie Charts
Pie charts should contain no more than 5 pieces, and preferably fewer than that.
Pie charts are supposed to show portions of a whole, so the whole should ideally be represented, even if you incorporate grouping (discussed next).
Remember your message and your audience (Tip #1), and then consider: are all your pie pieces all the same size? If so, is this pie chart really showing what you want to tell? Maybe it is… maybe your story is that all things are equally represented. But if that is not your message, consider a bar or column chart.
Consider incorporating data labels and callouts for further clarification.
Bar Charts and Column Charts
Bar and Column Charts are a great option if you have more information to display.
The main difference between bar charts and column charts is that bar charts are composed of horizontal data bars and column charts are composed of vertical data bars.
Both are great options for showing larger numbers of data sets.
Still, beware of cognitive overload with too much information (see Narrow your Focus for more tips below).
Clustered column charts are great for showing quick comparisons between small groups.
Line Charts
Line charts do well comparing two competing data sets over a time period, like monthly sales figures this year stacked up against sales figures last year.
They also can help a viewer quickly ascertain overall trends at a glance.
One tip: when possible, start the Y axis at 0. This should already be the default setting.
So Many More…
There are so many more charts to choose from! Check out this helpful Chart Chooser for more assistance with selecting the right type of chart.
3. Narrow Your Focus
For this section, I would like to revisit our original problem chart from the beginning. Remember, we started with this:
And changed the chart type to a column chart:
…but this is still not an ideal situation.
What story do you want to tell?
Tip #1 will help you narrow your focus for this step. In this case, we had a (fictional) chart of total U.S. Star Trek fans broken down by state. Let’s explore a few stories you may want to tell with this data.
Avoid displaying too much information
Because all the states are represented, this means there are 50 data points. Do we really need to show all of this information? Perhaps, you decide that the story you want to tell is to show the states with the highest rates of Star Trek Fans.
How about removing the states that are not pertinent to your story? Let’s try to narrow our focus by filtering out some unnecessary information (i.e. the lower figures in the data set) with the filter button to the right of the chart.
This leaves us with a more manageable data set, down from 50 points to 7…. let’s keep going.
HighlightImportant Information
Another useful technique to drive home a point is to use contrast to highlight important information, visually pulling it to the foreground. Select any data bar, right click, and select Fill to choose any color in the color wheel.
Let’s make use of this opportunity to pull the highest states to the foreground with a deep color, and grey out the others, pushing them to the background.
Take a look at the same filtered graph, recolored. Maybe the story you want to tell is that Hawaii, Kansas and New York had the highest populations of Star Trek fans… if so, this could be a cool way to do it.
Group information together
We initially started with a pie chart, and there still might be a case for this type of chart with this data. Grouping together pieces of information is another great way to focus in on your overall message.
Perhaps your goal is to spotlight Kansas, and tell the audience that Kansas contains 5% of the country’s Star Trek Fans. Why not group together the states that are less relevant? We can also incorporate the previous highlighting technique.
This is much easier on the eyes than the original pie chart.
Do you see why asking yourself the questions in Tip #1 can put you on the right track for creating a meaningful chart?
4. Choose Words Carefully
Take a look at the chart below. ..
How long did it take you to grasp the meaning of this chart?
Wherever possible, keep the words to a minimum. And when in doubt, refer back to Tip #1 and ask yourself:
Who is your audience?
What is your message?
How much time would you like them to look at your chart, and what would make the biggest impact?
Remember, the point of a chart is to create a visual illustration of data. More words means less visual impact…
5. Don’t Forget About Your Colorblind Friends
Hey, don’t forget about your colorblind friends!
Approximately 8% of the male population and 0.5% of the female population is colorblind or color deficient. This means if 1000 people will be viewing your chart, about 45 of them may not be able to differentiate between certain colors.
You can still use color in your charts… Just remember that you don’t want to use color alone to convey meaning.
This is something we have talked about before, so if you would like to read more information about the use of color in documents, check out my previous article on the subject.
Just remember, this chart may look perfectly clear to you:
… but here is how it looks to someone with the most common type of color blindness:
More Notes and Disclaimers
You probably know this stuff, but just in case…
You have seen example charts throughout this article. All the data used to create the charts is entirely fictional.
Do you want to learn more about how to make charts? Please attend one of my Excel: Pivot Tables, Charts and Pictures sessions listed in myTraining.
Thoughts?
Okay, what do you think Power Users? Do you think you will be able to put some of these tips to use with your data? I would love to hear from 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
Have you ever gone through a list row by row to highlight rows based on one value in the row? Here is one example: you have a list of students, and you want to highlight their entire entry if they have an “A” in your class… so you hand-select an entire row, fill in the desired color, and repeat this process 100 or so times per semester. Never do that again! Let’s check out another versatility of Conditional Formatting: the ability to highlight an entire row based on one value in a specific column.
Before we jump in… big thank you to Samantha and Amanda for inspiring this Byte with this excellent question in (two different) Excel Advanced Formulas sessions!
Exercises
If you would like to follow along, here is a fictional class list to work with. Our goal with this exercise is to make an entire row yellow if a student has an A in the class.
In my Advanced Formulas training we touch on formatting specific cells based on their value with Conditional Formatting, but this will be a bit different… in this case, we would like the entire row to be highlighted based on the value of one cell in the row (the grade).
If you haven’t already, I hope you will consider checking out an Excel Advanced Formulas session… for one, we cover Conditional Formatting in more detail, and for two we talk about concepts like absolute references, which I reference later in this Byte.
Create a New Rule: Crafting a Formula
1. Start by selecting all the data you would like the formatting to apply to: A2 through H77.
2. In the center of the Home tab, select Conditional Formatting, New Rule.
3. In the popup screen, select Use a formula to determine which cells to format.
4. Click into the box underneath the text Format values where this formula is true.
In your data, click on the cell with the first grade, in H2.
Excel will bring in its own language for this cell “$H$2”.
Type an equals sign ( = ) and (with quotes) “A”.
Your formula at this point will look like : =$H$2=”A”
Sidebar: We hit this in more depth in Excel Advanced, but as a reminder…
The “$” means an absolute reference, so a stagnant location in Excel. We see an example of this in class when we try to use autofill to carry down a formula.
The A has to have “” around it, since it is text.
If you want to learn more about these two points, please come to one of my Excel Advanced Formulas trainings.
Okay, we are not done with the formula yet… if we leave the formula like this, it will highlight the entire selection range (A2 through H77) if H2 has an “A” in it… this is not what we had in mind. To correct this, we are going to remove one dollar sign…
5. Since we realized that the row should not remain absolute, let’s delete one dollar sign (absolute reference). We are going to delete the dollar sign in front of the number 2. This will tell Excel that the row is relative; but the column will remain absolute.
Here is what your formula should look like now:
=$H2=”A”
We are not done yet, though… we still have to set up formatting…
Creating a New Rule: Formatting
It is easy to get so caught up in the formula that you forget to set a formatting rule. Right now the formula looks great, but Excel won’t make it look any different until we pick some formatting.
1. Select the Format button in the New Formatting Rule popup underneath where you placed your formula.
2. In the popup that appears, select the Fill tab. Select a color, then press OK.
3. Alright, this looks pretty good! We have a formula and a formatting. Press OK to see the results.
If all goes well, you should have something like this.
To sum up,Excel is looking through column H (which remained absolute with the “$”) for an “A”, and if it finds an “A” there, the entire row is highlighted all the way down (because the “2” was not absolute, thanks to removing the “$”)
Managing Conditional Formatting Rules
A few more things that might be helpful:
If you want to see the conditional formatting rules you have created, click on the Conditional Formatting dropdown, and select Manage Rules.
The default view shows you the rules present for any cells selected. So if you would like to see all the rules for a sheet, you can alter the Show formatting rules for dropdown accordingly.
Here is where you can also make adjustments to your rules: edit, delete, alter the cells it applies to (helpful if you selected too many or too few cells to begin with).
Thoughts?
What do you think, will you try out this type of conditional formatting in 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
In the last Office Byte, we talked about the ability to compare two Excel documents via a special Add In. In the case of Word, we can use a feature already present on the Review tab, and in many ways it is even cooler. We cover this Compare feature in the Word Advanced training… if you would like to learn more about this, I would love to see you at a future session! Let’s take a look.
Exercises
If you would like to follow along, here are a couple of exercises. The first is a fictional draft of a document, and the second is a final version.
Much like with the Excel example, our goal is to ascertain the difference between the two documents.
Also, big thank you to Hannah in HR for letting us use her Final document as an exercise (the draft is a fictional version of her final).
Compare
In Word, the Compare feature lives on the Review tab. This will make sense when you see what happens after we load up both documents.
1. In a blank Word Document, go to the Review tab, Compare group, and click on the Compare dropdown.
2. Select Compare.
3. A popup will appear. Click on the folder to the right of the Original Document and browse to select the draft document. Then click on the folder to the right of Revised document, and select the final version.
4. Notice you have the ability to label the changes. You could list the name of the author of the revised document here. Click OK.
One more note… there is a button that says More at the lower left of this screen. For now, I am going to skip that, but it is worth taking a look as you use this Compare feature more.
Navigating the Compare Screen
Hey look, a mission control screen! What are we looking at here?
Revisions
On the left side you will see Revisions. you can click on any of these to be taken directly to the appropriate area of the document.
Original and Revised Documents
On the right side, you have two panes: the Original and the Revised document.
Combined/Compared Document
In the center are both documents together. As you scroll down in this document, you will see the left and right panes scroll with you.
Already, this “mission control” view (not the official name) is making it much easier to compare the differences between these two documents, and see exactly what my coworker changed. But there are some other cool things about this feature.
Changes: Accepting or Rejecting
I mentioned there is a reason this feature lives on the Review tab. The center document effectively took our original, overlaid the revised version, and is retroactively treating it like comment and markup.
What does this mean? For one, you can accept or reject each of the revisions, just as if your coworker had used markup to change your document. What a cool tool! Especially if your office doesn’t regularly use Track Changes features, and you want to utilize their functionality…
Additional Notes
A couple more things I want to make sure to share:
New Document
When you chose to Compare these two documents, take a look at the top of your Word screen. This created a new document titled Compare Result.
You can save this as its own document. It will not have the mission control experience when you reopen, however it will retain the changes as if it were a document with track changes enabled, and you will be able to come back later and decide to Accept or Reject changes.
Combine
So that was compare… what is this Combine option in the same dropdown? If you selected Combine instead of Compare and ran through the same exercise, you actually wouldn’t see a huge difference.
So a common question is… what is the difference between the two features? I have heard it said that the difference is Compare is only for two documents, and Combine is for multiple documents… this confused me, since I don’t see a way to add more than two in the Combine screen. What I came to learn is that Combine allows for track changes to be turned on on the documents that are being compared… it is basically Compare for two documents with tracked changes already enabled… allowing for more collaborators.
Long story short (too late)… the features behave remarkably similarly, with the one exception that if Track Changes is turned on in either of the documents to be opened, Combine seems to be the method of choice.
Annnd… if you want to learn more about Track Changes, check out Word Essentials training! [/shameless plug]
Thoughts?
So what do you think, do you think you will use Compare with your Word files?
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
Has this happened to you? You create an Excel spreadsheet that you share with your coworker. She edits the document, sends it back to you, and you are left scanning through a long list of entries to try to determine what changes she made…
In our Word Advanced sessions, we talk about the nifty Compare feature in Word. This feature is absent in Excel, but there is a similar option for Excel users via a Microsoft Add In called Spreadsheet Compare. This Add In comes with PC versions of Microsoft Office 2013, 2016, and 365… in other words, you probably already have it…
Before we jump in, thank you so much to Hannah and Jamie for inspiring this Byte!
Exercise Files
If you would like to follow along, below are a couple of Exercise Files. Save these to a place where you can easily find them for the next step (e.g. Desktop).
This is a fictional class list of students. We are going to assume that the FictionalClass download is the original and the FictionalClassEdited is what your coworker altered and sent back to you.
Spreadsheet Compare Add In
To access the Spreadsheet Compare Add In, click on the Windows icon in the lower left of your task bar, and search for Spreadsheet Compare.
You will be taken to a sort of mission control for comparing spreadsheets. You haven’t loaded anything in here yet, so there is not much to see.
Compare Files
1. In the upper left, click on the first button called Compare Files.
2. A pop up screen will appear. Notice it is asking you to select an “older” file and a “newer” file.
3. Click on the folder to the right of each of these fields, and browse to find the FictionalClass (older) and the FictionalClassEdited (newer) that you downloaded from the exercise files above.
4. The two workbooks will load into the left (older) and right (newer) panes. Notice that changed cells are highlighted in green. Click on one of these cells, and it will select the corresponding cell in the other sheet.
5. Look at the bottom of the screen for an outline of the various changes that were made. This list notes all sorts of changes that were made, including the contents of cells and the formatting of cells. You can also click on any of these to be taken to the change in the worksheet on the panes above.
6. Try double clicking on one of the changes highlighted in the bottom pane. You can also access this feature by clicking on Show Details in the ribbon.
It is a little disorienting. This popup screen is showing you what was in the original document, and the change that was made in the second sheet, in kind of a strange format. Simply click on the X in the upper right to go back to the main Spreadsheet Compare screen.
Export Results
It is possible to export all the results from this comparison. in the Export group is a button called Export Results.
This will create an Excel file of all the differences between the two workbooks.
Drawbacks
While this is a nifty way to quickly see changes between two Excel files, it lacks some of the handiness of the Word Compare feature…
You can’t accept or reject the alterations/differences, you can only see them displayed and highlighted. Any desired changes will still have to be made by hand in the source document.
There is not a way to save a combined document containing both the original and changed document.
Other than that, a pretty cool tool!
Thoughts?
Do you think you will find a use for Spreadsheet Compare? Do tell!
100th Power User Certificate Awarded!
This is a most exciting week in Power User history! We have a three way tie for 100th WSU Microsoft Office Power User. Big congratulations to:
Tiffany Morgan
Matt Stiles
Lauren Wilson
For more information about the WSU Microsoft Office Power User Program, visit wichita.edu/poweruser
Have you ever attempted to mail merge with fields that contain formatted numbers, only to find that the formatting does not carry over onto your merged Word document? This is a common source of frustration with mail mergers, and something that we can quickly remedy on the Excel end. Let’s take a look… but first, big thank you to Jamie for having this excellent question at Wednesday’s Excel Essentials session and inspiring this Byte.
Exercise Files
If you would like to follow along, download today’s exercise files below to your desktop.
For this Byte, I am assuming you know the basics of working with Mail Merge in Word. If you are not comfortable with Mail Merge, please attend a Word Essentials training with me! I would love to show you how it works.
Excel File
Let’s take a look at the Excel file first. This is a list of employees… here are all out new professors who we are going to send letters to, welcoming them to Starfleet.
There are columns with Last Name, First Name, Salary, FTE, and Phone Number. The number fields are all formatted: Salary as currency, FTE as rounded to two decimals, and Phone Number as the Special Phone Number format.
Heads up: Notice that, highlighted in yellow, there are also some “helper columns” with TEXT titles. Let’s ignore those for the present, because in real life, perhaps you haven’t created those, and instead incorporated number formatting, as most of us do in our Excel Files
Word File
Opening up the Word File, you will find a letter welcoming professors to Starfleet Academy. Our plan is to enter merge fields into the bold area circled below.
Mail Merge with Formatted Number Columns
Let’s start our merge. We are going to build this using our number formatted columns. Again, I am assuming you have merged before for this exercise. If you are not comfortable with Mail Merge, please come attend one of my Word Essentials session. I would love to show you!
1. In the Mailings tab, go to Start Mail Merge, and select Letters.
2. For Select Recipients, select Use an Existing List. Browse for the Excel file, wherever you chose to save it.
3. Take a look at the Insert Merge Field dropdown, and make sure that you see all the columns you saw in your Excel file. Remember, we are focusing on the first five merge fields for now (not the TEXT ones).
4. Insert each merge field in the indicated location. It should look like the example below.
5. Let’s toggle the Preview Results button to see how this will look.
This is usually the point where people realize that certain types of number formatting do not come through in Mail Merge. The salary, phone number, and FTE are just general number formatting. The phone number has no dashes, the salary has no commas, and the FTE is missing a “0.”
6. Let’s remove the Phone, Salary and FTE merge fields, and try this again. This time, insert the TEXT options that we saw in our “helper” columns in the Excel file.
So Salary should be replaced with TEXTSal, FTE with TEXTfte and Phone with TEXTPh.
7. When you Preview results, it will look much more like what we had in mind.
What difference! Let’s see what makes these columns different in our Excel file.
Text Formulas
Open the Excel file again, and let’s do some investigating. We are going to check out our “helper” columns in columns F, G, and H that contain TEXT in the header.
1. Let’s see how the formula for TEXTSal is set up. Click into cell F2, and take a look in the formula bar. The formula used for Sal was:
=TEXT(C2,”##,##0″)
2. Do the same for TEXTfte in G2. The formula used was:
=TEXT(D2,”#0.00″)
3. … and finally, TEXTPh in H2. The formula used was:
=TEXT(E2,”[<=9999999]###-####;(###)###-####”)
So our investigating reveals that TEXT formulas have been used in these columns, which came over in a much better format for mail merge purposes.
If you would like to learn more about the TEXT function, take a look at this handy guide from Microsoft that will walk you through creating all of these used above, and more!
Thoughts?
Has this ever happened to you when you were mail merging with numbers? Do you think you will incorporate TEXT functions with your future merges?
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
Search Folders often elicit an “aha!” moment for attendees in Outlook Advanced training. Many people discover these handy folders can take the place of their existing complicated folder rules. Let’s see how these special folders work.
Exercise File
Hey there is no exercise file for you today! Just open up your Outlook and follow along.
Oh, and if you are a Mac user and think this won’t apply to you, think again! I have a solution for you toward the end.
PC Users: Create a Search Folder
On the left side of your Outlook mail, you likely have all sorts of folders. Scroll all the way down to the bottom, and you will find Search Folders
1. Right click on top of Search Folder and select New Search Folder.
2. A popup screen will appear with a plethora of options. Lets start with one toward the top: Mail Flagged for Follow Up.Select this option and press OK.
3. A new folder appears on the left side of your screen under Search Folders. Click on it, and here is a shortcut to all the mail you have flagged for follow up.
Don’t have any messages flagged for follow up? No problem, we are going to create some more folders.
Create Another Search Folder
1. Follow step 1 above, and this time select Mail from and to specific people.
2. Toward the bottom of the popup, press Choose to search for a person.
3. You will be taken to the old familiar search screen (blurred out here for WSU employees’ privacy). In the upper left, type the last name of someone who emails you often.
4. When you find their name on the list, select it, and press OK.
5. Now you have a folder of all the emails in your inbox from this person.
More options
It is worth your time to look through all the Search Folder options. Popular Search Folders include:
Unread mail
Mail flagged for follow up
Important mail
Mail from specific people
Categorized mail
Mail with attachments
Mail with specific words (think about form results that come to you with the same title)
Custom Search Folder: if all else fails, you can completely design your own
Since most of us have a lot of activity on the left side of our Outlook screen, if you want to draw attention to your new Search Folder, you can add it to favorites by right clicking on it.
In this way, and a couple other ways, Search Folders are going to behave very much like other folders. But there are some important differences.
A Word About Search Folders
Since they behave similar to regular folders, it is easy to forget that Search Folders are a different type of animal.
A shortcut to search
A search folder is a shortcut to search, nothing more. It is a window into what could have been an advanced search. This is important for several reasons:
The mail in the folders still only exist in your inbox, not a separate folder within the inbox.
Deleting a search folder will not delete the contents, just the search window.
If you delete a search folder, you will even receive a message that tells you as much.
Note: be careful when you do so that you are selecting Delete Folder, not Delete All.
Mac Users: Smart Folders
Mac users, it is finally your turn! While you don’t have Search Folders, you have something arguably cooler. They are called Smart Folders.
1. Start by clicking into the Search bar in the upper right. This will activate the contextual Search tab.
2. Click on the Search tab, and create a customized search: perhaps email from a specific person, then plugging in their name underneath.
3. In the upper right of the Search tab, press Save Search.
4. A new folder is created in your Smart Folders, all you have left to do is name it!
In a lot of ways, the Mac Smart Folders are more user friendly than the PC Search Folders, but they both accomplish the same task: they give you a very special window into a search of your inbox.
Thoughts?
What do you think? Will you find a use for Search Folders or Smart Folders? Will this eliminate the need for some of your folder rules?
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
