Tools for Information Literacy ⑪ Data control

An Outdated Version of Excel Led the U.K. to Undercount COVID-19 Cases

A Spreadsheet

Excel was not the first spreadsheet (remember Lotus 1-2-3?).
But it is deemed by many software engineers to be the most consequential program ever written,
in part because it has been so widely adopted.
... Microsoft controls 87.6% of the market for such software, to Google's 11.5% ... Economist, 24 Oct 2020

It is data displayed in rows and columns on a plane and Microsoft's Excel spreadsheet program is one of the most used spreadsheet applications. Excel's parts and its Worksheet are similar to MSWord and other Office applications. Many of the shortcuts in Word will work in Excel.

Windows shortcuts	Mac shortcuts
CNTL+s to save	CMD+s to save
CNTL+n for a new document	CMD+n for a new document
CNTL+c to copy	CMD+c to copy
CNTL+x to cut	CMD+x to cut
CNTL+v to paste, etc.	CMD+v to paste, etc.

Google Sheets or LibreOffice Calc are both reasonable alternatives, but Excel is still the most used and has some advantages.

a workbook is a collection of worksheets

Use this example as we review some of the basic components.

Sheet tabs for multiple worksheets

Scrollbars right and bottom

Rows numbered 1 through 1,048,576 in Excel 2007+

Columns lettered A through XFD (16,384) in Excel 2007+

Cells are where the data is stored

Fill handle is the small black square in the corner of the cell

• when you point to it, the pointer changes to a black cross
• you can then drag a copy of the contents of that cell to an adjacent cell or fill in a series
  • to see fill options, right click as you drag

Dealing with data

Entering Data

Select the cell where you want to enter data. Start typing. The data will appear at the top of the screen in the Formula Bar, where you can edit it, correct errors, and add text, figures, or formulas. The data is only placed into the cell when you hit the Return key, the Enter key, the Tab key, or any of the direction keys.

OR....

Double click in the cell. This will allow you to edit in the cell instead of in the Formula Bar

If you enter a long string of data, Excel will either truncate the display of the label or spill the display over into the next cell. You may see this: ###########. This is OK.

To increase a column width, put the cursor on the top of the column where it borders the second column. The cursor will turn from an + to a line with 2 arrows (similar to changing tables in word). Left click and hold the mouse, drag it to the desired position.

OR....

Autofill using the fill handle

• select cell and drag
• autofill data or number sequences
• can create custom lists for Autofill from popular options

Editing Data

Click on the cell you wish to edit, it's contents will appear in the Formula Bar

OR....

Double click the cell (see above under Entering Data)

If you add data to a cell by pasting from another cell or another application, you have many options. Pasting from another cell can bring values, formulas, relative references, and many other properties - as you wish.

---

Deleting data

Click the cell

If you use the delete key, Excel assumes you want to clear contents of the cell(s), but does not clear any formatting you may have added

• do not use the spacebar to clear cells; it adds a space to the cell rather than emptying it
• you can also select a cell, right click, and select Clear contents

If you use the right click Delete dialog, Excel assumes you want to remove the entire cell from the worksheet, not just the contents from the cell.

---

Moving from Cell to Cell

• use mouse
• use keyboard arrow keys
• use tab to move to the right
• use ctrl+g, enter Reference (the cell you wish to go to), press OK

---

Moving to Another Sheet

Click the Sheet tab

---

Selecting Cells

• click the cell, the pointer changes to a big cross
• to select multiple cells, select and drag
• when selecting a large area, use the Shift key to extend the selection. Click on the first cell of the range you want to select; then, while holding down the Shift key, click on the last cell in the range you want to select.
• select discontinuous range of cells by holding down the CTRL key while making the various selections.
• to select an entire row or column, click on the appropriate row or column heading
• to select the entire range in the worksheet, click on the box atop the row numbers and to the left of the column numbers

---

Inserting and Deleting Cells, Rows, and Columns

insert

• click in a cell
• right click
• select Insert...

delete

• select the area to be deleted
• right click
• select Delete...
• select the desired option from the dialog box

you can select a cell, an entire row, an entire column, or any group of cells

---

Cutting and Copying Cells

Excel does not follow the standard sequence for cutting, pasting, copying.

You may need to pay attention to how the data is formatted if you want Excel to understand your intentions

Basic Formatting is quite similar to Word

The same is true in Excel for Mac

Formatting is used to identify numbers as currency, percentages, decimals

Excel Kept Messing Up the Names of Genes, So Scientists Renamed Them

select cell(s) and use Standard Toolbar for fast formatting of currency/percentage & increased/decreased decimal. For additional formatting, select cell or cells & right click.

The same is true in Excel for Mac

select Number tab. Modify category, decimal places, symbol, etc.

Excel Date and Time Formatting

You can also change the appearance of a date number
Oct-27-2025 or 10/27/2025
or, my favorites (and in a more logical order)
27-Oct-2025 or 20251027

But, remember, a date is stored as a number that counts away from 01 January 1900.
Thus today's date is stored as 45601.
When we format the cell with this number in it as a date, it displays as 45,937 days after 01 January 1900, or 27 October 2025.

---

But you can do more with Conditional Formatting

Conditional formatting is more flexible, applying specified formatting only when certain conditions are met.

Techniques to Help View Data

Freeze Panes

Freeze row or column headings so that with long worksheets you won't lose sight of the labels as you scroll through your data.

To freeze a row/column, select the row/column immediately underneath or to the right of the row/column you want to freeze, go to View > Freeze Panes.

Split Window

is useful when you are working with a worksheet with a lot of columns or rows and you need to see values in widely separated columns or rows

Select the row/column where you want to place the split, go to View > Split

Q1: What's the difference?
Q2: When is one method preferable to the other?

Validation

To validate data as it is entered, use Data, Validation, and build a data entry rule with the dialog box.

The same is true in Excel for Mac

Add-ins

Excel comes with additional tools that don't necessarily load unless you ask for them. Look under Excel Options.

Add-ins: The Analysis Toolpack Add-ins: How to Translate Formulas

The same is true in Excel for Mac

Check the ones you want and they will be added to your Tools menu.