Microsoft Excel - Additional Information

Home › How-Tos › Microsoft Excel - Additional Information

Starting Excel on DECS PC's

To start Excel, click 'Start', 'Programs', 'Microsoft Office', then '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 another 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 short cuts 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 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 makes room for 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 funtions you are looking for you can search for the function or you can select 'all' under '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. I will illustrate in the following example.

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

I would like to know what my average salary has been over the 5 years listed above. Activating the cell B6, as shown above, I 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 we would like to find the average of the cells B1 through B5, so under 'Number 1' you would 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 trendline 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 pulldown menu).
  3. In Chart Wizard, specify bar, column, line, or xy (scatter) as the type of plot to add the trendline to.
  4. Sequence through ("Next") 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/Regerssion 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', and click on the 'View' tab. Select the check box labeled 'Formula' and click 'OK'.

The second way is to press 'Ctrl-Tilde'(~) and 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' and select '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 Accomodate the Excel Recalculation Bug 

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