Administrative Assistant / Secretary: Using Microsoft Excel

What Is a Spreadsheet?
Many administrative assistants use computerized spreadsheet software to handle accounting chores, assist with budgets, and perform similar tasks. Spreadsheet software takes the place of the columnar pad that was so popular in the past. A columnar pad is divided into columns across the top and rows that run down the side. The rows and columns intersect in small boxes. Altogether, there are hundreds of these small boxes on each page. Similarly, an electronic spreadsheet is a large grid of columns and rows. A box where a column and row intersect is called a cell. Each cell has a unique address. Most spreadsheets label columns using letters, and rows using numbers. Therefore, the cell at the intersection of column C and row 5 is cell C5.
The largest spreadsheet can contain millions of cells depending on the memory size of the computer running the software. However, most of the applications you’ll be working with use only 500 to 1,000 cells.
The most commonly used spreadsheet program is Microsoft Excel. 

Navigating Around a Spreadsheet
When you are using a spreadsheet, one cell is always active—that is, ready for you to input data. This cell is designated by a cell pointer, highlighted area, or flashing cursor. To make another cell active, you use the arrow keys, numeric keypad, or mouse to move to another location. Owing to the limits on screen size, only a small group of cells can be displayed at any one time. If you wish to view additional cells, use a mouse or the arrow keys to move even farther on the spreadsheet.
Navigating around on a spreadsheet to view additional cells is called scrolling. There are also special commands that will take you to predefined locations on a spreadsheet, such as the bottom or top.
To make using Excel as simple as possible, designers used menus and ribbon bars with icons for various built-in functions. Table 22-1 shows a list of menus and some of the functions on each menu.

Spreadsheet Data
Any entry into a cell can be one of three possible items: a label, number, or formula. A label is a word used to describe information in your spreadsheet. For example, you might want to calculate a budget for office expenses. Therefore, you would create a list of those expenses: paper, pens, computer supplies, stamps, and so forth. These words describe the numbers in another column or row, which is why they’re called labels.

Microsoft Excel features and functions.



Excel Menus and Functions
 

The actual expenses for the office supplies are the numbers. In order to calculate a total of all the expenses at the bottom of the list, you need to enter a formula, which is a combination of cell addresses connected by mathematical symbols—for instance, A1 + A2 + A3.
To enter a label, number, or formula into a cell, click the cursor on the cell you want to make active, then begin typing. As you type, the information you enter will be displayed in the control panel. When you are ready to put that information in the active cell, you either click the mouse or press ENTER/RETURN on the keyboard.

Formulas
A formula will work correctly only with numbers. Therefore, in order for a spreadsheet to distinguish labels, numbers, and formulas, most spreadsheet software programs use special predefined characters so that a user can specify the difference. For example, if the first character you type in a cell is a number, the spreadsheet will assume that entry is a number. If the first character you type is a letter, the spreadsheet will assume the entry is a label. And if you type in a special software-defined character such as “=”, the spreadsheet will assume your entry is a formula.
Spreadsheet formulas can get complicated. Formulas can link information from one area to another so that totals from one group or section can be added to an overall summary. For example, an accounting system could be set up that allows you to enter expenses into various accounts. One formula would calculate the total expenses for each account; another would be used to link the total of a particular account to an overall profit-and-loss statement.
The simplest formula is one that moves the data from one cell to another. If you entered the following formula in cell A1, “=Sum(A2),” whatever amount appeared in cell A2 would automatically also appear in A1.
The use of parentheses is an important aspect of formulas. For example, a formula such as “=Sum(A1:A10)” would provide a total of all the numbers in cells A1 through A10. When combined with other mathematical symbols for division, multiplication, and subtraction, a formula containing multiple sets of nested parentheses could be created, for example, “=Sum(((A1:A10) * A20) – A30).”
In order to create a formula correctly, you must know the order in which the mathematical operations will be performed by the computer. The natural order is to perform any calculation involving exponents first, followed by multiplication and division, and then addition and subtraction.

