Although many of Microsoft’s users often stick to making documents in Word or creating presentations in PowerPoint, there are many users who are now using or learning how to use Excel, the tech giant’s program that helps chart and keep track of data such as statistics and numbers on different charts known as “spreadsheets”.
It can be really challenging for new computer users and even veterans of technology to learn how to use this program, especially if they’ve never heard of or even used spreadsheets in any form up until this point. It might be challenging, but this article should alleviate some of that stress — the aim is to make that experience at least a little easier. Here are 5 tips that will help anyone pick up and learn how to use Excel like a pro in almost no time at all.
- Use Operations to Automate the Data
- Pivot your Way to Success with Pivot Tables
- Color-code Data with Conditional Formatting
- Using Signs to Make Cells Similar
- Looking Across Sheets with VLOOKUP
“The global economy is built on two things: the internal combustion engine and Microsoft Excel. Never forget this.”
— Kevin Hector, Twitter
Use Operations to Automate the Data
Although Excel can keep track of statistics and data, it can also perform mathematical operations if given operations that will calculate specific numbers. One of the best ways to use Excel like a pro is to automate parts of your spreadsheet to calculate specific numbers. Let’s say that you’re going to be calculating a lot of data that will require the same formulas to be used over and over again.
Normally, manually calculating all those numbers with the same operations over and over would be a repetitive and mundane task that would waste a lot of time — but thankfully, there’s a way to automate this process with Excel. Making numbers calculate automatically after plugging them into each cell is as simple as using one of Excel’s special functions.
For example, if you need Excel to calculate the sum of a certain amount of numbers and spit out the result into another cell, you would type a SUM function into the cell you want the result to be filed under (i.e. =SUM(A5:A45)). There are also other functions such as the function to average the numbers in a list of cells (=AVERAGE(A5:A45)), functions to see if a certain variable is present in a list of cells (=COUNTIF(B2:B10), “dogs”), and so on.
Making creative use of these functions will allow you to completely automate parts of your datasheet as you log and calculate numbers, which will speed up the process immensely.
Pivot Your Way to Success with Pivot Tables
Another useful function that can help you automate parts of your spreadsheet is learning how to make use of pivot tables. Pivot tables are a special feature in Excel that have the capability of effectively organizing and making sense of data, which is especially helpful if you have more data to document or an especially long spreadsheet.
To create a pivot table, click the Insert dropdown menu and click Pivot Table. Older versions of Excel will require you to click on the Data dropdown menu and click Pivot Table. Excel will then automatically populate a Pivot Table for you with all your data, but at this point you can filter and sort your data if necessary. There are four options for handling the data within the pivot table: Report Filter (which allows you to look at certain rows in your data set if you are looking for a specific category of data), Column Labels (the headers in your data set), Row Labels (the rows in your dataset) and Value (which, regardless of prior calculations you may have run in the data, will always run specific values based on whatever you ask of the pivot table).
The pivot tables can be especially useful with larger sets of data, so if you’re looking for a specific category, consider making use of a pivot table to sort your data and effectively categorize something that otherwise might be cluttered.
Color-Code Data with Conditional Formatting
Another helpful tip is to color-code your cells based on what type of data is in them. If you have a lot of data, color-coding them much the same way that you would highlight important sections in a book or an important piece of writing can help you distinguish between different pieces of data.
Conditional formatting is the best way to accomplish this. To create conditional formatting on cells, highlight the group of cells that you are going to color-code, then choose Conditional Formatting from the Home menu and select your logic from the dropdown. The menu will ask you to provide information about your formatting rule from a few preset options, but you can also define your own rule if you want — this can be anything from the top ten percent of sales, a certain group of dogs or anything in between.
A window will then pop up that will prompt you to provide more information about your formatting rule. Select OK when you’re done, and you should see your results automatically appear over the cells that you highlighted. Having conditional formatting overtop of certain cells will help you distinguish the visual difference between different types of data.
“It’s Excel’s world, we just live in it.”
— jgalt212 on HackerNews
Using Signs to Make Cells Similar
If you’re working on a pretty big data sheet, chances are good that you’re going to be moving around a lot of data or will want to change around the formula of some of your data from time to time. If you think that you might need to change around the data, dragging around where the cells are isn’t particularly hard to do: all you have to do is click the bottom of the cell and drag it to another spot.
However, if you do this, the cell’s formula for calculating its data will be changed, and this might make you lose the number that you previously had calculated. This can be really troubling if you’re in a rush, and losing your data isn’t something that you want to have happen to your data especially if you have a lot of formulas for different cells. Thankfully, there’s a solution to this issue — by adding dollar signs to the text inside your cell, you can tell Excel to keep the formula the same if you need the data in the data that is being calculated in the formula to be moved elsewhere.
This can be done by changing the formula in a specific cell to an absolute formula by adding dollar signs. For example, let’s say one of your cells has the formula (=D5 + D11) in it. By changing it to (=D$5$ + D$11$), it will ensure that any other columns or rows that use this formula will draw from the same source even if the rows or columns are in different places besides row D or column D. This is perfect for if you need several rows of data to use the same formula.
Looking Across Sheets with VLOOKUP
For many users of Excel, they may be working with at least a couple of sheets and may need to pull data from several different sheets while also charting different formulas or locating different cells on other sheets. If you need to cross-reference while creating sheets of data, using the function VLOOKUP to cross-reference different data points on different sheets can be very helpful.
If you have one column that is identical on both sheets, VLOOKUP can replicate that data on both sheets. The formula to use this on a cell is =VLOOKUP(lookup value, table array, column number, approximate match (TRUE) or Exact match (FALSE)). This function will make it so that the data is pulled from values from the second sheet that are to the right of the column containing your identical data — in other words, to the right of the column you have requested in VLOOKUP.
Though it can lead to limitations, it works very well for if you need to carry different data across different sheets and therefore you can replicate data to be automated in your sheets.
With these five tips, you’ll be using Excel to automate and get data across your sheets in almost no time and create sheets that are worthy of presenting all the quarter results in the office.
© 2022 Liz Fe