Master Excel with these 5 genius tricks

By Kim Komando

September 23, 2024

Photo © Hani Eriani | Dreamstime.com

You can’t keep track of everything on paper. Excel can play a significant role in your daily workload. Whether you’re a beginner or a pro user, there’s much to learn — like these five useful Excel tricks.

Want to become an Excel expert? It’s a good strategy for small business owners who want to grow this year. After all, you’ll generate tons of data. Speaking of which, here’s how to stop third parties from selling your data.

Maybe you’re overlooking the most useful Excel strategies. Scroll down for five Excel tricks to simplify your professional and personal life.

1. First of five useful Excel tricks: Solve errors

Microsoft Excel is chock full of practical formulas. They help save hours you’d otherwise spend clacking away at a calculator.

Unfortunately, it’s easy to make mistakes when calculating through Excel. Its formulas reference many cells, and you can confuse them if you don’t pay attention. Use this table to fix common Excel error messages:

Error message	Why did you get it?	How to fix it …
#####	One of your columns isn’t wide enough.	Increase the column width.
#DIV/0!	Your formula refers to a cell that has 0 or is blank.	Make sure your divisor in a function isn’t blank or zero, or try these other solutions.
#REF!	Maybe you deleted or moved a cell one of your formulas refers to. Either way, your cell reference isn’t valid.	Restore any cells you deleted or change the formula.
#N/A	One of the values in your function or formula isn’t available.	Replace #N/A with new data.
#NAME?	Excel doesn’t recognize the text in your formula.	First, make sure the cell name exists. Then, check for spelling errors.
#NULL!	You made an error when specifying an intersection of two areas.	Use a colon (:) to separate references to a contiguous range of cells. Are you referring to two areas that don’t intersect? Use a comma (,) instead.
#NUM!	Your formula or function has invalid numeric values.	Make sure the arguments used in functions are numbers.
#VALUE!	You used the wrong type of variable or value in a math operation.	Double-check your formula, function and cells referenced by the formula.

2. Transpose data in a snap

So you transferred some data into Excel — but it didn’t look how you wanted it to. Maybe you entered the data as rows when you wanted them to become columns. Don’t worry: You don’t have to reformat each cell manually.

With these steps, you can rotate data in your worksheet from columns to rows and vice versa.

1. First, select the data in your desired column or row.
2. Then, press Ctrl + C to copy the selected cells.
3. Right-click an empty cell in a row or column where you want the copied cells to go.
4. Select Transpose from the paste options.

Just like that, you’ll be able to reformat your data.

3. Master the substitute function

Want to replace a specific character or set of characters with a new set? First, select the cell you want to replace, like so.

Then, enter the = sign and type Substitute. A formula window will pop up.

Double-click the Substitute function. Then, type the cell location followed by a comma. Here’s what it will look like:

Next, type quotation marks and enter the old text you’re replacing, followed by a comma. Since I’m replacing the words “WestStar Multimedia Entertainment,” I typed that.

Then, type more quotation marks and enter the new text exactly how you want it to appear. Enter another comma. Close the function by typing a close parenthesis, the “)” symbol, and pressing the Enter key.

Select Enter. Just like that, I replaced the data in one cell with the data in the other cell, C4, that I typed. Nifty!

4. Quickly split columns

Let’s say you have multiple words in a single cell. You want to move one of those words into another cell. Maybe you’re writing a spreadsheet full of business contacts and wish to make a column for first names and another for last names.

Good news: The Text to Columns feature can spread text across multiple columns. Here’s how:

1. Select the column to the right of the column with your data. Right-click on an empty cell and select Insert.
2. Then, select the column with your data. From the Data tab, select the Text to Columns button.
3. Then, select Delimited > Next and choose your delimiter from the available options. (Not sure what a delimiter is? It’s a character that separates the text in your column, like a comma or space.)
4. Lastly, go to Next > Text > Finish.

This will save you a ton of time if you use Excel to keep track of business contracts. Want more small business tips? Tap or click here for five useful productivity tricks to try this year.

5. Last of our five useful Excel tricks: Set your own styles

There’s a lot of creative potential in Excel. Here’s how to create a style:

1. Put your cursor in a cell.
2. Select the Home tab, find the Styles group and click the Cell Styles button.
3. Next, click the New Cell Style button… at the bottom of your dialog box.
4. This pops up a Style Name box.
5. Enter your style name. (NOTE: You can’t use spaces.)
6. Then, click OK.

Then, you can simultaneously apply those formats to text or a range of text. Just do this.

1. To apply a style, select a cell or range of cells.
2. Then, go to the Home tab in the Styles group.
3. Click the Cell Styles button.
4. Select your desired cell style from the Custom styles area.

Just like that, you’ve applied a style and added some pizazz to your spreadsheet. Although you’ve learned some of the most useful Excel tips and tricks, remember that Excel can’t do everything. Sometimes, you need a tool with more robust features.

Small business owners, it’s time to upgrade

If your company is growing, you may need more than just Excel. You need high-quality finance software that gives you the complete picture. With our sponsor, NetSuite by Oracle, you can:

1. Gain visibility and control of your financials, inventory, HR, planning, budgeting and more.
2. Automate your processes and close your books in no time.
3. Stay well ahead of your competition.

Approximately 93% of surveyed businesses increased their visibility and control after upgrading to NetSuite. Over 32,000 businesses already use NetSuite.

NetSuite has a financing program if you’re ready to upgrade! Head to NetSuite.com/KIM for this special, one-of-a-kind financing offer on the No. 1 financial system for growing businesses.

Tags: budgeting, Competition, data, errors, Excel, Feature, finance software, formulas, Functions, home, inventory, NetSuite, numbers, Oracle, play, Productivity, small business, space, spread, spreadsheet, third parties, Typing, upgrade