picture of laptop with Excel charts on screen

Top Excel Features Every Freelancer Needs to Know

February 11, 2021 thefreelancerslife 2859 Views

If you’re a freelancer, chances are you’re storing some of your data in Microsoft Excel. You may also have various programs that track different things for you, but there will be times when those programs don’t give you the information you need about your data. So, you export that data and bring it into an Excel spreadsheet. But are you taking advantage of all the top Excel features and what they can do for you to highlight important facts within your data?

There are several top Excel features that you really should know. These top features bring out the power of Excel and are easier to use than you might think.

In this article, I’ll show you a little bit about:

Visualizing data with Charts.
Summarizing data with Pivot Tables.
And finding data quickly with Lookup Functions.

Visualizing Data with Excel Charts

Charts can make it easier to see facts that are hidden within your data. It’s not very easy to gain insight about sales, for instance, from a list of numbers.

A chart can take data like this:

*Figure 1 – Data for Sales by Region for five years.*

And transform it into useful information like this:

*Figure 2 – Chart that represents Sales data for five years.*

Using a chart like this, you can see details in your data that you couldn’t easily see in the raw numbers, such as:

The year with the highest overall sales was 2017.
In 2020, the North and West regions had the majority of the sales, the East and West regions had minimal sales.
From year to year, sales have been going up and down in alternating years.

With this information, you can question why certain things are happening, good or bad, and you’ll have the details you need to make improvements in your business.

Now, this is just one quick example, but I’m sure you can see how useful charts can be for visualizing your data.

Summarizing Data with Excel Pivot Tables

While charts are excellent for visualizing your data, pivot tables are great for summarizing it. If you have a long list of sales figures, you can use a pivot table to summarize those sales figures. You just need a column with repeating values in the data so that you have something on which to aggregate it.

You might have sales data in a long list like this:

*Figure 3 – Sales data for five years in a simple list.*

The data shows each Region’s sales for each of the five years in the reporting period. It’s hard to see any useful information from this data. With a pivot table, you could summarize your data by Region or Year to show you the total sales for each Region or Year respectively.

The pivot table below shows the total sales for each Region:

*Figure 4 – Pivot table showing data from list summarized by Region with total sales for each Region.*

And, this pivot table shows the total sales for each Year:

*Figure 5 – Pivot table showing data from list summarized by Year with total sales for each Year.*

These two pivot tables give you a better understanding of useful information contained within the data and can help you answer some vital questions like:

Which Region had the highest sales overall?
Which Year were sales the lowest?

One other great thing about pivot tables is that you can create something called a Pivot Chart from them. A Pivot Chart is a chart that uses a pivot table’s data as its data source.

So, your pivot table that showed Sales data by year could be used as the data source to create a pivot chart like this:

*Figure 6 – Pivot Chart created from pivot table Sales Data by Year.*

This chart shows an unfortunate trend in your sales figures. As the red trendline in the chart indicates, your sales are going down over time. While this isn’t good news, at least at this point, you know that it’s happening, and now you can take some action.

Finding Data with Excel Lookup Functions

So, charts and pivot tables can help highlight useful information in your data. But what if you had a lot of data and you wanted to find a specific value contained within it? This is where Excel’s built-in lookup functions come into play.

The most popular lookup function in Excel is the VLOOKUP function. You supply the function with four pieces of information (arguments):

The value you want to search for.
The range of cells to look through.
The column from the range that contains the value you want to return.
And whether or not you want to do an approximate match for the value you’re looking for.

For example, if you had this list of sales data for cities in Arkansas in cells A4 through B33 of your worksheet:

*Figure 7 – List of Sales data for Arkansas by City.*

You could use a VLOOKUP function to return the Sales (in Millions) value for a specific city. In cell D4, you would enter the city name, and in cell E4, you would enter the VLOOKUP function.

Here’s an example of what it might look like in Excel:

*Figure 8 – The VLOOKUP function searched for North Little Rock and returned its Sales value.*

And here’s the formula you would write in cell E4 using the VLOOKUP function:

=VLOOKUP(D4, A4:B33, 2, FALSE)

The four arguments in the VLOOKUP function are:

D4 is the cell reference of the city name you want to look for.
(you could type the city name in the formula instead of using the cell reference, but using a cell reference makes it easier to change the formula in the future, and you can see which city the sales value represents without looking at the formula)
A4:B33 is the range of cells you want Excel to search through to find a city and return a sales value.
2 is the column number within the data range to return the data from; this would be column B, where the Sales values are contained.
FALSE tells Excel to look for an Exact Match of the city.

Looking at the list of data again, you can see that the VLOOKUP function returned the correct Sales amount for North Little Rock, $86,000:

*Figure 9 – Highlighting North Little Rock’s Sales figure, which was returned from the VLOOKUP function.*

Once you have the lookup function in cell E4, you can simply change the city name in cell D4 to return that city’s Sales value. So, if you changed the city to Helena, you’d get Helena’s Sales value, as you can see below:

*Figure 10 – Updating the City changes the result of the VLOOKUP function to the Sales value for Helena.*

In cell D4, you can simply type the new city name, or, if you want to get really fancy, you could add a data validation list to the cell, and then you would just select the city from the dropdown list in the cell.

Conclusion

The top Excel features I’ve shown you in this article really bring out some of the power of Excel. If you’re not using all of these features, I hope you can now see how they can help you glean useful information from your data.

Are you already using all of these features? If you are, which one is your favorite and why? If not, which ones are you excited to try out?

Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.