The most utilized and hated tool: Excel
- Feb 2, 2017
- 2 min read
Spreadsheets: There’s nary a word that can elicit such viscerally positive or negative reactions from people as this one can. Businesses large and small use these data sets to make decisions from pricing and inventory, to web design and payroll. As the local Excel expert, managers regularly call on me to help them with formulas or even to generate a sheet altogether. Spreadsheets are either loved as a means to examine large sets of data, or despised by the complexity inherent in large sets of data.
Most people know the basics; autosum, find/replace, cut and paste, inserting or deleting cells, rows or columns etc. A few of my most used and less known functions are contained here. Hopefully this helps some people and saves some time.
Vlookup (and Hlookup) is my most widely used. I know Index(match) is getting more popular, but I still live my life with Vlookup. Essentially, this formula searches the left column of an entire set of data for information that matches what you want it to find, and then returns from that set of data whatever information you want. For example, if you have two spreadsheets, but one is missing the price of an item, you can use vlookup to find the item, and return the price of that item on the second sheet. Hlookup does the same thing, except horizontally instead of vertically.
The & function. This particular tool allows you to combine information between columns or rows into one cell. Say you have a spreadsheet with paint colors in one column, and the manufacturer in the other, but you need that information on the same line so it’s in a description, you would use & to combine them. You could even use & to add a comma and space between them by doing multiple &’s. =a1&”, “&b1 is a great way to combine information with delimiters, or to add an extension to a filename.
Find/Replace is mostly used to replace entire groups of information or remove characters, but did you know you can use it to insert information? If you need to replace all the information between two underscores and leave it blank, use find replace to search for _*_ and replace with a blank to accomplish that.
IF as a function happens to be incredibly useful if you are want to make sure your new price is profitable. The following formula will multiply a number by 1.1, and if that number is higher than a baseline figure, return the multiplied number, and if it’s not, it will return false: =if(a1*1.1>b1,a1*1.1,false). You can also use this formula to make sure discounts aren’t below cost by replacing the 1.1 with .85 or .9 or .99 or whatever the discount you want to offer.
I hope some of these tips help you, and feel free to email me if you want more advice or are having trouble with your spreadsheet.
Justin Huereña Staff blogger
Comments