RSS

Category Archives: Excel

How do I count the number of times a word appears in my document?

I was asked this question during the week, so here’s how to count how many times a particular word appears in a document (or spreadsheet or anything).

The easiest way to count the number of instances of a word is to use the Find function.

Access Find using Control-F (press the control key and F at the same time).

Type in the word you want to search for.

Word will find and highlight all instances of the word and highlight them for you – and will tell you how many times it appears!

Count instances of a word

Note: this search for transcription will find that word buried in other words, too – so TRANSCRIPTIONs and TRANSCRIPTIONist.

To find just the single word transcription, you need to use Advanced Find.

Click on the down arrow next to the search box and then choose Advanced Find:

2 Count instances of a word

Click the More button (which appears where Less is showing here) and then tick the box marked Find Whole Words Only:

3 Count instances of a word

Now Word will count and highlight just the instances of this exact word.

This article has taught you how to count how many times a particular word appears in your document. You can use this method in Excel and PowerPoint, etc. too.

If you’ve found it useful, please click like and share it. Thank you!

Other useful posts on this blog

How to search for anything using Control-F

How to count the words in your Word document

How to count the words in your PowerPoint presentation

Find and Replace

Advanced Find and Replace

 
2 Comments

Posted by on August 3, 2016 in Excel, PowerPoint, Word, Writing

 

Tags: , , , , ,

How do I change a column into a row or a row into a column in Excel 2007, 2010 and 2013?

In this article, I’m going to explain how to change a column into a row or change a row into a column in Excel 2007, 2010 and 2013 (I think this works in 2016 too but have not yet tested it).

Why would I want to swap a column for a row?

You might start off creating a header row then decide it should be a column, or create a spreadsheet then want to rotate it 90 degrees. Or, worse, someone might decide that for you and expect you know how to do it!

How to change a column into a row or rotate a block of cells

Here’s our original block of cells:

Swap excel rows for columns

We want to turn this around so that everything runs along the top rather than down the side. Here’s how to do it:

First, copy all the cells you want to move:

Highlight the cells, right-click with the mouse, and select Copy:

copying cells in excel

Now, and this is important, find a free, empty cell to paste into. Don’t worry about it being in the middle of the spreadsheet, we will tidy that later.

Right-click with your mouse in an empty cell and then choose Paste Special and Transpose (a hint will appear when you hover over the button, but it’s the one on the bottom right)

How do I swap columns and rows in Excel

Behind all those dialogue boxes, Excel will show you what this is going to look like.

Click on Transpose and your cells will appear, starting from the empty cell you clicked on:

swapped columns and rows

Your original cells are still there – so highlight their columns, right-click with the mouse and choose Delete:

Flipped cells in Excel

And here’s your spreadsheet, the opposite way around from how you started!

5 finished

In this article, we’ve learned how to change rows into columns and change columns into rows in Excel 2007, 2010, 2013 and probably 2016.

If you’ve found this article helpful, please do post a comment below, and if you think others would find it useful, please share it using the sharing buttons below the article. Thank you!

Other useful posts on Excel on this blog:

How to view two workbooks side by side in Excel 2007 and 2010

How to view two pages of a workbook at the same time

How do I print the column headings on every sheet in Excel?

How to print the column and row numbers/ letters and gridlines

Freezing rows and columns in Excel – and freezing both at the same time

How to flip a column in Excel – turn it upside down but keep the exact same order!

 
3 Comments

Posted by on June 8, 2016 in Excel, Short cuts

 

Tags: , ,

How do I flip a column in Excel 2007, 2010 and 2013?

In this article, I’m going to explain how to flip a column in Excel 2007, 2010 or 2013 (using screen shots from Excel 2013). Flip a column is what I searched for – you might have asked how to reverse a column or put a column in reverse order.

Note: I was a bit surprised by the solution to this one, but I’ve asked the experts and it is the only way to do this. If you have a better and simpler way (that doesn’t involve macros or coding) please pop a comment on this article or contact me!

Why would I want to put a column in reverse order?

I’m doing my accounts at the moment. I have a spreadsheet of my bank transactions which runs from the newest at the top to the oldest at the bottom. My list of invoices runs from the oldest at the top to the newest at the bottom. If I want to compare them, I want them to both be the same way around, but I need the bank transactions to be in exactly the same order, just the other way around.

Will using Data > Sort flip my column?

The usual way to change the order of columns in Excel is to use the Data > Sort function. However, if you sort by transaction date, it won’t necessarily sort it into the same order the other way around.

For example, we have a set of bank transactions which I’ve named in alphabetical order down the client column to make it easier to see what happens next:

1 spreadsheet

If I highlight all of the columns, go to the Data tab and choose to sort by date, older to newer, I get this result, which is NOT in exact reverse order:

1a spreadsheet sorted by date

So, what’s the solution? I was a bit surprised when I searched and searched for the answer, but it is the only way to do it …

So, how do I flip my columns so they’re in exact reverse order?

To do this, we need to create an extra column to sort by, and then reverse sort by that rather than date or any other column.

First, create a new column called sort and fill it with numbers from 1 to whatever your total number of rows is:

2 sort column

Expert tip: rather than typing these numbers manually, if you have a lot of rows, and to avoid errors, you can create a quick formula to insert the numbers automatically. Type 1 in the first row then the formula you can see below in the next row, where you get the F2 by clicking on the cell containing the 1:

2a sort column

Then, copy the cell with the formula (right-click, copy), highlight the rest of that column down to the last row and right-click, paste. This will give you the same effect. Note, though, when you sort by this column, the numbers will turn into rows of #####, BUT the sort will still work OK.

Once you have your additional Sort column, you are ready to reverse your columns.

Highlight all of the data and, in the Data Tab, choose Sort:

4 sort data

In the Sort Dialog Box, choose to sort by the Sort column, and from Largest to Smallest (i.e. the reverse order to its current order):

5 sort data

Press OK and hooray – your spreadsheet is sorted into exact reverse order. Just delete the now-redundant Sort column (highlight, right-click, delete):

7 delete sort column

and here’s your bank transactions in reverse order – you have flipped the column!

6 data sorted

This article has explained the (slightly surprising) way to flip the columns in an excel 2007, 2010 or 2013 spreadsheet, not using data sort, but another message. If you need to reverse the order of your columns exactly, then this is the way to do it.

If you’ve found this article helpful or if you have a better solution, please do post a comment below, and if you think others would find it useful, please share it using the sharing buttons below the article. Thank you!

Other useful posts on Excel on this blog:

How to view two workbooks side by side in Excel 2007 and 2010

How to view two pages of a workbook at the same time

How do I print the column headings on every sheet in Excel?

How to print the column and row numbers/ letters and gridlines

Freezing rows and columns in Excel – and freezing both at the same time

 
6 Comments

Posted by on May 25, 2016 in Excel, Short cuts

 

Tags: , ,

How do I print the gridlines in my spreadsheet in Excel 2007, 2010 and 2013?

When you’re printing an Excel spreadsheet, how do you make the gridlines print, too?

This article tells you how to print the gridlines automatically, working in the Page Layout Tab, in Excel 2007, 2010 and 2013 (screen shots are taken from Excel 2010).

With your spreadsheet open, go to the Page Layout tab and look to the right to find the Sheet Options area:

print headings

In this area, you can tick Print to make the gridlines appear when you print out the spreadsheet.

There are two things to note here:

  1.  You can also untick the View boxes so you can’t see the gridlines at all. I’m not sure why you might want to do this, but there it is.
  2. If you have used the borders option already to draw borders around some cells, if you print without ticking Print Gridlines, the borders you have added will print anyway; if you tick Print Gridlines, all of the gridlines and borders will print.

Adding customised borders to cells

A quick reminder on adding borders:

Click on the cell(s) you want to add borders to. Click on the Borders drop-down in the Home Tab, Font area:

apply borders excel

then choose where you want your borders to go:

border options excel

More sheet options

You will see that there’s a little arrow in the bottom right corner of the Sheet Options area:

sheet options

Click on this arrow and the Page Setup dialogue box opens – here you can change a few more options, too, or set your headings to print if you want to, as well as going into the other tabs to change the orientation or margins, etc.:

sheet options print gridlines

And that’s it – now you can print the row numbers and heading letters in Excel 2007, 2010 or 2013!

If this has been helpful, please comment below or share the article using the buttons. Thank you!

Related posts on this blog

How do I print the row numbers and column letters in my Excel spreadsheet?

How do I print the Excel header row on every page of my spreadsheet printout?

How do I print the Word header row on every page of my table printout?

 
6 Comments

Posted by on March 23, 2016 in Excel

 

Tags: , , ,

How do I print row numbers and heading letters in Excel 2007, 2010 and 2013?

When you’re printing an Excel spreadsheet, how do you make the row numbers and heading letters print, too?

This article tells you how to do this, working in the Page Layout Tab, in Excel 2007, 2010 and 2013 (screen shots are taken from Excel 2010).

In your spreadsheet, go to the Page Layout tab and look to the right to find the Sheet Options area:

print headings

In this area, you can tick Print to make the headings (or, indeed, the gridlines) appear when you print out the spreadsheet. Note that if you want to, you can also untick the View boxes so you can’t see the headings. I’m not entirely sure why you wouldn’t want to see those, but the option is there.

