R. Craig Collins >Common > How To: Excel

How To:Excel © R. Craig Collins, 2005, 20

See also https://support.office.com/en-us/office-training-center

See also Excel Basics, a Link to tutorials (pdf)
See also Welcome to Excel (pdf)
See also Advanced Excel

Additional resources https://edu.gcfglobal.org/en/excel/

Word Tables to Excel Tables (pdf)
To YouTube Video Companion Part 1 (High Quality best viewed Full screen; PC & Apple compatible; on-campus allow time for buffering)
To YouTube Video Companion Part 2 (High Quality best viewed Full screen; PC & Apple compatible; on-campus allow time for buffering)
Refer to this web site for more info on items mentioned in the video.
Using Word and Excel for basic Database functions (pdf)

The nice thing about Excel is the layout is very similar to Word; same icons, similar ribbon items, etc. And if you have used tables in Word, you already have an idea of what spreadsheets do. The biggest difference is that a spreadsheet table can manipulate numbers much better than just adding a row, as you can in Word... and Excel does all the math. (Note, a video demonstrating key points is here)


      Excel Key Points
      Excel is a spreadsheet, a program for manipulating numbers.
      A spreadsheet is composed of tables, which are made up of rows and columns; the intersection is a cell
      Excel has features to automate data entry, such as Autofill
      Most work in spreadsheets are done using formulas, such as =3+5, or =A1-B2
      Compound or complex formulas are called functions, such as =sum(A1:B2) or =ave(A1:B2)
      The column width can be changed to better hold contents
      Formatting can be automatic
      Charts can better convey complex numerical information
      Excel can share charts with other programs
      Excel is not WYSIWYG (What You See Is What You Get), steps must be taken to view headers and formulas.

Entering Data into an Excel Worksheet

Understanding Excel Data Types
Spreadsheets are designed to calculate formulas, analyze numerical data, and display information in charts. Instead of typing in just words, which are called labels in a spreadsheet, you may also enter numbers, called values, or manipulate the numbers with functions and formulas.

Entering Lables/Text
Rows are divided into cells, into which you can type labels, values, formulas, or functions. Select a cell with your mouse, or use the arrow keys, the type a word or phrase, and then hit the Enter key. Text is left aligned, by default.

Entering Formulas
Aside from entering =31+A1, you could also type =31+ then using the mouse, click on A1. Press Enter when finished.

Using the Status Bar AutoCalculate Feature
If you highlight a series of cells, on the status bar on some versions of Excel appears the sum of the numbers. This is only a preview feature. You may also right click the sum to get other calculations.

Note the Sum=7 preview on the status bar

Editing Formulas
If you decide to change a formula that has been entered, select the cell, then make the changes in the formula bar. Or select the cell, and click [F2]

To reveal all formulas, press Control + [`] (the unshifted ~). To go back to normal view, press Control + [`] again

Performing Calculations with Functions
What are functions
Functions are stored formulas, so instead of typing in =A1+A2+A3, you could do =SUM(A1:A3), which means add values in the RANGE A1 through A3.
There are many useful functions, such as AVERAGE, COUNT, plus financial, statistical, database, etc. Using Excel's Help, read Function Reference. You may quickly tell Excel you want to use a function by clicking the fx next to the formula bar, or use the Formula tab.
See Functions below for more on the Formula tab.


Using Auto-Sum (Sum)
If you click under a column of numbers, and press the [] button, Excel will automatically Sum those numbers.


Using the Insert Function Feature
As shown above, you may click the fx button to enter a function, but you may also choose the Formula tab. Dialog boxes will assist you with complex functions. Drag across cells to input them into the dialog box, and use different ranges of numbers (such as A1:A3 andA17). If you have trouble seeing the cells to click on, click the Collapse button on the Number text box, choose the number, the click the Expand button to get back to the dialog box. (Just like minimizing any window...)


The column width can be changed to better hold contents
Note, you don't resize cells, you resize columns.
If text is too large to fit in the cell, it may spill into the next cell (as in row 2),
however, the second something is added to the cell to the right (as in row 3), the words will be truncated.

The problem is, if numbers were spilling into the next cell, truncation might mislead you as to what the actual number was...
is it 50 or 500000? So numbers don't truncate... instead, if the column is not wide enough to hold a number, it displays #####

To correct this situation, resize the column.
Three methods
1. Place your cursor between the two columns, the cursor changes to a double sided arrow, click and drag to resize
     

2. Highlight all the columns that need to be resized, the double click the line between any two of the columns

3. On the Home tab, choose Format, then choose Column Width
     

Formatting can be automatic
To change text color, alignment, fill color:
Choose the cell, then choose the Home tab, then select Format, then Format Cells...
(See image above)
You may also right click the cell.
You may also create rules to automatically format cells, investigate with Conditional Formatting

Charts can better convey complex numerical information
Understanding Charting Terminology
Add the following to an Excel Spreadsheet:

Select all the cells except the labels at the top, and using the Insert tab, select pie chart, and then Finish.


Now you are ready to understand some terms.
The pie wedges represent data series, in this case, the number of As (7) or Bs (6).
Categories would be the labels. They aren't in this chart, because they weren't selected.
Some charts are on a grid, the horizontal and vertical lines are called axis. The dividing lines are called gridlines.
The legend indicates which color is for A, for B, etc.

Working with different Charts Types

Pie is for parts of a whole; Bar and Column are for comparing values at a point in time; Line, Area and Scatter show trends.

Creating and Saving a Chart
Instead of jumping to Finish, as we did earlier, There are many steps you may add to control whether or not your data is in rows or columns, includes categories or not, controlling the legend, etc. Look on the new ribbon when a chart is selected. Experiment!

Moving and Resizing a Chart
Once a chart is created, it can be dragged and resized. Click on the outside border to move, or click and drag one of the square box edges that show up after being clicked to resize. Hold the shift key down to keep it proportional.

Printing a Chart

If you wish to print just the chart, and not the whole workbook, select the chart, and then choose Office Button/Print. The Selected Chart button should already be selected... choose [OK]

Excel can share charts with other programs

First, copy the chart.

Open Word, or PowerPoint, and place your cursor where you want the chart.

Click the arrow under Paste

Select Paste Special, then Paste Link, and chose the Chart object.

Now changes made in Excel will reflect in the other document!

Excel is not WYSIWYG (What You See Is What You Get), steps must be taken to view headers and formulas.
When printing, you may first need to highlight what you want to print, then on the Page Layout tab, and select the range to print with the Print Area tool.

You may also need to click the arrow next to No Scaling to make your items fit on one page.

What you see on the screen may not be what comes out of the printer, because you may have headers that are not displaying in normal view.
At the bottom of the page select Page Layout rather that Normal view. Now you may also edit headers in this view.

Or, you can click Page Layout on the ribbon, then Headings, to access Headers.

Or, on any tab you see Page Setup, click the nearby arrow to access Header/Footers.

Finally, in Excel, you typically see the result of the forumula in the cell. If you select a cell, you can see the formula, or edit the formula.
To see ALL the formulas, press the Control key and the Grave` key at the same time.
Repeat to go back to the standard view.