Microsoft Excel - Additional Information

Starting Excel on DECS PCs

To start Excel, click Start > Programs > Microsoft Office > Microsoft Excel.

Creating a New Spreadsheet

Once you open Excel, it is ready for you to start a new spreadsheet. If you would like to create another spreadsheet while working on the current one, simply choose New from the File menu.

You can also get to a new sheet by clicking on Sheet Tab (which is located at the bottom of the spreadsheet) other than the one that you are currently working on (default is Sheet 1).

Some Basics Of Excel

Entering and Editing Data

To enter data into the spreadsheet, the cell that you are typing in must be active. When you first open Excel, cell A1 is active.

Begin to type and when you are finished, press the return key to go to the cell directly below the cell that you are currently working in, or the tab key to go to the cell directly to the right of the cell that you are working in.

Examples For Entering Data

Below is an example that will help you to enter data more quickly using shortcuts provided by Excel. If March, April and May also need to be added to the list, you do not need to type all of them in. Excel has a shortcut for certain lists and increments. In the lower right side of the box, there is a small black box. Place your mouse on this box, and hold down the left mouse button and drag the box to the right until you are three cells to the right (or however many extra months you would like to add). This enters in March, April and May.

You can also do the same thing for the years on the left side of the spreadsheet. Put two numbers into two different cells (this will set the increment value). Then put the mouse in the lower right box just like we have already done for the above example. Highlight the two cells (as shown in the figure below) and then drag the cursor down as many cells as you like to continue the count with an increment of one (which was set when typing in the 1985 and 1986). If you only highlight one of the numbers, it will copy that same number to any other cell you drag your mouse to.


Using the Formulas to Calculate Specific Values

Throughout most of this tutorial, you will see how to use the features of Excel by examples. In the examples that follow, you will be shown two different ways to have Excel calculate an equation for you.

In the following example, you will use the cell numbers and the fact that you want  to see the total count of pages printed from a particular toner cartridge in one of the DECS PC labs. To have Excel do this, in the cell highlighted, type the following command: =E2-D2. After pressing the Return key, you will see the result in the cell you had highlighted. Instead of typing that formula for the rest of the rows as they are entered, take the mouse button and place it in the lower right box and drag it to the next cell below. Excel will increment each formula to the next set of column D and E. In other words, it will change the E2 to E3 and the D2 to D3 and so on for the rest of the rows that you complete.

You can also use the function wizard shortcut key to do this. Click in the box that you would like to make a formula for and then proceed by clicking on the function wizard shortcut key from the toolbar.

Once you click on the function wizard, you will get a list of different functions that are already built into Excel for you to use.

Choose the function that you would like Excel to perform and choose the Next button. If you do not see the functions you are looking for you can search for the function or you can select all or select a category. By choosing different items from the Or select a category list, you will see different (some will be the same) functions on the Select a function list. After choosing the Next button, you will be asked for the cells that you would like to perform this function on.  This is illustrated in the following example.

Suppose that you were keeping track of your salary increases and decreases from year to year.

You would like to know what your average salary has been over the five years listed above. Activating the cell B6, as shown above, then click the function wizard and choose AVERAGE from the Insert Function list under the Formulas tab. Choose the Next button and then type in all the cells that you would like to include in the average function. In this example, find the average of the cells B1 through B5, so under Number 1 type B1:B5.

Then proceed by choosing the OK button.

Creating a Chart

 

We can take the information above and plot it on a chart. To do this, highlight the data to be entered to the chart, and then select the Insert tab.  From there you can select your chart type.  

To change the data selected in the graph, right click the graph and click Select Data Source.  

To edit the labels of the graph. select the graph and under chart tools select layout.

When making the graph, it defaults to a new object.  If a new sheet is desired, then select the Chart Tools > Design > Move Chart.

This box presents two options. You can either insert the chart as a new sheet or you can insert the chart into an existing sheet. If you choose As object in, you may select which sheet you would like your chart placed in.

Adding a Trend Line

To add a trend line to a plot or get the equation of a line that fits a data set in Excel:

  1. Select the graph
  2. To add a trend line select Chart Tools > Analysis > Trendline
  3. To add an equation right click on the line you desire > Add Trendline > Check Display Equation on Chart

Fitting a Worksheet to Page

Excel worksheets can be fit to a page by choosing Page Setup from the File menu. Choose the Page tab, and select Fit To, and specify the number of pages to which to fit the spreadsheet.

Forcing a Recalculation of a Worksheet 

To force an entire worksheet to recalculate, hold down the Control, Alt and F9 keys at the same time.

To Switch Between Data and Formula Display 

To troubleshoot and look at the Formula in the whole sheet, there are two ways. One of them is go to Tools > Options > View. Select the check box labeled Formula and click OK.

The second way is to press Ctrl-Tilde (~). This will toggle between data display and formula display.

View Your Worksheet 

To see the outlook of the whole entire sheet, choose View > Full Screen. Now press Ctrl-Home to go to A1 if you're not already there and then press Ctrl-Shift-End to select the entire worksheet (only the areas that contain data). Choose View > Zoom > Fit Selection. Click OK and you should be able to see the entire worksheet. It may be too small to try to read the data, but you can see the layout of the sheet.

How to Accommodate the Excel Recalculation Bug 

To force Excel to recalculate the spreadsheet, press the F9 key.