Chances are you’ve probably used a Microsoft Office product at some point in your life, but when you get to working a full-time job, you’d be surprised how complex using these programs gets. Suddenly your boss is talking about things like Mail Merge, functions and filters, and you have no idea what it means.
Don’t fear, recent grads, we’ve got your covered: I’ve constructed a guide to the top three Microsoft Office programs you’ll likely see at your job (Word, Excel and PowerPoint) and the helpful resources, tips and tricks that’ll make you seem like a pro as soon as you walk through the door.
Ah, Microsoft Word. You type, fix your spacing and add some page numbers, right? To an extent, sure. However, there are a few underutilized features that will make your life easier and are key when collaborating on the same document.
Styles and Table of Contents
Styles can be used to make a document look cohesive and to put together a table of contents super easily (even if Microsoft makes it sound pretty hard in this article). All you have to do is highlight your section titles and assign them the appropriate heading or subheading style. Go to the Reference tab to insert your Table of Contents, and voila! Done.
Additionally, you’re not limited to styles that Microsoft Word has determined for you. To edit a style or create your own, right click on the style and click “Modify.” You can also go the Design tab and choose from multiple preset themes that will change the formatting of all your headings.
If you have to, for example, print 500 letters with everything the same except for the name of the recipient, be confident in knowing that there is an alternative to copying and pasting 500 times and going back to change the names. Mail Merge, though often associated with sending out multiple emails with different names to different email addresses, can also be used within Word to make multiple copies of the same document to print.
Say someone sends you a document to look over and edit for spelling, grammar and style. Professional etiquette demands the use of Track Changes (in the Review tab) in order to highlight the changes you’ve made so that the person you ultimately send it back to doesn’t have to have two versions of the document open to compare them side by side.
If you’re asked to put together any type of report, chances are you’re going to start off in Excel. Unless you studied something quantitative, it’s understandable that you wouldn’t spend so much time in Excel, and it can be intimidating.
The thing to know about using Excel in the workplace is that unless you’re some type of statistician, you’ll probably only need to know a few ways to manipulate or summarize data, which you’ll spend 20% of your time doing, and the other 80% is formatting it to make it look nice. Here’s a breakdown of the basic concepts you need to know.
Functions are used within a cell to either calculate or manipulate data. You know, things like SUM, PRODUCT, AVERAGE, and COUNT. These can get complicated, especially if you’re doing multiple calculations at a time, so don’t forget your order of operations.
Additionally, there are functions like VLOOKUP, CONCATENATE, and TODAY that will make your life so much easier but don’t come to mind naturally. Also, IF statements are really important pieces of logic to understand how to use to tell Excel, “If this cell says x, use the space in this cell to spit back y; otherwise spit back z.”
Go ahead, click on those tabs up top; they won’t bite! Up here (A/N: the row of tabs and all the stuff in them is called the Ribbon) is a veritable treasure chest of tools that Excel has already built in. Some important ones to know:
- Pivot Tables (in the Data tab): A Pivot Table is a user-friendly way of summarizing large sets of data that might be downloaded from a payment system or an inventory tracking system. Pivot tables are super handy because they allow you to quickly choose the fields you want to compare against each other using the Row and Column Fields and summarize data using sums, averages and counts in ways that can be further manipulated to display as percents. It sounds complicated, but mastering Pivot Tables will save you tons of time and make you a star in the workplace!
- Evaluate Formula (in the Formulas tab): What happens when your super complicated formula is broken and you can’t figure out where you went wrong? Luckily, Excel includes something called Evaluate Formula that you can use to figure out exactly where your nested formula went wrong. (“Nested” means putting one formula inside of another, like Russian nesting dolls).
- Filters (in the Data tab): Highlight a list of data and press Filter. You can now not only sort alphabetically or numerically, but also limit what you’re seeing to whatever you want. If you’re filtering a long list of dates and don’t want to click on a month’s worth of individual dates, you can use “Date Filters” in the menu when you click the dropdown to narrow in on a range of dates. This works on numbers, too! Also, if you (or someone else) previously highlighted a few items to take a closer look at, you can filter by color to only see those cells that are highlighted.
- Text to Columns (in the Data tab): Did you download a .csv file and it came back looking;like;this;with;semicolons;instead;of;new;columns? Do you need to separate the airline and flight number of a bunch of flight codes? Enter Text to Columns, the answer to literally hours of mind-numbing copying and pasting. Text to Columns will ask you if you want to split up the text in your cell based on fixed width or a delimiter. Flight codes are 2 characters for the airline followed by a number, so you would use fixed width to tell Excel to split up your text after 2 characters. The CSV in .csv stands for “Comma-Separated Values” but it’s not always separated by commas, confusingly. In the above example, the delimiter you would identify is a semicolon, essentially telling Excel, alright every time you see a semicolon, put everything after it in the next column.
- Freeze Panes (in the View tab): When you’ve scrolled so far you’ve forgotten what the fields you’re looking at even are, you need two things: 1) coffee 2) Freeze Panes! Use this to keep your top row (or leftmost column, or whatever you want, really) in place while you scroll to your heart’s delight.
Now that you have the calculations you want, you need to make it presentable! A lot can be done using colors and text formatters (bold, italic, etc) on the home tab, but some parts need further elaboration.
I’ll be the first to admit that Excel charts can be infuriating when Excel can’t read your mind about what you want your chart to look like. It helps to figure out some details beforehand:
- What kind of chart should you be using? Depending on what you’re trying to portray, you should find the correct chart to suit your purpose. This flowchart from The Extreme Presentation Method is a good start, and this paper from Tableau, an industry leader in data visualization (yes, this is an industry) goes further in depth.
- What fields are you plotting? How should you split up your data to make the most effective point? In my experience, the best and easiest way to do this is to make a Pivot Chart (the prettier cousin of the Pivot Table) and drag and drop rows and columns until you’ve found what you want. You can either just use this Pivot Chart or recreate it using a regular chart (regular charts are more flexible on the formatting).
After you determine these two, you should go ahead and make your chart. You can add or take away elements like data labels or trend lines by clicking on the Design tab that appears when you’ve clicked on your chart and clicking on “Add Chart Element.”
Conditional formatting: Looking at lots of numbers is generally unhelpful and occasionally nauseating, so when you add colors to direct the viewer’s eye to what’s important or out of the ordinary, it’s immensely helpful. Rather than go through each cell and select just the right shade of green for each cell (and then inevitably have to go back and change it), you can use conditional formatting to do your highlighting with the click of a button and keep it dynamic based on revised calculations.
I highly recommend learning keyboard shortcuts to be able to navigate Excel more easily (and seem like an Excel wizard!). I won’t bother with Googling “Excel shortcuts” for you, but here are some absolute essentials [Mac options in brackets]:
- Ctrl/[Command] + Shift + [arrow key]: Highlight entire columns or rows of adjacent cell
- Ctrl/[Command] + Shift + v: Paste Value. If you’re copying from a formula and only want the value, not the formula, this is the shortcut to just get the number
- Ctrl/[Command] + Shift + t: Paste Format. Like I said, you’re going to spend a lot of time formatting!! To do this more efficiently, use this shortcut to copy and paste only the formatting from one range of cells to another.
- Ctrl/[Command] + PgUp/PgDn: Scroll quickly in between tabs of an Excel workbook.
With these tips at your disposal, you’re well on your way to becoming an Excel guru!
When it comes to PowerPoint, the golden rule is less is more. In a professional setting, under no circumstances would you want the design of your presentation to overpower the content.
To that end, here are some tips to make sure that happens:
- Use the Slide Master. In the View tab, you can find this essential feature that will help keep your slides consistent and looking professional
- Use the Align tool. Objects that are almost aligned can be very distracting. The Align tool (in the Home tab) is a great trick to make sure your shapes and objects fall in line.
- Keep the context of who is viewing your slides and how they’re viewing them in mind when building. There are three main types of PowerPoint presentations you can make:
- Presentation: Intended to be presented to one or more people, with heavy emphasis on diagrams and short phrases so that the audience pays more attention to the presenter rather than trying to read the slide. Can be creative more so than informative since it’s meant to be presented, and can have a higher number of slides if needed.
- Deck: Designed to stand alone as a summary of a would-be presentation or a presentation that happened in the past. People should be able to read through it and understand all the main points of the presentation without having attended it.
- Placemat: A one-slide detailed summary of the highlights of the idea being presented, not dissimilar to an infographic. Sometimes used during meetings but meant to be easily distributed without needing additional context or explanation.
- Make your presentation color-blind friendly if it’s very visual. It can make or break your presentation for some people in the audience. Some tips here.
Armed with all these tips, there’s nothing Microsoft-related you boss can throw your way and faze you. You’ll probably be teaching your co-workers a thing or two instead of the other way around.