For example, to solve the formula = Sum((22 × 10) + ((144/12) – (5 + 6))), the computer would do the following:

Then, do the addition and subtraction, starting inside the parentheses:
5 plus 6 equals 11 = Sum(40 + (12 – 11))
12 minus 11 equals 1 = Sum(40 + 1)
To finish, 40 plus 1 equals 41 = Sum(41)

Whenever a continuous group of cells is involved in a formula such as a row or column or block of numbers, rather than type in each individual cell address, a range is specified instead. For example, A1:A10 specifies a range of cells from cell A1 to cell A10 including all cells in between. A range can be as small as two cells or as large as the entire spreadsheet. It can be a row, a column, part of a row or column, or a block of several rows and columns. Most often a range is specified by identifying the beginning cell, followed by a colon or an ellipsis, followed by the ending cell.

Automatic Recalculations
One of the nice features of Excel is the ability to recalculate formulas automatically if you change any of the numbers in the cells included in the formula. If you have formulas that link various columns, rows, or sections, changing one number in a cell can cause numbers to change throughout the entire spreadsheet as each formula automatically recalculates. This recalculation feature is extremely useful for performing a “what if” analysis.
If you want to see the effect of a change on one particular area on a spreadsheet, enter the new number and see what it does to the overall total. This feature allows you to build what are sometimes called spreadsheet templates. For example, if you create a spreadsheet to keep track of petty cash or a project budget, once you create the spreadsheet and the formulas, you can go back and change the labels and the numbers and have a whole new spreadsheet with a lot less work. By saving the new spreadsheet with a new name, both the old version and the new version will be stored for future use.

Functions
Excel has a variety of built-in functions that can replace complicated formulas. One of the simplest is the sum function. It allows you to calculate the total of a range of cells. Functions are identified by first typing either “=” or “@” and then the function name. This lets Excel know that the entry is a function and not a label. The Formulas menu offers many different mathematical, statistical, and financial formulas.

Editing Your Spreadsheet
Another feature that can save you much time is Excel’s ability to copy labels, numbers, and formulas from one location to another. For example, you might want to list your petty cash expenses by months, with each month in a separate column. Rather than re-creating the labels and formulas for each month, you could copy the entire first month’s information and paste it into the next column or the next group of columns. Excel automatically compensates for the differences in cell labels from column to column and row to row and adjusts the new column so that the formula calculates the numbers in the new column, not in the original month column. The commands for selecting, copying, and pasting can all be found on the Home menu ribbon bar.

Arranging Layout
Arranging your layout to look the way you want is another useful feature of Excel. You can change the contents of any cell; add or delete rows or columns; and copy, cut, and paste data from one cell, row, column, or block to another similar area. All of these functions are available on the Home menu or by right-clicking a cell, row, or column on the spreadsheet and making a selection from the pop-up menu.

Editing Commands
The simplest editing feature is to access the contents of a single cell and to alter the data there. When you make a cell active by selecting it with the cursor or mouse, its contents appear in the control panel. There you can insert text, type in completely new text, or change a number.
You create new cells by inserting a new row or column. The INSERT command or CREATE NEW COLUMN OR ROW command allows you to do this easily. You select a column to the left of where you want to insert a new column, right-click, and then select the INSERT command from the pop-up menu. For inserting rows, you select the row where you want the new row to appear. Pointing to the very top of a column or the extreme left of a row with a mouse will allow you to select it.
To delete a row or column, select the entire row or column, right-click, and choose DELETE from the pop-up menu.
Whenever you insert or delete a row, it’s important to note that some cells and formulas will move in the spreadsheet to a new cell address. Excel will automatically adjust to compensate for these moves, so that you don’t have to go back and make changes. Therefore, if you have a formula that adds up the total of a column of ten cells, and you insert five new rows in the middle, the formula will automatically be changed to add up a total of fifteen cells instead.
Formatting features help you align the numbers and text to make your spreadsheet look good when printed out on paper. Formatting commands that are found on the Home menu allow you to justify the text in a document, center text or numbers, or make them flush left or flush right. You can format individual cells or rows to specify how many decimal points will be displayed or to create dates, dollar signs, commas, percentages, and scientific notations. You can change the width of a cell or column in order to display more information, such as a long label or a very large number.

Other Spreadsheet Functions

Saving and Printing
When you have formatted your spreadsheet to look the way you want, you may print it or save it. Printing and saving are commands you can select from the File menu. If you select SAVE, you’ll be asked where you want to save your spreadsheet and to give it a name. You choose the drive where you want to save the file and then type in the name. Whenever you work with a spreadsheet that has been saved previously and you wish to save it again, it will automatically be saved under the same name in the same location. If you want to save two or more versions of a spreadsheet, you will need to change the name slightly. This can be accomplished by selecting SAVE AS from the File menu.
When you wish to print a spreadsheet, you’ll be given an opportunity to determine how much of your spreadsheet you want printed, whether you want a header or footer, if you want borders or a grid, column and row numbers, and so forth.

Templates
To help get you started, Excel has built-in templates, which are pre-built spreadsheet models for common applications in business such as budgets and financial analysis. By loading a template, you can edit the spreadsheet to customize it to your particular business. This can be a great time-saver. You can access templates by selecting FILE > NEW, and then selecting one of the Office.com templates.

Spreadsheet Macros
Spreadsheet macros are another great time-saver. As you may already know from your word processing work, a macro is a way to minimize repetitive keystrokes. You can create a macro by selecting MACRO from the Developer tab.
The Developer tab is a special tab that must be added manually by customizing the ribbon bars. You can do this by right-clicking any of the ribbon bars and then select CUSTOMIZE THE RIBBON.
When you create a macro, you will be asked to type in the keystrokes you want to record. Once these are recorded, you’ll be asked to assign a simple keyboard command to trigger the macro. Excel comes with built-in macros that you can customize and access with special keyboard commands.

Creating Charts and Graphs
It is easy to create charts and graphs with Excel. Follow these steps to create a chart or graph:
1. Create a spreadsheet and add the data that you want to use for creating the chart.
2. Use your mouse to select the data from the spreadsheet to be used for the chart.
3. Click the Insert menu, and in the Charts group, select the type of chart you want to create. For example, select BAR to create a bar chart.
4. From the drop-down menu, select the specific chart you want.
5. The chart will display in your spreadsheet. It can be moved and positioned anywhere on the sheet, as well as copied and pasted into a Microsoft Word document.
6. If the information on the axes of the chart is incorrect, click the Design menu, and click SWITCH ROW/COLUMN.

Modifying an Existing Chart
To modify an existing chart, follow these steps:
1. Right-click on the chart and from the pop-up menu, select CHANGE CHART TYPE. The Create Chart dialog box will display.
2. Select the kind of chart you want and click OK.
3. To change the data used to create the chart, edit the original spreadsheet that was used to create the chart.

Creating an Organizational Chart
The SmartArt function in Excel, Word, or PowerPoint can be used to create an organizational chart (Figure 22-2). To create the chart, follow these steps:
1. Create a new spreadsheet.
2. Click the Insert menu, and select SMARTART.
3. From the Choose a SmartArt Graphic dialog box, select HIERARCHY. A list of chart styles is displayed.

Organization chart created using Excel SmartArt.


Screen shot used with permission from Microsoft.
4. Click the chart style you want from the list, and click OK. A blank hierarchy chart will appear on the spreadsheet.
5. Click the tab to the left with the small left and right arrows to expand the text entry box.
6. Enter the appropriate text for the organizational chart in the outline. Indenting items using the TAB key on your keyboard will create another level in the hierarchy.
7. As you fill up the chart, the text will get smaller and smaller. You can expand the size of the chart by clicking and dragging one of the corners of the chart.
8. You can change the look of the chart by clicking the Design Tab.
9. Select one of the SmartArt Styles to see the various possibilities. In addition, you can select one of the styles in the Layout group to modify the chart. One of the options even allows you to insert photos of each person listed in the chart.