A wise man once told me to never use an expensive and complicated power tool when a hammer will do the trick.
While certainly more robust than other software of its kind, Excel can also be used for very simple and effective methods of data analysis. And it doesn't seem to be going anywhere anytime soon.
In my full-time position as a data analyst, I use various tools to deliver long- and short-term insight solutions to business stakeholders. It's not uncommon that a number of ad hoc requests hit my inbox for which a tool like Excel is more than sufficient—or even necessary.
The following Excel capabilities have made my life significantly easier and more efficient since learning them. These are basic, easy, and used by many—for good reason. They may not be your cup of tea, and there are many articles, tutorials, etc. out there singing the praises of other Excel functions, so find what works for you and make it happen!
1. Freeze Top Row
The first thing that inevitably has to be done when I do a data pull or receive a dataset is some measure of cleaning the data, or at least making it more analysis-friendly. Sometimes this means all sorts of cleaning measures, but generally—if the data's good—there are just a few aesthetic boxes I check before really getting into it.
If the dataset is a straightforward table with column headings, I bold (Ctrl + B for PC, ⌘ + B for Mac) and freeze the top row. This keeps the my column names clear and always present, regardless of the row I'm on.
The easiest way to freeze the top row is simply to go to the "View" tab, find the "Freeze Panes" section, and select "Freeze Top Row."
For datasets that include an identifier in the first column that you'd like to have visible regardless of the column you're on, "Freeze First Column" is the way to go.
Note: To freeze more than one column or row, click the cell to the left and top of which you'd like to freeze the columns/rows, and select "Freeze Panes" to the left of "Freeze Top Row."
2. Filter Top Row
In addition to freezing the column headers, I add the filter option as well. This is useful for at least two reasons:
I can see the range of unique values I have in any given column
I can quickly sort by or filter any column as needed—by cell value or color, in ascending or descending order (see image—the popup window shown opens when you click on the filter triangle next to the column name)
There are two ways that I use to add the filter option to the column headings. Select anywhere within the data, and then:
1. On the "Home" tab, click on the "Sort and Filter" dropdown button, then select "Filter",
OR
2. On the "Data" tab, simply click "Filter"
3. "IF" Function
This delightfully simple function has many applications. The one that I use most regularly is to assign a binary value (1 or 0) to a value in a column, which I can then use to Sum based on various criteria.
For example, let's say that I have a set of sales data, and I want to know which of the sales were at or above $200. I could filter, sort, COUNTIF, etc., but if I know that I'll be using this criteria for multiple analyses later on, I'd add a column to the right of the Sale Amount column, and use the following:
=IF([Cell with Sale Amount] >= 200, 1, 0)
Effectively, I'm saying that if the Sale Amount is great than or equal to $200, then give me a 1. Otherwise, for all other cases, return a 0.
Alternatively, you could do the opposite and get the same result:
=IF([Cell with Sale Amount] < 200, 0, 1)
This one's a little simpler, but the logic is the same—if the Sale Amount is less than $200, give me a 0. Otherwise, for all other cases, return a 1.
Regardless, I can then easily get the number of sales that meet that criteria, whether in a pivot table or a simple SUM function.
The big thing to remember here is that the last argument (the 0 in the first example and the 1 in the second) indicates the value you want to return if your IF criteria is not true. So make sure you understand your data well enough to be able to make the assumption that these arguments are enough to cover all the logic bases.
If not, you'll end up with 1's and 0's where they aren't supposed to be. For example, let's say that in the Sales Amount column you have a few cells that are blank (the sale was cancelled, for example, and you don't want a $0.00 in there because the sale wasn't $0.00—you didn't give away any free product, etc.). You may not want to include these at all in your 1's or 0's thing.
There are a number of ways to get around those situations, including nesting another IF function and throwing and AND or an OR in there. ANDs and ORs are especially helpful for calculated fields that have more than one factor to consider.
We won't get into all of that now.
The big thing to remember in this and all cases, the business logic and the structure of your data have to be taken into consideration, or you're more likely to make the wrong assumptions about the data, and the results will be less reflective of what's actually going on, none of which helps with business decision-making.
In all cases, the business logic and the structure of your data have to be taken into consideration
One final tip. Using the ("") to produce a blank cell can be extremely useful in the IF function and others. Keep in mind, however, that while the cell appears empty, the reality is that it still contains a function (i.e. it's blank, not empty), and while it will be skipped over in SUM functions, it will be counted in COUNT functions, because COUNT counts all non-empty cells.
4. Cell References
This isn't so much an Excel capability as it is just a general tip:
When possible, refer to other cells rather than entering static data in to the cell or function.
This is especially useful when you're building out a model that you'd like to be at least somewhat dynamic—as you change the data in various locations of your model, the following cells that refer to that data update as well.
Consider the following example, in which I created a (very basic) financial model for going in on an Airbnb with a group of investors, planning to contribute 5% of the estimated total up-front capital required. Referencing the rate, period, down payment, etc. in their own cells allows me to use those cells dynamically in generating the monthly payment, creating the amortization schedule, etc., all of which update automatically when one cell is changed.
By easily adjusting only the necessary cells, I can see whether a potential Airbnb prospect is financially viable for me, depending on my budget, timeline for a desired ROI, cash flows, overall equity, etc. (again, this is a very simple model, not taking into account things like taxes, risk, and the time value of money).
Very handy. And also a lot of fun. If you're nerdy like me and enjoy doing this kind of thing in your free time, I highly recommend it.
5. Select All (Row, Column, Etc.)
Finally, this simple trick will save you so much time. There are a few ways to select a certain array of data, but this is the method I use the most.
To select only the cells in a row that contain data (i.e. not the entire row in the Excel tab, which is done by just clicking on the row number), select the cell at the beginning of the row, then "Ctrl (⌘ on Mac) + Shift + →" to select all the cells to the right that contain data. If you want to select a whole section of rows and columns, just follow up the previous keystrokes with an additional set that includes the up or down arrows. This can be done from any cell in the dataset, and will save you a lot of time dragging and scrolling.
To break this down a little, the Ctrl/⌘ key in combination with the arrow keys allow you to jump to the edges of the data. For example, to jump to the bottom of my 20,000 row table, rather than scrolling I could simply use "Ctrl + ↓" to do so. (Note that it jumps to the last cell that contains data in the column/row, so if you have a break in the data at any point, the jump will stop there and you'll have to continue to use the command again to move past it.)
The Shift key highlights all cells for which you are holding down Shift and moving with the arrow keys. For example, to select the next three cells to the right of the one I've selected, I can use "Shift + → + → +→." You may prefer to drag your cursor instead, but this way you don't have to take your hands from the keyboard.
The use of these keys together combines their powers into one stronger supercommand that makes life that much easier.
Bonus: Double Click to Autofill
One little extra nugget that has been a blessing to me, and therefore I want to share.
If you have a big set of data, particularly one with a large number of rows, you likely don't want to enter the same thing over and over all the way down if you're adding to each row.
Fortunately, if your new column features the same formula or type of data, there is a very fast way to do that without having to click the Autofill button in the menu: Enter the formula/data at the top of the column, put your cursor on the bottom right corner of the box (the cross-section cursor will turn black) and double click.
In the example above, I concatenated the Month Name and Year and then autofilled it all the way down.
Keep in mind that Excel is smart and likes to anticipate your needs. So if you enter "January" into the first cell and nothing into the second, it will autofill the second with February, the third with March, and so on. However, if you want it to autofill with increasing integers (i.e. 1, 2, 3, 4, 5, etc.), then you'll need to enter 1 in the first cell and 2 in the second, or it will autofill with only 1s all the way down.
If you made it this far (or just scrolled to the end to see how much you have to put up with to get to this point), I sincerely hope at least something in this article was useful to you. These tricks of the trade have certainly been my friend over the years, and I hope they serve you as well as they have me. We've only barely scraped the surface in this post, and I look forward to more of these to come!
**Disclaimer: While I certainly feel comfortable using it, I will never claim to know all there is to know about Excel! If you see things that are wrong, could be done better another way, etc. please let me know! I always welcome feedback, and want these posts to be as accurate and helpful as possible.
Additionally, if there are Excel functions, shortcuts, etc. that haven't made the list that you love to use, comment or contact me directly and I'll add them to a sequel post.
Comments