Searching and Manipulating Data in Excel

In today's world numbers are present in every domain, be it accounting, education, agriculture, etc. People working in domains related to computers are known to use sophisticated tools. However, Excel comes in very handy for even non-technical users if they need to perform basic calculations or maintain records.

This guide will go over how to search and manipulate data in Excel and cover topics such as:

• Changing the text case
• Sorting and filtering data
• Combining data using the concatenate function
• Pasting data into cells
• Find and replace

You may get text in your spreadsheet in any format. To change the text case in Excel, first consider these records:

As you can observe in the above table, all the statements begin with a capital letter (the exception being keywords like Windows, Android, etc.) and the rest of the words are in lowercase. This guide will explain how to perform changes to the text.

You'll start with the PROPER function, which converts first letter of all the words into uppercase as shown:

=PROPER(select_cells)

*Output:

Next, there are two other functions: LOWER and UPPER. The implementation along with their results has been shown below:

=LOWER(select_cells)

Output:

=UPPER(select_cells)

Output:

Consider a data set from UCI Machine Learning Repository named Abalone. The first 10 rows of the data set will be used to teach you how to perform sorting and filtering of data in Excel.

The following image depicts how sorting and filtering icons looks in the Excel menu bar:

The first step is to select the table and click on the Filter icon. This will bring a drop down menu icon on each of the column headers.

Let's say you want to sort these records based on the gender labels. Click on the drop-down arrow of the Gender column and select Sort A to Z. This will result in the following table:

The records with label F (Female) will appear at the top of the list, followed by I (Infant) and M (Male). If you just want the records corresponding to Females, unselect all and then select the label F under the Gender drop-down menu. That will result in the following table:

Suppose you have a data set such as as the one given below:

And you need to arrive at the given result in a new column:

What will you do to achieve this task?

You may have to manually copy the values from a cell and paste it into the final one, not to mention the need to put the proper spacing and a comma right after the last name. To make this process easier, Excel has a function called CONCATENATE. You can use this function and complete this task in less than a minute. The syntax goes like this:

=CONCATENATE(A2, " ", B2, ", ", C2)

A proper spacing, a comma and the cell names. This results in the final column as expected.

In Excel, when you copy data from one cell and try to paste it into a different cell, a lot of manipulations can be performed. For instance, in the previous section you learned how to implement the CONCATENATE function, so assume you have written the function in cell E1 and now you want to copy the result of the function into a different cell.

If you use keyboard shortcut Ctrl+V to paste, it will paste the formula, not the result itself. However, you can right click on the cell and the paste section will show a lot more options to explore. You can paste the formula itself, the result as-is, transpose the result, take care of the formatting, etc. Try to explore these options as shown in the below image:

Consider the Abalone data set with 10 records. To perform a search and replace task in Excel, you can use the keyboard shortcut Ctrl+F, or you may select the entire range of options available under the icon named Find and Select present at the extreme right hand side of the menu bar.

Open the Find and Replace dialog box using the Ctrl+F keys and search for a number 0.1415. This number is present under the Viscera column. Clicking on the Find button again and again will take you through all the cells which contain this number. To replace a value once you have found it, go to the Replace tab under the same dialog box and enter the value to be found and the value it needs to be replaced with.

There's also an option for conditional formatting, where you can find cells based on certain conditions and highlight the ones which are detected. To understand this concept, take a look on the given table:

So if you want to see how many user IDs are above the value 1000, select the ID column, go to Conditional Formatting, Highlight Cells Rules, and Greater Than.... Select the value 1000 and press OK.

This will highlight the 2nd (9842), 7th (6878), 8th (6201) and 9th (5477) row values.

In this guide, you have learned how to manipulate data in Excel at a beginner level. To learn more on the same topic, refer to the Pluralsight Searching and Manipulating Data in Excel course.

• Visualizing Data with PivotChart: Part 2
• Calculating Cell Values with Formulas
• Formatting Excel Worksheets and Cells