Employers today expect their professionals to be proficient in Microsoft Office apps, including Excel. Excel is a powerful spreadsheet program with the capability to organize data, make calculations, visualize information, and automate calculations. If you just use Excel to put information into neat and orderly tables, you’re missing out on the most powerful capabilities. Here are some Excel skills you should learn to bring your credentials to the next level.

Visualizing Data

Excel spreadsheets can fill up your screen quickly and the amount of data can become overwhelming without the right tools to find what you’re looking for and understand what the data represents.

Filtering

Filtering your data to display only what you need to look at is critical when working with large spreadsheets. Fortunately, doing so is pretty simple. Just click on a column header, and select Data > Filter. You’ll see a drop-down arrow on that column to sort by ascending or descending values. You can also filter the table so you only see rows with one or more of the cell values in that column or set other filtering options to determine which rows to display.

Charts and Graphs

Charts and graphs offer a way to visualize data in order to highlight trends, understand relationships, or summarize the big picture. They make it easy to understand data at a single glance. If you’re giving a presentation or writing a paper, a chart is much more powerful than a table of raw data!

Inserting a chart is quite easy (click here for instructions), but making it look just right takes some practice. You need to be thoughtful when you pick the chart format. Here are some guidelines for the three most popular types of charts:

• Pie charts – Best for showing the proportion or percentage of each category (e.g., club members in each year of school). If you have more than 6 categories, consider another chart type or the segments will be too small to see.
• Bar graphs – Best for comparing categories in a discreet interval (e.g., number of applicants to each school this year). The bars help you easily compare one category to another.
• Line Graphs - Best for showing how data changes over a span of time. Multiple lines show the relationship between categories. For example, sales history and popularity of several products.

Create multiple types of charts for the same data, so you can see how well each type shows the relationship or trend you are trying highlight.

Calculating Data with Functions

Excel lets you enter a basic mathematical equation. For example, if column A is quantity and column B lists the unit price, you could find the total cost by just entering “=A1*B1” in the next cell. Functions in Excel go a step further than basic math. They are predefined formulas that perform more complex mathematical, statistical, or logical calculations. These functions help eliminate a lot of tedious calculations. You can manually type out a function, but the easiest way is to click on the Function (f_x) symbol on the left side of the Formula Bar to open the Function Arguments window, which guides you through the process of selecting a function, identifying each of the factors needed, and writing the function in the correct syntax. Here are some of the most popular Excel functions:

SUM

The SUM function adds the values in multiple cells. For example, if you are tracking the number of people who attended each training class, you could use SUM to find the total number of people who attended training throughout the semester. When using SUM, you can highlight a range of consecutive cells across rows or columns to add together, or press CRTL/CMD and select non-consecutive cells individually. The Autosum button (Σ) will automatically add all the numbers in a row or column (depending on where you place it). Be aware that Autosum can be tripped up by blank cells, so be sure to validate the range it selects.

COUNT

The Count function counts how many cells within a range have a number in them. For example, a business tracks the total order amount for each customer for each day of the month. You could use Count to see how many orders were received that month by counting how many cells had a sales amount listed.

There are several variations of the COUNT function:

• Count only the cells with a specific value.
• Count cells that have text in them (rather than numbers) using COUNTA.
• Count only cells that meet specific criteria using COUNTIF.

VLOOKUP

VLOOKUP is a powerful function that lets you search for something specific in a spreadsheet, and gives you some other piece of information in the same row. For example, you have a list of usernames and you want to determine their major by looking it up in the master list of enrolled students. This is especially helpful when you have a large reference spreadsheet, and then have a long list of data for which you need to look up information. It would take a long time to look them up individually, and the risk of making an error when transferring the data is high. The syntax of VLOOKUP is a little complicated. But it has four basic parts: what to look for, where to look for it, what info it should give you back, and whether it should look for an exact or approximate match.

IF

The IF function is extremely useful when you need to automate decision-making. It will return one value if the condition is true, and a different value if the condition is false. It seems simple enough, but is so much faster than looking through all the rows manually. For example, a sales company wants to give their employees a bonus if they made over $50,000 in a quarter. You add up their sales and create a column where IF will indicate that they should get a bonus only if their total sales exceed $50,000.

Support

There are many resources to help enhance your Excel skills. Microsoft’s Excel Support site covers all the bases, with articles and videos on just about any topic you can think of. You can also take advantage of Linkedin Learning and take courses like Excel Essential Training and Excel: Advanced Formula and Functions. There are loads of other courses you can explore that teach you about specific functions or quick tips.  

If you want in-person help with Excel, don’t be afraid to reach out to our Tech Ambassadors for an in-person presentation. They’re students who have learned all the ins and outs of tech and are here to help you achieve your goals. You can also contact the Technology Help Desk with thorny questions

--By Vivian Zauhar, PITT IT Student Blogger