Category: Application

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.

Posted on

Adding comments to Excel worksheets

Adding comments to an Excel spreadsheet helps when you are sharing a worksheet with colleagues.  You can add extra information to a figure in a cell, use to note trends, or add an action note.

The comments are hidden when you are working in your normal worksheet view, but you know a comment has been added by the little red triangle in the top right of the cell, If you put your cursor over the cell, the message appears as a yellow post-it style note.

To add a comment to a worksheet
Click on the cell you want to add a comment to.  Then go to the Review tab, and the Comments group.

Comments-review-tab-training-courses-excel
The comments box appears, I can add my comment press return and it’s saved. The red triangle in the top right corner reminds me that there is a comment.

I can edit a comment or add to one made by another colleague, by selecting the comment, and using the Edit comment button.

Viewing comments
In a large worksheet, you may want to review all the comments quickly.  Excel can take you through each one in turn if you select Review tab, then in the Comments group, select Next.  Excel saves you scrolling through lots of data to find notes, by whizzing you through the document and locating the notes you need.

Printing comments in a worksheet
It can help when reviewing an Excel worksheet in a meeting, if you can have a print out of the sheet with the notes at the bottom of the sheet, or where they appear on screen. If you hit Print, the comments don’t appear.

Go to the Page Layout tab and select the Page Set Up, and select the down arrow next to Comments.  This is where you can select the option that fits your needs.  Now when you print your document, you have access to the comments.

Page-set-up-print-comments-training-courses-in-excel
You can set up how you want the comments to appear in your hard copy.

With comments, you have the flexibility to add extra details to your Excel worksheet, without it taking up lots of space.  It is easier to share comments with colleagues and get more out of your data.  Consider training-courses-in-excel for more Excel skills https://www.stl-training.co.uk/excel-2010-intermediate.php