SBP Blog

Microsoft Excel: a few handy tips

Apr 16, 2015 by AdrianC

Crucial to many people in their work, disliked by some, Microsoft Excel is one of the best software solutions, which hides many, many productivity secrets.

Although I'm sure that many of you are acquainted with some of the "magic" that this software is capable of, becoming more proficient should be our target, that's why I think it's necessary to enhance our skills with some new Microsoft Excel tricks:

Trick 1: Quick Navigation

The first one is very basic (and maybe known by all), but also very useful. Pressing 'Alt' will display shortcut letters to the Ribbon menus, and after pressing a shortcut key, further shortcuts will appear.

Example: Select cell - Press 'Alt' + 'H' + '1'

Microsoft Excel quick navigation

Trick 2: Same Data in Multiple Cells

If you want to input the same data in multiple cell, there's no easier way than this:

1. Hold 'Ctrl'
2. Select the cells that you want to contain the same data
3. Type the text you want
4. Press 'Ctrl' + 'Enter'...and that's it.

Trick 3: Auto-sum

An automatic sum function will appear at the bottom (for column) or right cell (for row), of the selected cells, after pressing 'Alt' + '='. This is a quick way to calculate the sum of a column, a row or a part of them.

Trick 4: Goal Seek

This is very helpful when dealing with sales and various calculations, because you can let Microsoft Excel update a value in order to obtain a final result. Let me detail this a bit: you have a formula which calculates the Total Revenue for some tickets sales. Let's say that you want to know what it takes in order to have a total revenue of $ 310,000, based on changes from day 2:

1. Select F7 cell
2. Select "Data" from Ribbon menu
3. Select "What-if Analysis"
4. Choose "Goal Seek"
5. Change the "To value:" field to $ 310,000
6. Enter 'E7' in the "By changing cell:" field and press "OK"

Microsoft Excel Goal Seek

Trick 5: Input Restriction

By using this, you can force certain field to allow only specific values in a table:

1. Select the cells you want to apply the restrictions to
2. Go to "Data" - "Data Validation" and choose "Data Validation"
3. Choose the restriction that you want to enforce from the "Allow" drop-down list (Decimal, Date, List etc.)
4. Specify the conditions and press "OK"

Trick 6: Insert Transposed Table

You'll sometimes want to flip the rows and the columns in a table, and in order to do this, you'll have to:

1. Select the table and press 'Ctrl' + 'C'
2. Click on the cell that you want to be the top left corner of the transposed table
3. Right click and choose "Paste Special"
4. Check "Transpose" and press "OK". Voila!

Microsoft Excel transposed table

There's another trick to this: you can transpose the table, in order that when you modify something in the original table, the changes are also made in the transposed one:

1. Count the number of rows and columns of the original table (let's say we have 4 rows and 5 columns)
2. Perform a selection in the area where you want to put the transposed table (the selection must have 5 rows and 4 columns)
3. Click in the formula field and type '=Transpose(A1:E5)'
4. Press 'Ctrl' + 'Shift' + 'Enter'

These are only a minute part of all the functionalities of Microsoft Excel, but maybe these can help you to increase your productivity, satisfy your managers' demands or prepare your homework for the next Excel class that you have on Monday.

Tags: Microsoft  Office 


Michael commented on 5/8/2015 1:18:20 PM

The Goal seek feature is very useful, and people who work with lots of numbers will surely appreciate this trick.

Your Comment: