Basics of Data in Excel

The five sections of the Data Ribbon are:
Get External data; Connections; Sort & Filter; Data Tools; Outline.

Excel Data Ribbon

The first two sections are about fetching or updating data from elsewhere other than the worksheet that is in focus. This can be from another spreadsheet, MS Access, an SQL database or Sage amongst others.

The next section allows the user to sort data in two different ways.

Excel Data 1

In the above image, each of the green titled sections have a symbol on the right hand side. This allows the user to select criteria within long lists of data.
At the top of the selection box is a simple smallest to largest and largest to smallest selection. Beneath that are filters for colour and text. At the bottom is the filter for contents within the chosen column.

In the above instance the choices are All, anything with a V or an X or Blank cells in that column. The selection that has been made is to view all rows of data with an X in that column or where that column is blank. So, the column on the right is only showing rows with an X or where the cell is empty.

To clear a selection and view the whole list, select All.

Excel Data 3

When the Sort button in the Data Ribbon Bar is selected, the choice is about which column is defining the sort criteria. Note that in the top right of the criteria box there is the option to choose whether or not to use the data’s headers as criteria. If the box is not selected the columns will be listed, otherwise the list of columns will appear similarly to the above example with the headers being the choice.

There is an option to search for a particular piece of text or a value. Separately hit each of these keys:

Alt then E then F

This brings up a search box. Enter a value or some text and Excel will search for that data within the open worksheet.

Excel Data 4
In the example above, a range of cells was selected (from the 682 to the 11,984). The criteria was 93.6 and the button ‘Find All’ was selected. This lists all instances of 93.6 within the selected range. Note that selecting one of the instances in the list will send focus there and the formula bar shows each of the values added in that cell. Note also that hovering the mouse over the cell shows the comments (which in this instance are a list of the clients and invoice numbers that were received on that day and in the order in which they appear in the formula bar)
.
If no cell range is selected, the search will search all of the worksheet. There is a danger that two cells are selected by accident. This means that only those cells will be searched. Selecting a single cell searches the whole sheet.

Searching in a spreadsheet for a value such as 93.6 will produce hundreds of results. If that seems likely, the search should be from an appropriate range. If the search is for 298.36 or some other value that is likely to be unique, it is appropriate to search the whole sheet by selecting a single cell.

Advertisements