Excel is an amazing tool that can do so much, from keeping basic lists to more complex data analysis and visualizations. In this article today you are going to learn 4 Excel tips that will make your life easier and you more efficient in Excel.

In this article we are going to look at

• Simple Steps to Transpose Data
• Lightning Speed Navigation with GOTO Special
• Easy Data Entry with Forms in Excel
• Analyse data with Quick Analysis

Trick 1: Simple Steps to Transpose Data in Excel

Sometimes it is necessary in Excel to change the data from the rows to the columns or from the columns to the rows. With large amounts of data, re-entering these values would be both time consuming and also prone to clerical error.

Paste Special is a unique feature that allows you to perform operations that may be tedious to perform any other way.  One of the functions available is Transpose

A worksheet has been set up with the month names listed down the rows.  However this was done in error and we need the months going across the columns.  How can we do this efficiently?

The first step is to copy the data that we wish to transpose.  We can do this by selecting all of the cells and then pressing Ctrl+C or by right clicking and selecting copy.  By doing this the data is now copied to Excels clip board.

Once the data is copied to the clip board in Excel, we can now select where we want to paste this data.  Click on the cell in which you want the data to start.  This cell is now the active cell.

With the data copied, we can now activate Paste Special. To activate Paste Special, in the home ribbon select paste and select paste special.

The keyboard shortcut to active paste special is ALT+E+S

To transpose the copied data, select Transpose from the Paste Special window

The data will now be copied across the columns with no need to reenter them.

Trick 2: Lightning Speed Navigation with GOTO Special

GOTO Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. GOTO Special only selects cells in the current worksheet rather than the whole workbook.  The usefulness of this is that it enables you quickly look at the structure of a workbook.

To get to the GOTO special, press F5 and the GOTO box will appear.  At the bottom of this box you will find Special.

As you can see from the go to special dialogue box, you can search for Comments, Formulas, Constants, row different and data validation to name just a few.

Let’s look at highlighting all the constants containing text in Green.  Press F5 to get to GOTO and select Special. In special select constants, and text and press OK. When you press Ok, all the text cells with be selected, you can then just go to your home ribbon and change the fill to Green.

Following the same process we can now highlight all the constants with numbers in yellow

Very quickly we can now spot some problems with this worksheet

Following the same process we can now highlight all the constants with numbers in yellow

Can you see how this has highlited problems with this worksheet?

By using GOTO Speical to select and highlight we can now see the Q1 results are hard coded in Dublin, but are formulas for the other regions, so really this should be a formula summing Jan to march.

We can also see that the March values in Germany are text values, which means the formula value for Q1 is not correct as it would have excluded the text values.

And we can also see a hardcoded value just off the table.  This is worth investigating and one of the reasons GOTO Special is used in spreadsheet auditing.

Go to special is a really easy way to navigate a complex workbook and quickly select what you want. Popular uses of GOTO special are, selecting blank cells, locking formula cells, and finding hardcoded values in formulas.

Trick 3: Easy Data Entry with Forms in Excel

How often do you have to enter data into a spreadsheet manually?  This repetitive task can easily lead to clerical errors and omissions. Especially if it is a large data set that requires updating.

With large amounts of data, Data Entry forms in Excel come in extremely handy.  They can both speed up the data entry process making you more efficient and help reduce errors.

By adding the form to your quick access toolbar you are ensuring speed of access with only one keystroke to access the form.  Follow the steps in the gif to add forms to your quick access toolbar.

The form will be based on the template within the workbook. For the form to identify a template, the worksheet must contain

• Headers
• Sample data

The sample data should be a manual entry of the first row of data

To begin using the form, select a single cell within the range and click the form entry button on your quick access toolbar.  The form will open and by default will show the first row of data

To enter a new record select New on the form.  The form data will clear and you can enter the data as per the headers given.  If there are formula in your sample data, you will not be able to input data to that field.

Once you have entered a record, pressing enter will add the data to your spreadsheet and return you with a blank form. When you have completed the data entry, pressing close on the form will return you to normal data entry mode.

Trick 4: Analyse data with Quick Analysis

Microsoft are continuously adding new features to Excel that allow us carry out tasks for efficiently.  One of these additions is the quick analysis that becomes available when you highlight some data in a workbook.  Quick analysis give you access to format, chart, totals and sparklines all within the click of a mouse.

From quick analysis you can quickly do any of the following

• Add or remove conditional formatting
• Add or remove charts
• Add aggregation functions such as sum, average and running totals
• Convert to a table or pivot table
• Add or remove sparklines

To access quick analysis first select the data you wish to analyse, then press CTRL+Q to open the quick analysis tool box.  You can also access the quick analysis tool box by hovering the mouse over the bottom right of the last selected cell.

How do I add data bars using quick analysis?

First select the data you wish to visualise using data bars. Then press CTRL+Q and under formatting select data bars.  Immediately the data bars will be shown in your data set.  To remove these data bars press CTRL+Q and under formatting select Clear

How do I add a running total column?

Totals are seperated into row total and column totals.  To add a running total column to your data,  first select the column that you wish to tot. press CTRL+Q and select totals.  Scroll over to find the Column running total and select it.  A new column will be added to your data that contains a running total for the original selected column.

How do I insert a chart using quick analysis?

Charts can be quickly added to your data using quick analysis. First select the data you wish to chart, then press CTRL+Q and select charts.  Excel will show you the most appropiate charts for your selected data, by hovering over one you will get a preview.  Click on the chart type and a chart will appear in a new worksheet.

How to I insert a pivot table from quick analysis?

Pivot tables can be quickly added to your data using quick analysis. First select the data you wish to pivot, then press CTRL+Q and select tables.  Excel will show you the most appropiate tables and pivot tables for your selected data, by hovering over one you will get a preview.  Click on the pivot table type and a pivot table will appear in a new worksheet.