While we would never consider ourselves experts at Excel, we know there is a ton of power in it that most folks aren’t using. We have featured a pile of Excel tips in the past, but here are some additional and mostly simple functions within Excel that you should be using if you aren’t already. We’re sure you can find a use for them.
TODAY and NOW
These ones are fairly simple but very useful. If you need to insert today’s date or today’s date and time, throw either of these in there for a constantly-updating variable.
Changing Case with UPPER, LOWER, and PROPER
While Word has a function for this, sometimes things need to be done in Excel. With these functions, you can convert the text into all UPPER-CASE, lower-case, or Proper-Case. Then you can output data like this:
Various Rounding Functions
There are a variety of rounding functions built into Excel that can be used for different purposes, depending on your needs:
- ROUND: Rounds a number to specified number of digits.
- ROUNDUP: Rounds the number up to a specified number of digits.
- ROUNDDOWN: Rounds a number down to a specific number of digits.
- MROUND: Rounds up or down to specified multiple.
- CEILING: Rounds a number up to the nearest multiple of significance.
- FLOOR: Rounds a number down to the nearest multiple of significance.
Depending on your needs, you may need one or more of those formulas. Here is some sample output:
Remove Extra Spaces with TRIM
Sometimes you have data in Excel that was imported from somewhere that has a mess of extra spaces. The TRIM function will help clean that up.
Logical Functions in Excel with IF, AND, OR
While IF, AND, OR, XOR and NOT are extremely powerful and useful in Excel, they’re a bit more complicated than some of the simple functions above. If you’re trying to get some logical expressions setup in Excel, I’d recommend reading up on the IF function and nesting IFs from the Excel wizards at Ablebits.
More Text Manipulation Functions
While there are a bunch of useful Excel functions for text manipulation like SEARCH, FIND, LEFT, RIGHT, MID, SUBSTITUTE and SEQUENCE that we may feature in a future article or two, Microsoft recently released a few new ones to simplify some of the functionality of those:
- TEXTBEFORE – Returns text that’s before delimiting characters
- TEXTAFTER– Returns text that’s after delimiting character
- TEXTSPLIT– Splits text into rows or columns using delimiters
Here you can see it in action:
You have a favorite Excel formula or trick? Share it in the comments below!