You will see that there’s a little arrow in the bottom right corner of the Sheet Options area:

sheet options

Click on this arrow and the Page Setup dialogue box opens – here you can change a few more options, too, or set your headings to print if you want to, as well as going into the other tabs to change the orientation or margins, etc.:

sheet options print headings

And that’s it – now you can print the row numbers and heading letters in Excel 2007, 2010 or 2013!

If this has been helpful, please comment below or share the article using the buttons. Thank you!

Related posts on this blog

How do I print the Excel header row on every page of my spreadsheet printout?

How do I print the Word header row on every page of my table printout?

 
3 Comments

Posted by on March 17, 2016 in Excel

 

Tags: , , ,

How do I view two sheets of an Excel workbook at the same time?

You’re looking at an Excel spreadsheet workbook which has more than one individual sheet, accessible via clicking on tabs at the bottom of the workbook. This article shows you how to view two different sheets in the same workbook side by side on the page. This article is valid for Excel 2007, Excel 2010 and Excel 2013. It follows on from my article on different ways to view multiple spreadsheets at the same time, and you may wish to refer to that article for further details on the options.

How do I view multiple Excel workbook sheets side by side?

First of all, open up your workbook. You can view different sheets of the workbook by clicking on the tabs at the bottom:

Excel tab to view sheets

However, when you do this, the new sheet is displayed in the window, and you can’t see Sheet1 any more. To be able to view both (or more) at the same time, you will need to create a new window containing the second sheet, and then display them next to each other.

How do I create a new duplicate window in Excel?

First, create a new window. Go into the View tab then click on the New Window button:

Excel new window

