6 Must Know Excel Functions for Everyday Tasks
Microsoft Excel has been one of the most powerful, useful business applications on the planet going on the better part of 30 years now. Almost anyone can pick up a basic understanding of how to enter data into its cells and sheets. However, to bring out its true potential you’ll need to master some of its built-in functions. These six must know Excel functions can really increase your productivity and efficiency.
1. SUM is one of the most, if not the most, useful function that you will probably use in Excel. It simply adds a range of cells together and gives you the total.
SUM(A1,A4) will add the values in column A1 through A4
2. AVERAGE is another of the very popular mathematical/statistical functions. This function will give you the average value of a list of individual cells or a range of cells.
AVERAGE(B1,B4,B7) will give you the average of just these three cells. AVERAGE(B1,B7) will give you the average of cells B1 through B7
3. UPPER and LOWER are extremely useful string manipulation functions that you can use to change the text in a cell or range of cells to either upper or lower case. This saves a ton of time since you don’t have to retype the text contained in the cells. These functions will change the text for you automatically.
UPPER(A1) or LOWER(A1)
4. RTRIM and LTRIM are also string functions that help you maintain the integrity of your data. If the data contains beginning or trailing spaces, you can use these functions to automatically remove them. RTRIM removes spaces from the right side and LTRIM removes beginning spaces from the left side. You can also use the TRIM function to remove both left/beginning and right/trailing spaces.
RTRIM(A1) or LTRIM(A1) or TRIM(A1)
5. CONCAT is another very useful string function that will combine 2 strings into one. For example, if you had a first and last name column, you could use the CONCAT function to combine them and create a full name or display name column.
CONCAT(A1,B1) or you can concatenate any list of strings such as CONCAT(A1, “equals” ,C1)
6. DATEADD is a date time function that will add and subtract time from a specific date. You might want to create columns that contain specific intervals that are days, months, or years into the future. DATEADD can also return the value in a specific format so you can govern the way the text is displayed.
DATEADD(“yyyy”, 4, “11/11/2011”) would result in a date of “11/11/2015”
DATEADD(“m”, -3, “11/11/2011”) would result in a date of “11/8/2011”
These six functions barely scratch the surface of the power of Excel. They are just some common, everyday functions that help you to quickly complete tasks that would take a long time to work through manually. The extensive library of Excel functions goes much deeper and becomes much more complex depending on what your goals are. Hopefully, these will get you started and you can build from here.
To become a master, consider an Excel certification from a high-quality institution like ONLC.
