This article introduces you to the basics of spreadsheets which involve the use of a workbook and the associated worksheets. You will learn how to enter data and format, use formulas, and design charts and other spreadsheet activities using the MS Excel program.
A spreadsheet is an important business tool that helps to
analyze and evaluate information especially for maintaining lists, budgets and
financial reporting, inventory management, and cost estimating. In this
lecture, you will learn the basics of creating professional worksheets in MS
Excel.
Spreadsheet Basic
Spreadsheets are basically used to manipulate numbers especially useful in creating a budget, financial forecast, and basically anything that deals with columns, numbers, and formulas. To avoid confusion when working with MS Excel, always remember to:
(i) give a Label to your numbers
(ii) use a Cell Reference when possible, and use Absolute and Relative cell references as needed
(iii) avoid having Blank Columns, set your column width instead and
(iv) verify all your formulas and functions to be sure it produces the correct result.
Spreadsheet Terminologies
The most important terminologies in MS Excel include columns, rows, cells, cell reference, cell range, active cell, anchor cell, worksheet, and workbook. Columns run vertically on the screen and are labeled with the letters of the alphabet. In MS-Excel 2003, there are 256 columns starting from column A. When you get to column Z the next column is AA, AB, AC, ... AZ, BA, BB, etc.
You can insert and delete columns, but you will still have 256 columns. As a general rule column will hold information that is alike. If you are working on Excel 2007 or any of the latest versions, there are 16384 columns. The last column, in this case, is XFD. Rows run across the screen horizontally and are numbered from 1 to 1048576 rows for MS Excel 2007 or later and a maximum of 65536 rows for MS Excel 2003.
Cells are the intersection of the column and the row. Column A, row 1 is referred to as cell A1. Cells can hold 4 different things (Numbers, Formulas, Functions, and Labels). When dealing with functions and formulas the result (or number) will be displayed in the cell. Labels are simply text typed into the cell. Labels by default will be left-aligned in the cell. Labels have no numeric value. You can fit 256 characters in a cell.
Numbers are numbers typed into a cell and are by default right aligned. Formulas are mathematical formulas that are typed into a cell and the result of the formula will be displayed. All formulas will begin with an equal sign (=) to indicate to the computer that it is a formula and not a label. Formulas are made using cell references. For example: =A1+A2 will simply add the number in cell A1 to the number in cell A2 and display the result in the cell that the formula is located in. Functions are built-in formulas that are already in the spreadsheet. They also begin with an equal sign (=).
A commonly used function is the SUM function that will add a range of cells. Cell Reference is when you refer to a value located in a cell by using the cell name, like A1. This will use the value located in cell A1. Cell Range is a consecutive grouping of cells. For example, cells A1, A2, A3, and A4 can be referred to as one range by simply starting with the first cell in the range followed by a colon and then the last cell in the range. Like this A1:A4 or A6:D8. A range of cells must always be in a square or rectangular shape. If you can highlight the group of cells, then it is a valid range. Active Cell - is the cell that has the cursor around it. Anchor Cell is the first cell in a highlighted range.
When you highlight the range, it is in black color, however, you will notice that the first cell is still in white. This is the anchor cell and is part of the highlighted selection. The worksheet is the sheet that you type in your information, numbers, and formulas. Worksheets are by default named sheet1, sheet2, etc.
You should name your sheets with a meaningful name. Simply double-click the sheet tab to rename it. Workbook by default contains 16 worksheets and is the file that you save. As you do more complex spreadsheets you may find that you have several worksheets that are all related and want to keep them in one file which is called a workbook. You should delete any extra sheets in your workbook that you are not using.
Excel Screen
The MS Excel Screen contains the Title Bar, Formula Bar, Sheet Tabs, Gridlines, Insertion Point, etc.
Title Bar shows the name of the application and the name of the file. Formula Bar displays what is actually typed into the cell. In the case of a formula or function it will display the actual formula in the formula bar. The result would be displayed in the cell. Sheet Tabs identify the sheet you are working on. In Excel you can have several sheets all in one file. Gridlines are the light gray lines that outline the rows and columns. These can be turned on or off. Insertion Point is the active cell. In a spreadsheet you type information into cells.
Simple Functions
A function is a built-in formula that comes with the
spreadsheet program. These are commonly used formulas. They consist of the
function name followed by the parameters or arguments enclosed in parenthesis.
If there is more than one argument each will be separated by a comma. As with
formulas all functions must begin with an = sign. Some basic formulae include
SUM, MIN, MAX, and AVERAGE.
Moving Around in Excel
There are many ways to make a specific cell the active cell. Most users click with their mouse on an active cell. However, since typing is a significant part of using Excel, there are keyboard shortcuts for moving around on a spreadsheet as shown in Table 2 below:
Absolute References
By default, Excel uses the A1 reference style, which refers to columns with alphabetical letters. These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number, such as D50. To refer to a range of cells, enter ranger by entering the first cell followed by a colon (:) and then the next cell should follow such as A5:B10. In formulas, you can use references to cells on other worksheets in the workbook, or even cells in other workbooks. If a dollar sign precedes the letter and/or number, such as $A$1, the column and/or row reference is absolute. If you don't want Excel to adjust references when you copy a formula to a different cell, use an absolute reference.
For example, if your formula multiplies cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows: =A5*$C$1.
Table below Cell References
Charts
Excel gives various options of charts such as Column (Bar), Line, and Pie. The Column (Bar) charts are good for comparing both categories and data series. The first sub type is the common one. The second column of sub types is called a stacked column (or bar) and it takes the second data series and stacks it on top of the first data series. The third column of sub types is a one hundred percent where each data series is added together and then instead of the value you get a percent. Line involves charts which are good for showing trends and fluctuations over time. This type is also good for showing predictions. The first column of subtypes is the most common. The second column is a stacked line and the third column is a one hundred percent line.
Pie charts are good for getting the percentage of how each category fits into the whole picture. You can only use one data series for a pie chart. If you want more than one data series, then you need to choose a 100 percent chart type. Scatter charts are good for showing a correlation between your data points. Area charts are good for showing the total of all the data series and the area difference between your data series. The data series are stacked on top of each other with the first data series being on the bottom. The resulting top line of the chart is the total of all data series.
MS Excel Chart Types
