087 059 7777

14 Excel Tips and Tricks

14 Excel Tips and Tricks
June 7, 2017 gnuworld
MS excel app on tablet tips and tricks

In almost any line of business, it’s likely you’ll use Microsoft Excel at some point.

Excel has a powerful range of features, but it can also be intimidating – and most users barely scratch the surface.

Even for beginners, though, certain tips and tricks can make the popular spreadsheet program faster and easier to use, and more valuable.

1) Open multiple spreadsheet files simultaneously

You can open multiple Excel spreadsheet files – or workbooks, as Microsoft calls them – with just one click. Select the files you want to view and press Enter on your keyboard.

2) Seamlessly shift between spreadsheets

It can be frustrating to switch between multiple, open spreadsheets to find the one you need. To make this easier and avoid confusion, press the Ctrl + Tab keys. This enables you to shift seamlessly between open spreadsheets.

3) Add multiple rows or columns

If you need to add several rows or columns to a spreadsheet, there’s a quick and easy solution. Simply highlight the required number of existing rows or columns, right-click, and select Insert from the shortcut menu that appears.

4) Automatically filter data

You can use the Filter feature to filter columns so that your spreadsheet displays only data you want to check. For example, you might choose to filter a Region or City column to check data just for a particular location, or to filter a Year column to view data just for a particular year.

To do this, select any cell in the relevant column and, to activate the Filter feature, open the Data tab on the ribbon and select Filter.

Once you’ve done this, clicking the solid arrow next to a column header opens a pop-up box, which lists all values that appear in the column (as well as options for sorting the column entries). You can select one or more of these values to view only the rows of data in which those values appear.

5) Get the hang of Pivot Tables

Pivot Tables make it easy to extract and summarise significant findings from large sets of data. You can create a Pivot Table by selecting any cell in a data set, clicking PivotTable on the Insert tab and clicking OK.

Using the PivotTable Fields pane, you can then choose which fields to display as rows and columns, and choose one or more fields for filtering the results.

A simple tutorial on creating and using Pivot Tables is available here.

6) Extend the Quick Access Toolbar

The Quick Access Toolbar is in the upper left corner of the screen. By default, it includes the Save, Undo and Redo icons. If there’s a command you use regularly in Excel – like Copy, Cut or Sum – you can add it to the Quick Access Toolbar so it’s easy to access.

Just click File, click Options and select Quick Access Toolbar in the Excel Options dialog box. You can then choose which command(s) to add.

7) Instant select all option

To select all the data on a spreadsheet, simply click the grey triangle that appears at the top left, where the column and row headers intersect. Alternatively, press the Ctrl + A keys on your keyboard.

8) Remove duplicated content

To remove duplicated entries, simply select a data range, open the Data tab on the ribbon and click Remove Duplicates in the Data Tools group. Excel will automatically remove repeated entries.

9) Delete all blank cells

Blank cells can affect the accuracy of your data. Fortunately, it’s easy to remove all blank cells from a spreadsheet. First, select all cells and press F5 on your keyboard. In the Go To dialog box, click the Special button. Then select the Blanks checkbox. This will select all blank cells.

You can then delete the blank cells by opening the Home tab, clicking the Delete down-pointing arrow and choosing Delete Cells.

10) Colour-coded data specific cells

You can use the Conditional Formatting function to colour-code cells with entries that meet criteria you specify. For example, you might choose to display all entries greater than or equal to a certain value in green, or to add a background colour to all cells that include a name you specify.

To do this, expand the Conditional Formatting menu on the Home tab. You can then choose the rules you want to apply and the associated formatting.

11) Automatically count entries that match specified criteria

Using the COUNTIF function is a quick, easy way to count the number of cells in a selected range that match specific criteria. For example, you can count how many cells contain a particular entry, or how many contain values greater than or less than a certain value.

To count how many cells contain a particular value, just enter this formula:

=COUNTIF(range of cells to check;value)

For example, if you type =COUNTIF(A1:A10,”Pretoria”) in a cell and press Enter, Excel will return the number of cells in the first ten rows of column A that contain the text Pretoria.

12) Navigate like a pro

In a large spreadsheet, you can easily jump to the first or last row, or the first or last column, by holding the Ctrl key on your keyboard and pressing the appropriate arrow key. For example, pressing Ctrl and the down arrow key will automatically move your cursor to the last populated cell in the currently selected column.

13) Transpose data for enhanced display

You can choose to transpose information from a row to a column without having to type it out again. Use the Copy feature on the data you want to transpose, and shift the pointer to a blank position on the spreadsheet. Go to Home, click Paste and choose Transpose. The selected content will then display across the columns.

14) Change the case of entered text

You can use simple functions to change the case of text in selected cells. You do, however, have to create the changed entries in a new column or row, and then copy them back over the original entries.

To change all the values in column A to uppercase, for example, enter =UPPER(A1) in a new, blank column and then copy this formula down the rest of the column. The new column will then contain all the values in column A, in uppercase.

For lowercase, use =LOWER in the same way – and to display every entry with an initial capital letter, use =PROPER.

At The Workspace, we offer affordable, fully serviced offices, coworking facilities and a range of business services, designed to make it easier and more affordable for all our clients to conduct their businesses and do their work. For more information or to book a tour of one of our branches, call us on 087 059 7777 or contact us online.

Contact us to find out more