4 Advanced Excel Hacks to Increase Your Productivity
Microsoft Excel can be used for far more than making forms or holding tables of information. (If you need to review our post on 4 Basic Excel Hacks, click here for a refresher.) There are also multiple ways to perform certain tasks, so unless you’re a highly-trained Excel expert,, you may not be aware of the easiest or fastest method. For those of you who are familiar with formatting, charts, and multiple worksheets, these tips and tricks will turn your small sea of data into a wide ocean of organized information.
1.) Flash Fill (Excel 2013 and newer)
Under the Data menu, the Flash Fill function can populate your cells based on an educated guess on what you’re filling in. For instance, if you have a table with email addresses and you’re extracting last names, Excel will start to suggest last names into the rest of the cells in your table. As long as you are following a pattern in what you’re entering and using the same delineators, Excel can recognize the rest of the data that should be entered. If Flash Fill isn’t automatic, go to the File menu and the Advanced options to turn it on.
2.) If Function
The If function will return values based on whether the information in the cell is true or false based on your search criteria. The formula to use is fx=IF (logical_test, [value_if_true], [value_if_false]). One example use could be giving a bonus to employees who meet a sales goal of $500 for the month. The formula would read fx= IF (D8>500, “Bonus”, “Try harder next month”). You can nest up to 64 logical tests within the IF function. You can use the following signs in the If functions: = (equal), > (greater than), >= (greater than or equal to), < (less than), <= (Less than or equal to), or <> (not equal to).
CountIf will return all of the matches that meet multiple criteria. For instance, if you need all the cells where the value is more than 10 with cells in row A2 through F2, you can type out the formula fx=COUNTIF (A2:F2, “>10”). You can do multiple criteria as well, so if you want cells where the value is more than 10 but less than 20, you can just add it into the formula: fx=COUNTIFS (A2:F2, “>10”, A2:F2, “<20”). Excel can handle up to 127 pairs of ranges and criteria, but the search must be within a range of cells (like A2:F2, or G:G), not within cells that aren’t adjunct (like E4, F7, and H9). The function SUMIF works the same, but adds all of the values in the cells that meet the criteria.
3.) Text to Columns
If you have a line of data in a single cell that you need broken up into two or more cells, there’s a menu item under Data called “Text to Columns” that can split it up.
For instance, if in cell A5 you have Joe Muggs Coffee, 6789 First Street, Charlotte, NC 28203, you can set the “Text to Columns” to separate out the data string at each comma. Cell A5 will no have Joe Muggs Coffee, cell B5 will have 6789 First Street, cell C5 will have Charlotte, and cell D5 will have NC 28203. You can also separate out strings of data that are separated by a tab, space, comma, semi-colon, or even set your own delineator (like after each * or before the letter “z”.)
4.) Goal Seek
For forecasting, consulting, and sales, Goal Seek can pull insight from a set of data and return a calculation for your criteria. For instance, if you have the sales numbers from the first six months of the year, you can find out what month you will hit the sales goal that’s twice the amount of June. Go to the Data menu and select the What If function, and Excel will calculate the amounts and tell you which month will be the successful one. You can only use one variable in Goal Seek.
We hope this Excel article is useful for increasing your productivity. If you need more assistance with your organizing spreadsheets, or contact and database management, DCA Virtual team members excel in helping your business. Contact us today, we’re happy to assist!