Mastering Data Cleaning and Sorting in Microsoft Excel

Slide Note

Delve into the intricacies of data cleaning and sorting in Microsoft Excel. Understand the importance of clean data and explore essential techniques for organizing and preparing your data for analysis. Discover efficient ways to utilize Excel's features for data cleansing and how sorting can enhance your data visualization and decision-making processes.

jenson Follow

Uploaded on Aug 03, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

Beyond the Basics of Microsoft Excel Lone Star College HEAPCON February 26, 2021 Presented by: Kim W. Anderson LSC-University Park

Is your Data Clean? Beyond the Basic Topics Sorting Data Auto Fill Splitting Data Frequently Used Functions

MS Excel: Data Spreadsheets are composed of columns and rows that create a grid of cells. As we discussed in part 1 of this class, Typically, each cell holds a single item of data. Here's are three types of data most used in spreadsheet programs such as Excel: Text Data Number data or values is used in calculations Formulas or mathematical equations Data vs. Clean Data The backbone of any analysis done in Excel is based on data. When it comes to data, there are several things that can go wrong. From structure, placement, formatting, and so on. Also, misspelled words, stubborn extra spaces, unwanted prefixes, improper cases, and nonprinting characters. And this is not necessarily a complete list of ways data can become, let's say, dirty.

MS Excel: Data Before you can analyze the data, you may often need to clean it up. Fortunately, Excel has features to help get data in the format needed. Sometimes, the task is straightforward and there is a specific feature that does the job. For example, you can easily use Spell Checker to clean up misspelled words in columns that contain comments or descriptions. You may also need to remove duplicates if they exist. Some of the basic steps for cleaning data are: Import the data from an external data source. Create a backup copy of the original data in a separate workbook or another worksheet in the workbook. Ensure that the data is in a tabular format of rows and columns with similar data in each column, all columns and rows visible, and no blank rows or columns within the range. Do tasks that don't require column manipulation first, such as spell-checking or using the Find and Replace dialog box. Next, do tasks that do require column manipulation.

MS Excel: Sorting Sorting data is an integral part of data analysis. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions. Sorting requires the use of a sort key and a sort order. The sort key is the data in the column or columns you want to sort and is identified by the column heading or field name. Options for Sorting can be reached several ways: Data Ribbon Right Mouse Click Home Ribbon

MS Excel: Sorting To sort multiple columns of data, select the cells that you want to sort, then select the Data Ribbon Tab. On the Data Ribbon tab, under Sort & Filter, select sort. The sort box will open, and you can select your options. Use Ctrl-A to select Data

MS Excel: Sorting You can also sort data using the same built-in custom list that Excel uses to add days or months to a worksheet using the Auto Fill. These lists allow sorting by days or months chronologically rather than in alphabetical order. If one were to need to sort the following data by Order Month, just using the A-to-Z sort, would put the months in alphabetical order. Using a pre- defined custom sort, would put the months in chronological order.

MS Excel: Auto Fill Excel has an Auto Fill feature to make it easier by automatically filling in data for you. Auto Fill goes beyond simple incrementing numbers or dates which is what most of us do. Auto Fill can be used for any type of data that fits a regular pattern. Auto Fill is a feature that allows you to copy text, numbers or even formula in a spreadsheet. It can be done by following several simple steps. Content is copied. Type in the content and position the cursor on the lower-right corner of the cell until it becomes crosshairs. Click and drag to copy the content. Click on the drop down arrow.

MS Excel: Auto Fill Aside from copying data to adjacent cell, Auto fill can also create series. If you need this option, you will just need to select the right option from the Smart Tag. Type in the content and position the cursor. Click on the drop down arrow. Click and drag to copy the content. There are many ways to use the Auto fill option in Excel. You just need to follow certain steps and choose in the Smart Tag options your desired output whether to copy the cells, fill in the series, copy the formatting or fill cells without formatting.

MS Excel: Splitting Data Data stuffed into one column? Split it. Split the content from one cell into two or more cells through Flash Fill or based on Delimiters From This To This

MS Excel: Splitting Data Flash Fill In the cells under First name, type the first names that are in the Email column: Nancy, Andy, and so on. When you see the faded list of suggestions, press Enter right away. This list of suggestions is called Flash Fill. Flash Fill detects when you type a consistent pattern and provides suggestions to fill the cells with. When you see the faded list, that's your cue to press Enter.

MS Excel: Splitting Data Based on Delimiters Select the cell or cells whose contents you want to split. On the Data tab, in the Data Tools group, click Text to Columns. The Convert Text to Columns Wizard opens. Choose Delimited if it is not already selected, and then click Next. Select the delimiter or delimiters to define the places where you want to split the cell content. In the Column data format area, select the data format for the new columns. By default, the columns have the same data format as the original cell. Click Finish.

MS Excel: Frequently used Functions Some of the most used Excel functions are: Sum Use it to add up a range of numbers. Average Use it to return the average of a range of numbers. CountIf (Conditional) Use it to count the number of cells that meet a criterion. SumIf (Conditional Mathematical) Use it to sum the values in a range that meet specific criteria.