Support Center

Advanced Spreadsheet Tricks

Last Updated: Feb 23, 2017 11:52AM EST
This page describes some of advanced things you may need to do inside Excel in order to get a spreadsheet into working order. Follow a link below to learn some spreadsheet tips on how to:

Turn any one column into two (or more) separate columns
Replace text throughout an entire column
Convert a column of capitalized text into title case
Use a formula to create a full sentence using multiple columns


Breaking Up Columns

Occasionally you may have a spreadsheet that has one column that needs to be split into two (or more) columns. If a column contains students' full names or multiple email addresses, it may need to be broken up.

This example contains a column of student full names (instead of separate columns for both first and last names):

First create at least one new column immediately after the column that is to be changed.

Once the column(s) are in place, highlight the column you need to break up, and click on the Data tab at the top. Select the Text to Columns option.

Choosing the Delimited option allows will break up a column according to the characters in the spreadsheet, such as spaces, quotes, ampersands, etc. In this example, select the Space option: 

Clicking Finish will separate the original column into two columns. In this case, every time a space would have appeared a new column was created.

The first and last names are now in separate columns. 


Replacing text in bulk

Occasionally a spreadsheet may include text that will need to be altered before it's used in an achievement. For example, many schools use standardized abbreviations for class level, gender, major and degree. We recommend against using these abbreviations or codes in a spreadsheet, as most readers won't know what these abbreviations stand for.

You can use Excel's Global Replace feature to transform these abbreviations into easy-to-use text.

For this example, we'll use a spreadsheet with abbreviated degrees and majors:

Here you'll notice that students have a column labeled MAJOR and inside this column are shortened codes for the degree and major the person has earned. To convert these columns into standard text, highlight the column and press Control+H. This will open up a global replace search box:

Type a code, such as BA-COM in "Find what:" and include the appropriate information it should be replaced with, such as Bachelor of Arts in Communications as shown above. Clicking on the Replace All button will display how many instances of this text has been replaced in the document

This process can be repeated as many times as necessary to ensure all data is changed.

Converting Case

The text inside a spreadsheet will always be inserted into achievements and press releases exactly as it appears in the spreadsheet. 

In this example, the First and Last Name fields are written in title case, but the City field is written in uppercase. Using this data, a sentence in an achievement would read:

Erik Collins of BAKERSFIELD, CA was named to the Dean's List at PR University.

The text in all caps stands out from the rest of the sentence, and makes the student's hometown look out of place. Thankfully, there's an easy way to change the case of text inside your spreadsheet.

First, create an empty column in the spreadsheet.

Next, enter the formula =PROPER into the empty cell, and click on the cell that needs to be changed. 

In this example, the formula tells Excel to convert the contents of cell C2 to proper (or title) case.

There are three ways to convert text:

=PROPER - Converts all text to title case by capitalizing the first letter of each word.
=UPPER - Converts all text to uppercase
=LOWER - Converts all text to lowercase

Pressing enter will convert the text into the desired format:

Then, copy and paste the formula into every cell in the column. 

Right now, even though Albany is listed in cell D2, and Bakersfield in cell D3, the actual contents of these cells are =PROPER D2 and =PROPER D3

To remedy this, highlight the column and copy it's contents.

Then paste this text back into the same column (or another empty column in the spreadsheet) by using Excel's Paste Special feature.

This will open up a box requesting the format for the data. Select the Values option.

This will paste the actual converted text into the spreadsheet instead of the title case formula. 

Using Formulas

We encourage users to include full sentences of information in your spreadsheet. This is a good way to include details about some students and not about others.

Spreadsheets don't usually come with full sentences in them, so you may need to create them manually using formulas. In this example, some students have parent information and others do not: 

First, create an open column in the spreadsheet. Then, sort the spreadsheet so that all of the rows with parent data are at the top; this makes copying and pasting easier.

To create the sentence, you will use a formula in the spreadsheet to pull data from other cells into the newly created column.

In this case, "&B1&" will tell Excel to use at the contents of cell B1. You can also add additional text, spacing and punctuation to the formula to turn it into a complete sentence. 

The formula used here is: =B2&" is the "&H2&" of "&F2&" of "&G2&".

After pressing enter, the formula pulls the data from the sheet, creating the sentence: Patrick is the daughter of Bernard Patrick of Lubbock.

Then, copy the contents of this cell and paste it into the cells where the formula should be applied.

Each row will become populated using the data from the corresponding columns.

However, at this time the contents of these cells are formulas and NOT the actual results. In order to change the text to the actual values, copy the contents of the column and then right click and choose the Paste Special option.

Choose the Values option, which will paste the actual text into the column.

Now the spreadsheet will contain a full sentence of text that describes the students' parents.

Contact Us
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found