This will generate a new window, on top of the first one and identical to it (you can check that you have two open by clicking on the Excel button at the bottom of the screen:

Excel check two files are open

Now, in the window you have just created, click on the tab for the second sheet that you wish to view (in this case, Sheet2):

Excel tab to view sheets

This will display Sheet2 in the new window:

Excel view sheet2

If you want to view more than two sheets, follow this process for each additional sheet that you wish to view.

What are my options for viewing multiple sheets of one workbook in Excel?

Once you’ve got two windows, one displaying the first sheet and one displaying the second, you can view them side by side, or in tiles, or however you choose. In the View tab, click on either View Side by Side or Arrange All to select your options (see this previous article for details on all of the options):

Excel choose options for displaying multiple sheets

Note that if you choose Arrange All, you must make sure that you tick Windows of active workbook:

Excel view multiple sheets active workbook

In this case, I’ve chosen Arrange All – Vertical, and here are my two sheets of my workbook, displayed next to each other:

17 view multiple sheets in a workbook

For details of all of these options and what they do, please see my post on viewing multiple spreadsheets at the same time.

How do I get back to viewing only one sheet at a time?

If you want to return to a full-screen view of a particular spreadsheet, simply double-click on the title bar of your spreadsheet (by its name) and it will expand and be the only one visible:

return to single sheet view

In this article, we’ve learned how to view two or more sheets belonging to one Excel workbook on the screen at the same time, and how to return to a single sheet view.

If you’ve enjoyed this article and found it useful, please take a moment to share it using the buttons below!

Please note, these hints work with versions of Microsoft Excel currently in use – Excel 2007, Excel 2010 and Excel 2013, all for PC. Mac compatible versions of Excel should have similar options. Always save a copy of your document before manipulating it. I bear no responsibility for any pickles you might get yourself into!

Find all the short cuts here … and view the blog resource guide here.

Other useful posts on this blog:

How do I view two Excel spreadsheets at a time?

 
43 Comments

Posted by on September 24, 2014 in Excel, New skills, Short cuts

 

Tags: , , , , , ,

How do I view two Excel spreadsheets at a time?

You’re looking at a spreadsheet and you want to compare it to another one. In Word, it’s easy to line up two separate documents side by side to look at them both. In Excel – not so easy. This article explains how you can view two Excel spreadsheets next to each other on your screen and compare the two spreadsheets easily (or more, if you want!). Next week, we’re looking at how to view two sheets from the same workbook side by side, too! This article is valid for Excel 2007, Excel 2010 and Excel 2013 to some extent. The problem doesn’t exist in Excel 2013 as you can move spreadsheets around just like you can in Word, however the options still exist for arranging your multiple views (thanks to Alison Lees for pointing out the resolution of the problem).

Why can’t I view two Excel files on the same screen?

If you’re used to working with Word, you’ll know that if you have two Word documents open in any version of Word, you can pick them both up by the top bar (I usually do it near to the name of the document), slide them across to the left and right until they ping back and fill half of the screen …

Two Word documents on one screen

… and end up with two documents next to each other (you can, of course, move the boundary between them to make one bigger and one smaller):

Two Word documents showing side by side

But, if you’ve ever tried to do this with two Excel spreadsheets, you’ll have found that you move one over …

Moving an excel spreadsheet

… and the other one moves to sit underneath it, inaccessible and impossible to view at the same time as, say, Spreadsheet 1:

Second spreadsheet hidden

Move Spreadsheet 2 across to the right and Spreadsheet 1 will follow it. Grrr!

I’m going to show you how you can view both (or even lots of) spreadsheets on the same screen, in various arrangements, and then return to viewing only one. And next week I’ll show you how you can view two sheets from one workbook side by side.

The quick way to view two spreadsheets side by side

We’re going to look at the View tab here. In the View tab, you’ll find a button labelled View Side by Side.

view side by side excel

If you have two spreadsheets open in, say, Excel 2010 (from which these screenshots are taken, but the process is the same for Excel 2007, 2010 and 2013), pressing this button will show you both spreadsheets, one above the other (this always reminds me of playing competitive driving games on the games console):

View side by side - result excel

You can see that the Synchronous Scrolling button is highlighted in the image above. This is a really useful function – if you have both spreadsheets lined up to start with (i.e. you can see Column A and Line 1 at the top left of both), if this button is showing in yellow, scrolling in one spreadsheet (the active one has the scroll bar) will move the other spreadsheet up and down or left and right at the same time:

view side by side synchronous scrolling

However, if you don’t want to use this feature, you can click on the Synchronous Scrolling button to turn it white, and then your two spreadsheets can be scrolled independently (the scroll bar still displays on the active spreadsheet, i.e. the one you’ve clicked on):

view side by side remove synchronous scrolling

Note that synchronous scrolling only works in this View Side by Side option, so if that’s important to you, choose this option.

But what if you want to view the sheets side by side, or more than two in a tiled layout (I’ve got a widescreen monitor so I always want to view side by side)? Read on for that option  …

How do I view two spreadsheets next to each other or in a tiled layout?

To view your multiple spreadsheets arranged next to each other, to swap to the horizontal view we just looked at, or to use the cascade option, stay in the View tab and the same area but click on the Arrange All button:

excel arrange all button

This will give you a range of options for displaying the spreadsheets that you currently have open:

excel arrange all button options

Let’s look at these in turn …

Arrange all – Tiled

If you have two spreadsheets open, the Tiled option in Arrange All will simply show them arranged vertically, i.e. next to each other. All of my other examples feature two spreadsheets, but to demonstrate the Tiled option, here are four spreadsheets:

excel arrange all Tiled option

Note that the spreadsheets arrange themselves in the order in which you have them open, so if Spreadsheet 4 is the last one you looked at, that will appear top left. You can expand and move the individual spreadsheets, then return to Arrange All – Tiled to click them back into position again.

Arrange all – Horizontal

If you choose the Horizontal option in Arrange All, your spreadsheets will appear on top of each other, with the split between them horizontal:

12 arrange all horizontal

Note here that I had Spreadsheet 2 active (visible) when I chose this option, so it appears at the top. To choose which one appears at the top, have that particular spreadsheet visible and active when you click on the Arrange All button.

Arrange All – Vertical

Choosing the Vertical option in Arrange All gives you the two (or more) spreadsheets arranged next to each other, with the split between them vertical:

excel arrange all vertical option

This is how I prefer to view them.

Arrange All – Cascade

I find this one a bit odd. When you choose the Cascade option in Arrange All, the windows containing the individual spreadsheets all appear on top of each other, with a little bit of one poking out from underneath the active one. Here, Spreadsheet 1 is just showing at the top, but if I click on Spreadsheet 1, Spreadsheet 2 will be sticking out at the bottom. It’s odd, but there must be a reason for it, or Excel wouldn’t offer it:

excel arrange all cascade option

How do I get back to viewing only one spreadsheet at a time?

If you want to return to a full-screen view of a particular spreadsheet, simply double-click on the title bar of your spreadsheet (by its name) and it will expand and be the only one visible:

excel return to single sheet view

In this article, we’ve learned how to view two or more Excel spreadsheets on the screen at the same time, and how to return to a single spreadsheet view.

If you’ve enjoyed this article and found it useful, please take a moment to share it using the buttons below!

Please note, these hints work with versions of Microsoft Excel currently in use – Excel 2007, Excel 2010 and Excel 2013, all for PC. Mac compatible versions of Excel should have similar options. Always save a copy of your document before manipulating it. I bear no responsibility for any pickles you might get yourself into!

Find all the short cuts here … and view the blog resource guide here.

 
32 Comments

Posted by on September 17, 2014 in Excel, New skills, Short cuts

 

Tags: , , , , , ,