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 bring up Excel, it will automatically be ready for you to start a new spreadsheet. If you would like to create another one 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.

Just 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, I will show how to use the features of Excel by examples. In the examples that follow, I will show you two different ways to have Excel calculate an equation for you.

In the following example, we will use the cell numbers and the fact that we would like 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 for us, 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 you average salary has been over the 5 years listed above. Activating the cell B6, as shown above, then click the function wizard and choose AVERAGE from the Statistical list under the Or select a category menu. 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. Then choose the Chart Wizard shortcut button from the toolbar. You can also select Chart from the Insert menu.

The Chart Wizard box that appears allows you to select the Chart type and the Chart sub-type in step 1 of 4.

In step 2 of 4, you are able to select the data range if you have not already done so. You also have the option of adding and removing data series from your chart using the Series tab.

With the data series selected and entered we are ready to move to step 3 of 4. Clicking Next takes us to the Chart Options box.

This step allows you to configure many aspects of your chart such as titles, labels and axes. Clicking each tab will bring up different options for your chart. Configure your chart and move on to the final step.

After step 3, the Chart Location box appears.

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. Click Finish to insert your chart.

After the chart has been inserted, you will see your chart along with the chart toolbar as seen in the picture below.

The chart toolbar contains shortcuts to chart options. If you do not see the chart toolbar, you can select Chart under the View, and Toolbars menu.

Further, if you would like to edit the chart, you can do so by right clicking the mouse button over the chart and choose to format from the menu given.

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 (highlight) the data cells to be plotted.
  2. Click on Chart Wizard button (or Insert then Chart from pull down menu).
  3. In Chart Wizard, specify bar, column, line, or xy (scatter) as the type of plot to add the trend line to.
  4. Sequence through the Chart Wizard until all requirements have been specified, then select Finish.
  5. On the displayed graph, click on the plotted data to select it (plotted data will be highlighted).
  6. With plotted data selected click Add Trendline from the Chart menu.(You may also right click on the data series in your chart and select Add Trendline)
  7. In the Add Trendline window select the type of trendline you would like under Trend/Regression type and configure the trendline options in the Options tab.

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.