Effective uses of MS Excel at workplace

MS Excel is one of the advanced tools for working with raw data, but people fear it too. The application seems complicated, and people think it as something that would take hours to figure out.

This isn’t true at all. My students once asked me this question “Is there any possible way to make Microsoft Excel easy?” My reply to them was “Absolutely Yes”.

Learning a few tricks, you can effectively use MS Excel and bring it down to your level. Your perception for the application will change.

Stay with me and I will show some MS Excel tricks and tips that will help you use MS Excel effectively and confidently at your workplace.

1. Format Painter

The Format Painter is among the most underused features of Excel. The Format Painter copies a set of formatting from one place and applies it to another.

A spreadsheet that is visually organized is highly appreciated as it can help the department heads to follow data and calculations easily.

To quickly apply your formatting across hundreds of cells, use the Format Painter:

1. Select the cell or a range of cells with the formatting you wish to replicate
2. Click on Home tab in the ribbon and in the Clipboard group, click Format Painter. Excel will display a paintbrush next to the cursor. See image below:
   
3. Click on it to apply all of the attributes from that cell to any other.

When we need to format a range of cells, we can double-click the Format Painter during step 1. This keeps the format painter active. To deactivate it use the ESC button.

2. Selecting Entire Spreadsheet Columns or Rows

Another quick tip is to use the CTRL and SHIFT buttons to select entire rows and columns containing data.

1. To do this we need to click on the first cell of the data sequence you want to select.
2. Press CTRL + SHIFT together.
3. Use the arrow keys to navigate either above, below or adjacent to the cell you’re in.

To select entire data set we can also use CTRL + SHIFT + *.

3. Import Data into Excel Correctly

Using is Excel you can also combine different types of data from all kinds of sources. This is called importing the data.

Instead of copying and pasting complex data sets, we can do it by using the Get External Data option which is under the Data tab.

There are specific options for different kinds of sources. Select the appropriate option according to our needs.

In the image below we can see different options like “From Access”, “From Text”, “From Web”, “From Other Sources”, and “Existing Connections”.

4. Entering same data into multiple cells

Sometimes we need to enter the same data into a number of different cells. Normally we use copy-paste over and over again. But there is also a quicker way for this.

1. Select all cells where you want to put the same data filled in by holding the CTRL key and click on the individual cells across the worksheet.
2. In the last selected cell type in your data.
3. Press CTRL+ENTER and the data will be filled in for all cells you had selected.

5. Display all Formulas used in a spreadsheet

We regularly get into such situations where we receive a spreadsheet from someone and need to understand it. Let us call it jumping into a spreadsheet created by someone else.

No need to worry. We can easily find out which formulas were used for the specific calculations.

In the ribbon under the Formula tab, in Formula Auditing section we can find Show Formulas button.

Keyboard shortcut key to this is CTRL + `. It gives you a view of all formulas used in the workbook.

6. Freeze rows and columns

This function of MS Excel is mostly used while viewing lengthy spreadsheets. Normally once we scroll the first 20 rows, the headings in the first row with the column labels disappear from view and we begin to lose track of how the data was organized. We have to look back again and again.

By using the Freeze Panes feature under the View menu we can use freeze the top row option, if you have a spreadsheet with numerous columns, you can opt to freeze the first column.

7. Enter Data Patterns instantly

One great feature of MS Excel is that it automatically recognizes data patterns. Further to this Excel also allows us to enter those data patterns to other cells.

1. To establish a pattern we need to simply enter information in two cells.
2. Highlight the cells and we can see a small square in the bottom right hand corner of the last cell.
   
3. Place the cursor over this square until it becomes a plus sign.
4. Then click and drag it with the mouse down to the cells within a column or row and see the pattern working itself.

You can see below example that it follows a pattern of adding 2 to each cell.

To manually enter data into multiple times will consume much time. But using this feature helps save a lot of time resulting in increased productivity.

8. Hiding rows and columns

Sometimes we may have information in rows or columns that we need to be visible to us and no one else’s shall see it. We can do this by using Hide feature.

1. Select the column or row that we want to hide.
2. Under Home Tab in the Ribbon go to Format section.
3. Select Hide & Unhide option to hide rows or columns according to our need.

To unhide them we need to just click on the first row or column that occur just before the hidden range. Repeat steps 2 and 3. This time we need to select Unhide Rows or Unhide Columns.

9. Copy formulas or Data between worksheets

Other helpful tip to ease Ms excel is how to copy formulas and data to a separate worksheet.

This has to be handy when we are dealing with data that is spread across different worksheets and would require repetitive calculations.

1. Open the worksheet that contains the formula or data we require to copy. Hold the CTRL key and click on the tab of the worksheet you need to copy it to.
2. Navigate to the cell with the formula or data that we need in the opened worksheet.
3. Use F2 to activate the cell.
4. Finally press Enter which will re-enter the formula or data into the required sheet. It will also enter the formulas or data into the same corresponding cell in other selected worksheet.

These are general tips for ease of access for MS Excel. This won’t turn us into an Excel guru, but they can help you efficiently and timely complete your tasks.

If you have any difficulty understanding any function or if you have any questions, please comment below and I will be happy to respond them personally.