7.1.8: Entering, Selecting, Sorting, and Formatting Data

📌 Click here to view Microsoft Excel Setting a Foundation Chapter Overview

---

At its core, Excel is a grid of cells (rectangles identified by their column letter and row number, such as A1). Data entry begins by selecting a cell and typing either text, a number, or a date. Press Enter to move down one row or Tab to move across a row—an efficient way to input large lists.

Entering Data​

It is usually easy to add data to an existing worksheet, because Excel has seemingly unlimited columns and rows of cells. (There are limits, but this is not Jeopardy!) Nonetheless, sometimes it is necessary to insert a column or row into the middle of an existing range of cells. To insert a new column, select the column heading to the right of where you want the new column to appear, and click the top-half of the Insert button from the Home tab. Alternatively, just right-click the column heading to the right of the destination, and choose Insert from the Context Menu. Deleting columns is very similar. Whichever column(s) that are selected will be deleted if using the Delete button instead of the Insert button. Inserting and deleting rows is also very similar. Selecting the row below the intended new row and either choosing Insert from the ribbon or from the Context Menu will create a new row above the selection. For example, right-clicking row 10, and choosing Insert from the Context Menu will create a new, blank row 10, and all existing data will move down one row.

Shortcut menu Excel

Using the right-click method is a little faster than clicking the worksheet and then clicking the ribbon, as the shortcut menu avoids the need to move the mouse to the ribbon. The Context Menu, also known as Shortcut Menu, provides added functionality by offering actions that can be taken with the selected item. In addition to inserting and deleting, formatting column widths and row heights, as well as  hiding and unhiding columns and rows are options from the Context Menu. Adjusting the column width is a common action since excessively wide columns prohibit efficient worksheet printing. Therefore, it is often preferable to widen a column width by double-clicking the boundary bar that separates the column heading letters. This is known as auto-fitting the column width since the column will resize its width to be just wide enough to display the longest data contained in that column.

Column width pointer

Clicking and dragging the double-arrow pointer to the left or right allows users to manually adjust the column width.

To edit existing entries, you can:

• Double-click the cell to make inline changes.
• Click once and use the Formula Bar (above the worksheet grid) to edit longer text or complex formulas.

​Selecting Data​

After entering data, a common practice is to begin formatting the data. However, before a user can format the data, they must first select the data. Selecting data has multiple techniques based on user intent. Clicking on a single cell makes that cell the active cell. The cell reference appears in the Name box to the left of the formula bar. To select an entire column of data, just click the column letter heading. To select an entire row, click the row number heading. If a user wants to select several cells adjacent to each other, this would be considered selecting contiguous cells. Clicking the middle of the cell and dragging horizontally or vertically will select a contiguous range of cells. Another way to select a contiguous range of sequential cells is to click the first cell, hold down the Shift key, and click the last cell in the range. For example, clicking cell A3, and then holding down the Shift key and selecting F7 would select all of the cells from A3 through F7. The range reference would be written as A3:F7.

Contiguous Range

Selecting a non-contiguous range of cells requires the use of the Ctrl key. To select non-contiguous cells, click the first cell, hold down the Ctrl key, and click each additional cell. This practice is sometimes referred to as “cherry-picking” cells. In the example below, the selected range of cells are A4,A6,A7,F4,F6,F7. This practice can be used for selecting non-adjacent rows or columns as well.

Non-Contiguous cells

To select the entire worksheet, click the triangle box located to the left of column A and above row 1, or by pressing the keyboard shortcut: Ctrl+A.

Sorting Data

Worksheets can have large amounts of data, and it can be overwhelming if it is not sorted correctly. Arranging data in a specified order is called sorting. Rows can be sorted in either ascending (low to high) or descending (high to low) order. Ascending order can also be considered alphabetic order if the data is text or chronological order if the data is dates or times.

Before data can be sorted, Excel needs to know the exact range of cells that is to be sorted. Excel will select areas of related data as long as there are no blank rows or columns in the data range. Blank rows and columns define the outer limits of the data range. To ensure that the correct data is selected, highlight the range before starting the sort.

As Excel determines the defined range of data to be sorted, Excel also determines if field names exist. Users should format the first row of the data range with unique formatting Often, just making the labels bold will help Excel identify the field names. Identifying the field names will prevent Excel from including these fields in the records to be sorted. The field names become the sort keys needed for multiple column sorts. In the image below, the possible sort keys are: Cruise Date, Accommodation Category, Rooms Available, Rooms Rented, Rate, Revenue and Shortfall.

Sort Data Range

The data range above is already sorted in ascending order by the Accommodation Category key field. A single column sort is quite easy. Simply click any cell in the column that represents the key sort. In the above example, any cell from B4 to B28 would be acceptable. Then, click the the A-Z option  in the Sort & Filter group of the Data tab. The entire row is sorted because each row is a record that should stay grouped together. Be careful NOT to select a single column of data or the records may not be kept together. Excel will warn the user with a warning message. If the user chooses to Continue with the current selection, only the data in the column with change their order, while the data in the other columns remain unchanged. This will most likely destroy the integrity of the data.

Advanced sorting involves multiple key fields from multiple columns. A multi-level sort can be set up by clicking the Sort button from Sort & Filter group from the Data tab (or the Sort & Filter button from the Editing group of the Home tab and choose Custom Sort) to open the Sort dialog window. The first sort criteria, also known as the primary sort, was previously defined to sort by Accommodation Category in ascending order.

Multi-level sort Excel

To add a second level, also known as a secondary sort, click the Add Level button. After entering additional sort criteria, additional levels can be added. As shown in the graphic to the right, secondary criteria has been added to sort in descending order by Revenue. The data in column B will not appear to change, but the records will reorder. Compare the results below with the table above.

Secondary Sort

An Excel filter, also known as an AutoFilter, is used to display only the data that meet specific criteria. Data that does not meet the criteria is hidden from view. Excel’s basic filter functionality allows users to filter rows by value, by format and by criteria. Filters can reduce a large, complex worksheet of data into meaningful information. With filtering, you can control not only what you want to see, but what you want to exclude. Users can filter based on choices they make from a list, or they can create specific filters to focus on exactly the data that they want to see.

Applied Filter

Clicking the filter button from the Data tab will add drop-down arrows to the column headers in a range of data. Clicking a drop-down arrow provides sorting and filtering options, like filter by color or text filters.  A simple AutoFilter will be signified by the clearing of check boxes of the data that should be hidden. After applying a filter, a user can copy, edit, chart or print only visible rows without rearranging the entire list. Like sorting, filters can be applied to multiple columns to further reduce the results that are displayed.

A column that has been filtered will have its drop-down arrow icon switch from an upside-down triangle to a filter icon, as shown highlighted in yellow below. Only the cruises from the Penthouse accommodation category will be displayed after clearing all check boxes except the Penthouse category.

Applied Filter example

To remove a single filter, click the filter icon in the row header and choose Clear Filter from…. To clear all filters quickly, choose the Clear button from the Data tab on the ribbon or to remove filtering functionality, re-click the Filter icon from the Sort & Filter group. For more advanced users there are many more ways to utilize filters in Excel, such as Custom Filters and Advanced Filters. Nonetheless, a lot can be accomplished with the basic AutoFilter feature.

​

Formatting Data and Worksheets

Formatting data generally makes it easier to comprehend, and more attractive. The goal of formatting a business spreadsheet should be to make it look professional, accurate, and understandable. Options to format worksheet data depends a lot on the type of data. Formatting text in Excel is very similar to formatting text in Word. Using the Font and Alignment group icons on the Home tab of the ribbon, users can manually format all of the data in a cell or in a range of cells. Changing default formats includes things like changing the font color, style, size, text alignment in a cell, or apply formatting effects – all in an effort to make the data appear more visible.

Excel’s tabular layout (organized into labeled columns and numbered rows) sometimes limits the amount of data that can be efficiently displayed.

Alignment of data

Using the Alignment group options can help format data to make it appear as much like a word processed report as possible. In the screenshot above, two Excel-unique formatting features help make the data more readable. In row 1, the text has been merged and centered across cells A1:F1. This required the cell range A1:F1 be selected before clicking the Merge & Center button from the Alignment group. This is very popular for worksheet titles. If the wrong cell range was selected and the Merge & Center is not effective, the user must re-select the Merge & Center button to unmerge the data, so the correct range can be selected before again clicking the Merge & Center button.

Another popular Excel text-formatting feature illustrated above is the Wrap Text tool, which will prevent text from being truncated by wrapping the text into multiple rows within the same cell. If a cell has a lot of text, the data will overlap into the next cell to the right of the active cell. However, if the adjacent cell already has data, the text will be truncated (cut-off) without applying this feature. Cells C3:E3 in the above screenshot each have the Wrap Text feature applied.

While Excel has some pretty useful text-formatting functionality, the majority of data entered into Excel is usually numerical data. Therefore, utilizing Excel’s number formats effectively is crucial to make the worksheet as professional as possible. Professional formatting relies a lot on consistent formatting. Therefore, using the Format Painter tool (introduced in the Word chapters) and workbook themes are very helpful to maintain that consistency.  Changing the existing theme will dynamically update the appearance of all worksheets in a workbook by modifying the fonts, styles, colors and/or effects.

Applying the correct cell format to numerical data is more than just style – it is substance too, because applying the wrong format can impact how other, related cell results are interpreted. The following graphic describes some of the more popular number formats. In addition to the examples provided, there are also other formats, including date & time values, which can be accessed by clicking the Number Format drop-down list.

Number Formats

Changing the number of decimals displayed can be manipulated by the Increase Decimal and Decrease Decimal buttons in the Number group. The displayed value could change to a rounded value, however, the actual value stored in the cell has not changed unless a ROUND function has been applied. Formatting only affects how the value is displayed. Applying formats can result in number signs (#####) being displayed in a cell. This is not an error, but an indication that the cell width is not wide enough to display the formatted number.

Excel data can also be formatted as a table. A table is a collection of related data, organized into a tabular layout of rows and columns, that can be manipulated via sorting, filtering and formulas. To create a table, select the data and click Insert > Table from the ribbon to open the Create Table dialog window.

Create table

Applying a table style will format the table with a predefined set of special table properties that includes options to define a header row or total row, and to band the row and/or columns to make the data easier to read. The filter buttons can also be removed. If the table functionality is no longer desired, click the Convert to Range option in the Tools group of the Design tab to convert the table to a normal range.

Table Tools Design tab

 

Table style Blue, Table Style Medium 16 with banded rows, a header row, and filter buttons.

There are many additional formatting tools available within Excel, but it is important to consider that sometimes, less is more! Too much formatting can be overkill and reduce the readability of a worksheet. Try to limit the use of multiple colors, fonts, and background colors/graphics. Format with a design purpose, not to entertain, and don’t forget to use the Spell Check tool (F7) to proof your workbook before printing or sharing!

​

Proper data formatting improves clarity and ensures data is interpreted correctly.

Cell Format

As mentioned previously, Excel will default to certain styles when you create a new worksheet. In particular, this includes the way that numbers are displayed and whether or not commas are automatically included. In this section, we will take a look at changing these defaults.

When you type numbers into an Excel workbook, it will often default to a specific format. For example, if you type “12/15/17,” Excel will convert this to read “12/15/2017,” assuming you were entering month, day, and abbreviated year. Similarly, “3/4” will display at “4-Mar,” the fourth day of March. However, it is possible that you may have been entering fractions, so “3/4” was meant to indicate three-quarters instead.

If this is the case, you will need to format your cells to properly display the information you are entering. When possible, consider formatting your cells before you enter the data. Otherwise, Excel may convert some of the entries and you will need to re-enter that information.

1. Begin by highlighting the cells you plan to use.
2. Select the Format dropdown from the Cells group of the ribbon.
3. Select the Format cells option at the bottom of the dropdown menu.

Number Group

The Home tab → Number group provides built-in number formats:

• Currency and Accounting for financial data.
• Percentage for ratios or growth rates.
• Date/Time for schedules and timelines.
• Comma Style to add thousand separators for large numbers.

Figure 1.5.1: Number formatting expanded

Comma Styles

At times, you may also wish to use a specific comma style with numbers entered into an Excel worksheet. For example, you may wish “1234” to display as typed or with a comma like “1,234.”

Comma styles are easy to change in Excel using a quick select option in the Number group in the ribbon. Simply to click on the Comma Style button in the Number group.

When clicking the comma style button, the comma style default is to display numbers with a comma in the thousands place and include two decimal places (Ex: “1200” becomes “1,200.00). This will also change the visible cell styles in the Style” area of the ribbon so you can easily select different options for comma and display format.

Listed below are the three most common options for comma and display format.

1. Comma: Comma with two decimal points (e.g., 1,234.00)
2. Comma [0]: Comma with no decimal points (e.g., 1,234)
3. Currency: Comma with two decimal points and a dollar sign (e.g., $1,234.00)

Font and Alignment Group

The Font and Alignment groups allow you to highlight categories and improve readability:

• Use bold, italics, and colors to emphasize headers or totals.
• Increase font size or apply fill color to distinguish sections.
• Align text left, center, or right for professional layout consistency.

For repetitive data tasks, Excel offers tools that save time:

• AutoFill: Select a cell with “January,” drag the fill handle, and Excel continues the series (February, March…). The same applies for number sequences.
• Copy/Paste: Use Ctrl+C / Ctrl+V to duplicate content. With Paste Special, you can paste just values, formats, formulas, or column widths—extremely useful when working with templates.

Flash Fill

Like many modern software programs, Excel is designed to recognize certain patterns. For example, perhaps you are creating a table that lists the last and first names of attendees at a company training session. After all the names have been entered into two separate columns, you realize you would like a single column to correctly display the full name. An easy way to achieve this without having to manually retype the entire list is to use Flash Fill.

1. Create a new column for the combined information you wish to display.
2. In the first cell, type the name as you wish it to display. In our screenshots, this would be “John Smith.”
3. Begin typing the next piece of data in the next cell. Excel should automatically suggest a Flash Fill option.
4. If the Flash Fill suggestion matches how you would like the information displayed, simply hit the Enter key and the rest of your column should fill in automatically.

Flash Fill is especially helpful if your data is initially in different forms but you want the final information to display in the same fashion. For example, in our attendee list, some of the names were capitalized, in all caps, or had no capitalization. Sometimes you may need to manually update more than one option but Excel will detect your pattern.

Flash Fill should automatically be turned on in Excel but if it is not, you can turn it on using the File > Options > Advanced menus. You can also turn Flash Fill on or off using the shortcut Ctrl+E. **Be aware that the Mac version of Excel does not have Flash Fill.

By combining clean entry practices with clear formatting, you ensure your worksheets are both accurate and visually professional.

---

Page written with material from COM112: Course Text Copyright © 2020 by The American Women's College is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.

​

Attribution

Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

​