Be a Spreadsheet Ninja with these Excel for Business Tips


Every office has one of them: the spreadsheet whiz who can orchestrate the most beautifully complex formulas or make data look amazing in reports. And they’re fast—somehow speeding through tasks that would take you twice as along and drive you crazy with the tediousness of it all. What have they got that you don’t?

There must be some hacks you’re not using.

The only explanation here is that somehow along the way, you’ve missed out on learning the right hacks. To master Microsoft Excel for Business, you’ll need them!

Not to worry: you’re here now and we’re about to remedy that. Here are your pro tips for becoming the office ninja at Microsoft Excel.

1. Use ‘Paste Special’

A common need in Excel is to take a bunch of rows and convert them to a bunch of columns (or vice versa). You’ll lose your mind doing that by hand, so use Paste Special.

  1. Highlight the rows you want to convert to columns.
  2. Click ‘Paste Special’ from the top menu.
  3. Click the ‘Transpose’ box at the bottom of the pop-up window that appears, then hit ‘OK’.
  4. Done!

2. Learn to Use String Functions

Anyone who works with data knows there are fast ways to do things and then there are the dumb ways to do things. If your data needs to be reformatted or split up into multiple cells, there’s usually a hack for accomplishing your task quickly.

Re-keying acres of data is never a good idea, yet people do it all the time.

Let’s say you have a column of data that are comprised of letters and number like this: QPY123ABC. You want the data broken up into three separate columns: QPY  123   ABC.

Instead of wasting an entire afternoon re-keying, there’s a faster way: string functions.

Specifically, we’ll be using Excel’s Left, Mid, and Right string functions. Essentially what we’re doing is extracting the left portion (QPY), the mid-portion (123) and the right portion (ABC) and placing each portion into its own column.

Here’s what that formula looks like:

 =Left(source_string.number_of_characters)

which translates, for our example, to:

=Left(A2,3)

The ‘source string’ is the cell from which the formula is deriving its info. The original QPY123ABC is located in cell A2.

The mid  function has an extra section in it as follows:

=Mid(source_string.start_position.length)

which translates, for our example, to:

=Mid(A2,4,3)

Finally, we have the right function which is merely a mirror version of the left function: =Right(A2,3), where “3” is the number of characters.

3. Make Your Data Look Pretty With Graphics

Pie charts, bar graphs, and other elements that you’re using in your data reports can take images, too. For example, let’s say you have a pie chart where each slice represents a company with whom you do business.

Wouldn’t it be nice to put company logos on those slices? Here’s how:

  1. Select the pie slice.
  2. Select ‘Series Options’.
  3. Select ‘Picture or texture fill’.

Other fun options include inserting an image not into the graph itself but into the window for the graph so it sits next to your pie chart. You can also leave a slice blank if your data calls for it. For that, choose ‘no fill’.

4. Text to Columns

Need to import text into your spreadsheet? Or already have a bunch of data in one column that needs to be split up into multiple columns? No problem.

The most common example is a column full of first and last names. You’d like that split into two columns: a column for the first name and one for the last name.

  1. Select the column.
  2. Click the Data tab, then choose ‘Text to Columns’.
  3. Choose to separate by ‘delimeters’ (the delimeter in this case will be the ‘space’).
  4. The rest is intuitive- hit ‘next’, then finish the deal. There will be other fancy options but this is a good start.

Moving Forward

If you work with lots of data, these four tips should save you tons of time. Now your only problem will be figuring out what to do with all that extra time you saved!


Enjoyed this post? Why not keep up to date with our news and future blog posts by signing up to our newsletter