Excel Training – Page 67 – STL

Here is a quick tip for customising your Excel worksheet.

I like my Excel worksheets to have alternate shading or colours so that it is easier for me to read across rows.

Now, I could set these up manually, by shading in my rows, but if I use sort, or add new rows, the shading will not be alternate. I will get some kind or random stripe effect. What I want is Excel to do, is automatically apply shading to each alternate line, even if I add, or remove a row.

So here is a quick way to set it up

I have a list of students who have all taken an Excel exam.

student list plain format excel intermediate training — Here is my list of students. Plain format, so easy for me to read across lines incorrectly.

To make it easier for me to read, I want to add shading on alternate rows.

I select the information I want to shade, including the header, and go to the Home tab, and select Format as Table and select a colour scheme from the options given.

format-table-in-excel-intermediate-training — The Format Table button offers loads of ready made formats to choose from.

I then select the colour scheme I want, and I get a message from Excel confirming the cell range, and whether my list includes headers. My list does, so I click ok.

format-table-message-excel-intermediate-training — Excel prompts me to check that my cell range is correct and to confirm if my selection has a header row.

I can then click out of the selection range and Excel has applied my colour scheme. I can now add student details, or delete rows, and Excel will automatically update the format for me.

I have to admit, I’ve been wanting to be able to do this for ages. One day of intermediate excel training boosted my skills and made it far easier to use the data in my worksheets. If you are feeling curious and want to learn more, take a look at our courses https://www.stl-training.co.uk/excel-2010-intermediate.php.

You may have come across the situation where you have a list of names in Excel, with each name in a single cell and you want to separate out the first name and surname into separate cells. You can do this with the Text to Columns feature but this has limitations. Alternatively you can do this using Excel text functions without these limitations.

Text to Columns

For example, you have a list of staff names in an Excel spreadsheet. The list is in a column of data and each cell contains the first name AND surname, separated by a space. To use Text to Columns, select the cells containing the names, then choose Text to Columns.

In Excel 2003 choose Data, Text to Columns.

In Excel 2007/2010 choose the Data tab, then Text to Columns. In the Text to Columns panel you’ll see the text previewed. Click Next and then choose the Space separator. Note you can select different separators such as commas, tabs or characters. You’ll see that as you choose the Space separator, Excel previews the data now correctly separated. Then click Next and then Finish to complete. Excel replaces the original cells with two columns, one containing the first name and the other containing the surname.

However there are limitations to using Text to Columns.

You can only use this feature one column at a time, and run the wizard each time.
You cannot change where the data ends up – the original data is always replaced.
Excel will overwrite any data in the column to the immediate right of the original data (following a prompt) because it needs this for the surname, in our example.

Using Excel Text Functions

You can achieve the same results, keep the original data, place the resulting data where you want and not overwrite any existing data by using combinations of the four Excel text functions, Find, Left, Right, and LEN. We’ll start with the Find function.

The find function is used to find the position of one or more characters within a piece of text. In our example we want to find the position of the blank space in the original data. In Excel we represent the blank by a space surrounded by quote marks.

Suppose the staff names list starts in cell D1. Cell D1 contains the label “Staff”. The actual data is in cells D2, D3, D4 and so on. You might like to type in “FIND” as a label in cell E1. Then in cell E2 type =FIND(” “,D2) and press Enter. Cell E2 now shows a 5, meaning the space is the fifth character from the left in the original data. You can then fill the formula down all the cells to show the blank space position for all the data. Next we’ll use the Left function, so type “LEFT” as a label in cell F1 and select cell F2.

The Left function will select a specific number of characters from the left hand side of data in a cell. So in cell F2 type =LEFT(D2,E2-1) where D2 is the original data and E2 is the FIND value. After you press Enter the cell will show the first name only. Note we add a minus 1 to the character count so we only show the first name without the space. Then fill this formula down the table as before, and all the first names will show.

The LEN function is used to help with the character count as we prepare to select the surname from the right hand side of each item of data. So we’ll add the label “LEN” in cell G1. Then in cell G2 we type =LEN(D2) where D2 is the original data. After you press Enter the cell shows the total number of characters in the original data. Then fill the formula down as before, all the character counts will show. Now we’re nearly there. Lastly we’ll use the Right function, so we’ll add the label “RIGHT” in cell H1.

The Right function will select a specific number of characters from the right hand side of data in a cell. We’ll use the LEN value minus the FIND value to determine how many characters to select from the right. So in cell H2 type =RIGHT(D2,G2-E2) where D2 is the original data, G2 is the LEN value and E2 is the FIND value. After you press Enter the cell will show the surname only. Then fill down as before to produce a list of all the surnames.

We’ve seen so far how to break up the actions of separating two words separated by a space character into different steps. Once you’re familiar with how to use these functions, we could combine the formula and only show two extra columns to the right of the original data, with one for the first name and the other for the surname. You might like to try this and see how you get on.

Microsoft Excel training is an effective way to increase your skills and knowledge at work. Take a look at how much you can learn at https://www.stl-training.co.uk/microsoft/excel-training-london.php