Author avatar

Chhaya Wagmi

Searching and Manipulating Data in Excel

Chhaya Wagmi

  • Apr 1, 2020
  • 12 Min read
  • 770 Views
  • Apr 1, 2020
  • 12 Min read
  • 770 Views
Business Professional
Productivity Apps and Client OS
Office Applications
Microsoft Excel

Introduction

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

Changing Text Case in Excel

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

Statements
Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS.
It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
Microsoft Excel has the basic features of all spreadsheets.
Excel forms part of the Microsoft Office suite of software.

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:

Statements
Microsoft Excel Is A Spreadsheet Developed By Microsoft For Windows, Macos, Android And Ios.
It Features Calculation, Graphing Tools, Pivot Tables, And A Macro Programming Language Called Visual Basic For Applications.
Microsoft Excel Has The Basic Features Of All Spreadsheets.
Excel Forms Part Of The Microsoft Office Suite Of Software.

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

=LOWER(select_cells)

Output:

Statements
microsoft excel is a spreadsheet developed by microsoft for windows, macos, android and ios.
it features calculation, graphing tools, pivot tables, and a macro programming language called visual basic for applications.
microsoft excel has the basic features of all spreadsheets.
excel forms part of the microsoft office suite of software.

=UPPER(select_cells)

Output:

Statements
MICROSOFT EXCEL IS A SPREADSHEET DEVELOPED BY MICROSOFT FOR WINDOWS, MACOS, ANDROID AND IOS.
IT FEATURES CALCULATION, GRAPHING TOOLS, PIVOT TABLES, AND A MACRO PROGRAMMING LANGUAGE CALLED VISUAL BASIC FOR APPLICATIONS.
MICROSOFT EXCEL HAS THE BASIC FEATURES OF ALL SPREADSHEETS.
EXCEL FORMS PART OF THE MICROSOFT OFFICE SUITE OF SOFTWARE.

Sorting and Filtering Data in Excel

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.

GenderLengthDiamHeightWholeShuckedVisceraShellRings
M0.4550.3650.0950.5140.22450.1010.1515
M0.350.2650.090.22550.09950.04850.077
F0.530.420.1350.6770.25650.14150.219
M0.440.3650.1250.5160.21550.1140.15510
I0.330.2550.080.2050.08950.03950.0557
I0.4250.30.0950.35150.1410.07750.128
F0.530.4150.150.77750.2370.14150.3320
F0.5450.4250.1250.7680.2940.14950.2616
M0.4750.370.1250.50950.21650.11250.1659
F0.550.440.150.89450.31450.1510.3219

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

Imgur

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:

GenderLengthDiamHeightWholeShuckedVisceraShellRings
F0.530.420.1350.6770.25650.14150.219
F0.530.4150.150.77750.2370.14150.3320
F0.5450.4250.1250.7680.2940.14950.2616
F0.550.440.150.89450.31450.1510.3219
I0.330.2550.080.2050.08950.03950.0557
I0.4250.30.0950.35150.1410.07750.128
M0.4550.3650.0950.5140.22450.1010.1515
M0.350.2650.090.22550.09950.04850.077
M0.440.3650.1250.5160.21550.1140.15510
M0.4750.370.1250.50950.21650.11250.1659

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:

GenderLengthDiamHeightWholeShuckedVisceraShellRings
F0.530.420.1350.6770.25650.14150.219
F0.530.4150.150.77750.2370.14150.3320
F0.5450.4250.1250.7680.2940.14950.2616
F0.550.440.150.89450.31450.1510.3219

Combining Data Using the Concatenate Function

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

FirstLastDegree
AliceJohnsonMD
PeterRottingumMBBS
JamesTealPhD

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

Complete
Alice Johnson, MD
Peter Rottingum, MBBS
James Teal, PhD

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.

Pasting Data into Cells

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:

Imgur

Find and Replace

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:

IDUsername_hash
854U_sdfh
9842U_sfjje
578U_ktj
124U_bel
598U_kel
475U_bgk
6878U_lqo
6201U_qwn
5477U_mbq
56U_nflq

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.

Imgur

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

Conclusion

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.

More on Excel- [Visualizing Data with PivotChart: Part 1](https://www.pluralsight.com/guides/visualizing-data-with-pivotchart)

7