Skip to Content
Five useful Excel tricks that will help you in 2023
Photo © Hani Eriani | Dreamstime.com
Small business

Master Excel with these 5 genius tricks

Presented by Oracle NetSuite

Presented by Oracle NetSuite

Let NetSuite show you how they’ll benefit your business with a free product tour at NetSuite.com/Kim.

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 a lot to learn — like these five useful Excel tricks.

Want to become an Excel expert in 2023? 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 that will make your professional and personal life much easier.

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 if you don’t pay attention, you can confuse them. Use this table to fix common Excel error messages:

Error messageWhy you get itTo 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/AOne 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.

Want to start concatenating columns, splitting columns, transposing data and understanding errors, causes and solutions?

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:

Using proper, lower and upper functions in Excel is super useful. Using custom formats is another one of the most useful Excel tips you'll find online.

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.

Excel tips: Creating a style, applying a style, modifying a style, using the substitute function and how to change the case of text in Excel.

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: You can use the Text to Columns feature to 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 select your delimiter from the available options. (Not sure what a delimiter is? That’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 apply those formats to text — or a range of text — simultaneously. 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:

  • Gain visibility and control of your financials, inventory, HR, planning, budgeting and more.
  • Automate your processes and close your books in no time.
  • 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.

cryptocurrency e-book hero

New eBook: ‘Cryptocurrency 101’

Don't want to lose your dough to crypto? Check out my new eBook, "Cryptocurrency 101." I walk you through buying, selling, mining and more!

Check it out