It’s that time of the year again, we are all scrambling to close out 2018 while planning for 2019. There’s a lot of new technology and tools to help, but most of us still resort to the tried and true – Microsoft Excel. If you’re not a pro with formulas, and auto fill – then read on! You’ll save hours of time and make fewer mistakes.
Excel Formulas
Don’t be scared of Excel formulas. They are your best friend and hundreds to choose from. To get started, Microsoft has a great overview of Excel formulas.
In addition to a great overview, there are videos and examples on:
- VLOOKUP – find things in a table or range
- SUM – adding values
- COUNTIF – count if the cell meets your criteria
- IF – make comparisons
- SUMIF – add the values in a range that meet your criteria
- And more
A couple of my favorite formulas for more complex data manipulation are VLOOKUP() and SUMIF(). VLOOKUP is great when I am trying to match data or I’m looking for data on other Excel sheets to put on a report. I use SUMIF a lot too. I can easily show totals for sales people, products and more on one sheet.
Understanding relative cell references is an important element of formulas. In a nutshell, when you create a formula, the cell references are relative to the location, meaning, the formula will update if you copy and paste (or use the auto fill handle) it to a new location. Here’s an example:
Auto Fill
The auto fill feature has been around since 2003, but the question is, how often do you use it?
Auto Fill will fill cells with data that follows a pattern or that is based on data in other cells, no more entering data manually on a worksheet.
Here’s how:
Screen Shot #1 – Filling data into adjacent cells by using the fill handle (+) Click the black plus sign at the lower-right-hand-corner of cell #5 (in this screen shot below E1) and drag your mouse across or down the cells you want to fill.
Note: By default, the fill handle is displayed, but you can hide it, or show it if it’s hidden.
Screen Shot #2 – After you drag the fill handle (+), cells will auto fill with data from the fill series. You can select from the options menu whether to fill with or without formatting, or select from any option you want.
- Note: If you don’t want to display the Auto Fill Options button every time that you drag the fill handle, you can turn it off. Likewise, if the Auto Fill Options button does not appear when you drag the fill handle, you might want to turn it on.
Screen Shot #3 – Or like mentioned above, formulas (which contain relative cell references), will automatically update when you use the Auto Fill handle.
Note: If you drag the fill handle up or to the left of a selection and stop in the selected cells without going past the first column or the top row in the selection, Excel deletes the data within the selection. You must drag the fill handle out of the selected area before releasing the mouse button.
I hope you find these time-saving tips helpful. For more Office Productivity tips & tricks, subscribe to the Crestwood Blog.
