INLS161-001 Fall 2021

Tools for Information Literacy




Acquainting ourselves to the spreadsheet tool


What is a Spreadsheet?

Data displayed in an X/Y axis grid,
with cells at each intersection of each row and column,
each of them individually capable of being formatted.

Who Runs the World? Microsoft Excel.
[Dilbert Cartoon, 20070807, by Scott Adams]
What is a spreadsheet? intro getting started entering data formatting other tools

back to top

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. 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.

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

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

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

[Office365 MSExcel 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

The same is true in Excel for Mac

[MSExcel 2013 components]
What is a spreadsheet? intro getting started entering data formatting other tools

back to top

Getting Started

Before you begin a complex spreadsheet, plan it out

  1. what are the desired outputs? (e.g., charts, professional look, for personal use?)
  2. what data do I need to enter?
  3. how is the data positioned?
  4. what mathematical expressions are used?
  5. who will be using the spreadsheet?

Setting up your workbook

[how to find Office365 set-up options]

First, check your options in the file sidebar

[Office365 setup options dialog box]

The same is true in Excel for Mac

[Excel 2013 setup options dialog box]

Tune up your workbook to your needs. Make it work for you, not against you.

Saving a Worksheet

Save with cntl+s or Save As with alt+f+a, and,
as in UNIX and MSWord, ... try_not_to_leave_spaces_in_filenames

What is a spreadsheet? intro getting started entering data formatting other tools

back to top

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

[MSExcel 2013 auto fill options]

The same is true in Excel for Mac

[MSExcel 2013 auto fill 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)

[MSExcel 2007+ entering data in cells]

Dealing with data

Click the cell

[MSExcel 2007+ deleting tools]

The same is true in Excel for Mac

[MSExcel 2013 tools]

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

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.

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.

Moving from Cell to Cell

Moving to Another Sheet

Click the Sheet Tab

Selecting Cells

Inserting and Deleting Cells, Rows, and Columns

insert
delete

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.

select the cell or range of cells to cut or copy

choose either cntl+x (Cut) or cntl+c (Copy)

If you select a range of cells as the destination of either Cut or Copy, and this range differs in size or shape from the source, Excel will complain. Selecting a single cell is easier, since this method tells Excel where to start the paste and will ensure that the destination is formed exactly like the source

What is a spreadsheet? intro getting started entering data formatting other tools

back to top

Basic Formatting is quite similar to Word

[MSExcel 2013 cell formatting tools]

The same is true in Excel for Mac

[MSExcel 2013 cell formatting tools]

But you can do more with Conditional Formatting

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

[MSExcel 2016 cell formatting tools]

Page Setup

Also similar to Word, but here you want to set up an electronic spreadsheet for paper applications.

Page  - for orientation & scaling

[MSExcel 2007+ page setup dialog box]

The same is true in Excel for Mac

[MSExcel 2013 page setup dialog box]

Header & Footer  - use the custom tools

[MSExcel 2007+ header setup dialog box]

The same is true in Excel for Mac

[MSExcel 2013 header setup dialog box]

Sheet - rows to repeat puts column headings on each page

[MSExcel 2007+ sheet setup dialog box]

The same is true in Excel for Mac

[MSExcel 2013 sheet setup dialog box]

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

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.

[MSExcel 2013 formatting cells]

The same is true in Excel for Mac

[MSExcel 2013 formatting cells]

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

Excel Kept Messing Up the Names of Genes, So Scientists Renamed Them
[MSExcel 2007+ format cells dialog box]

You can also change the appearance of a date number
Nov-22-2021 or 11/22/2021
or, my favorites
22-Nov-2021 or 20211122

But, remember, a date is stored as a number that counts away from 01 January 1900.
Thus today's date is stored as 44497.
When we format the cell with this number in it as a date, it displays as 44,497 days after 01 January 1900, or 28 October 2021.

What is a spreadsheet? intro getting started entering data formatting other tools

back to top

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.

[MSExcel 2013 freeze panes]

The same is true in Excel for Mac

[MSExcel 2013 freeze panes]

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

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

[MSExcel 2013 split sheets]

The same is true in Excel for Mac

[MSExcel 2013 split sheets]

What's the difference? 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.

[MSExcel 2013 data validation]

The same is true in Excel for Mac

[MSExcel 2013 data validation]

Add-ins

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

[MSExcel 2007+ add-ins]

The same is true in Excel for Mac

[MSExcel 2013 add-ins]

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

What is a spreadsheet? intro getting started entering data formatting other tools

back to top

Copyright © R.E. Bergquist 2014- | Last Updated on | Powered by w3.css