Category: Hints & Tips

Hints and tips for MS Office and other applications. Contributions from our team of talented trainers.

Posted on

Using the Total cell style in Excel

Trying to review a spreadsheet with unformatted data can be a real eye sore, but by using the Total cell style in Excel you can quickly give your data more context.

An example of using the Total cell style in Excel

Below we have a list of sales figures for several sales reps in a travel company. In just a few steps by applying the Total style helps the “Total” column of sales figures stand out.

Total cell style in Excel
Using a couple of Excel styles can make all the difference

1. Select the range of cells you wish to format. Goto the Home tab, then the Styles group. Then click on the “More” button the more button in Excel to expand the group. (for Excel 2013 & 2010 click on Cell Styles button, see the second screenshot below)

Using the Total cell style in Excel 2007
How to select Total option from style section
Using the Total cell style in Excel 2010
Selecting Cell Styles in Excel 2013 and 2010

2. Now within the Style options, under the section, “Titles and Headings” just select the Total option. The selected range of cells will now display the Total formatting.

In the example above, we also repeated these two steps for the row with “John”, and with one further twist, also included an additional style option under “Themed Cell Styles” which was to select a colour.

Tip: If you are experimenting with different styles you may wish to remove a cell style, to do this simply select the range of cells you wish to “reset”, then go to the Style group (within the Home tab) and select Normal from the “Normal, Bad, Good, Neutral” section.

how to remove a style in Excel
To remove a style, just hit the Normal button

What we have covered here is a simple demonstration of how using styles can lead to more engaging and professional-looking spreadsheets. You can even create your own custom cell styles which may contain multiple formatting options and can be a real time saver when dealing with similar spreadsheets.

Found this useful? Learn more about Excel cell styles:-

  • How To Use The Cell Styles Functionality In Excel 2010. Read more
  • How do you group a selection of styles on a workbook? Read more
  • A comprehensive guide to applying, creating and removing cell styles

 

 

Posted on

Copy vertical data and paste it horizontally in Excel

Easily copy & paste using the Transpose option

This is a very useful little tool.  Sometimes when setting up a worksheet you may realise that it would be better to put your column data into a row.  One way to achieve this would be by cutting and pasting individual cells.  But there is a more efficient way.

There are lots of pasting options – here is a quick example of how the Transpose option works.

To change vertical data in a column to horizontal data in a row:

Step 1. Copy the vertical data. In this case months of the year can be used as a simple example. Just click and drag to select the text, and then Control + C to copy it.

months_excel

Step 2. Find the cell you want to insert the data, and then click on it to select.

Step 3. Select the Paste button, but click on the down arrow – and a pop up menu of choices appears (these are your Paste Special options).

paste-special-advanced-microsoft-excel-courses
The transpose option is on the second row, last icon.

Step 4. Select the Transpose option and click ok…and your vertical data is now across the top row

copy-from-vertical-to-horizontal-advanced-microsoft-excel-courses
The vertical data is now pasted across the top row.

Tip: You can also access the Transpose option, by right clicking when you have selected the new destination cell, and you can select Transpose icon from the Paste Special menu.

You can use the Transpose option to paste Horizontal data to the Vertical using the same method.

It’s a really simple tool that can save significant time and allow you to develop your worksheets without having to redesign everything if you decide to switch rows, to columns.  Other paste options are included in our advanced Microsoft Excel courses https://www.stl-training.co.uk/excel-2010-advanced.php.