INTERMEDIATE EXCEL

MORE WORK WITH CHARTS

Open the workbook called Widget Sales (download here, then click on File, Save As, and save it on your laptop). It should look like this.

CHARTING NON-ADJACENT RANGES

Excel can chart non-adjacent ranges (ranges that are not side by side), and if you change your mind, you can always add other ranges later.

EXAMPLE:
Suppose you would like to chart the sales for Alabama and Georgia.
1.	Select cells A2:E3. This includes the titles and the information for Alabama.
2.	Hold the CTRL key down, and select cells A5:E5.
3.	Click on the Chart Wizard icon.
4.	In Step #1, choose a column chart.
5.	In Step #2, check the selected ranges. Notice the comma between the two separate ranges. Click on Next.
6.	In Step #3, add a chart title that says: Widget Sales. Add a category x-axis title that says: (in millions). Click on next.
7.	In Step #4, select the object button, and click on Finish. The chart should appear in the worksheet.
8.	If the chart is covering the data, click on the chart so that the sizing handles appear around the chart, then click on the chart and drag it to a more suitable location.
9.	If the labels on the x-axis are unreadable or turn in the wrong orientation, point to one of the middle sizing handles and drag to make the chart bigger.
Your chart should look like this:
EDITING THE CHART To edit the chart, you must first select that part of the chart. If you watch your mouse as you drag it across the chart, you will see Quick Tips appear. If you want to change the plot area, for example, move the mouse until the Quick Tip reads Plot Area, then click. Sizing handles will appear around the plot area. Consequently, the format menu will apply to whatever area you have selected.
EXAMPLE
1.	Select the plot area.
2.	Click on Format, Selected plot area, then click on Fill Effects.
3.	Click on Gradient, Two colors. Select white for color one and yellow for color 2. Choose the horizontal shading style and the variant in the top left corner (light to dark). Click on OK, then click on OK again.
Your chart should look like this:
ADDING ANOTHER DATA RANGE If you decide later that you would like to add another data range, such as Florida:
EXAMPLE
1.	Select the data range A4:E4.
2.	Point the mouse to the edge of the selected data range. It should change to a white arrow.
3.	Click and drag the range to any point over your chart. The mouse will have a small black plus attached to it. Let go when the pointer is over the chart. The new range should appear in the chart. However, the ranges may not be in the correct order.
4.	Click any data series (one of the sets of columns). The selection handles should appear on the columns.
5.	Click on Format, Selected Data Series.
6.	Click on Series Order, select the series that needs to be moved and click on Move Up or Move Down until they are in the proper order. The legend should change also.
USING A PICTURE FOR THE DATA SERIES It is very easy to use a series of pictures instead of a column to represent your data series.
EXAMPLE
1.	Click on the data series that represents Florida. The selection handles should move to that series.
2.	Click on Format, Selected Data Series, Patterns, Fill Effects, Picture.
3.	Click on Select Picture. You can choose almost any picture, but the pictures are going to be very small, so simpler pictures are better. You will find the clipart files and other picture files at C:\Program Files\Microsoft Office\Clipart. For this exercise we will use jigsaw in the Popular folder.
4.	Under format on the picture tab, chose from Stretch, Stack or Stack and Scale. For this exercise, we will choose Stack and Scale to 50 Units/Picture.
5.	Click on OK, then OK again, and your column should now contain the jigsaw picture. Notice that the legend has also changed.
Your chart should look like this:
ROTATION IN 3-D CHARTS Sometimes small data series get lost in 3-D charts, hiding behind taller columns. Or, in a pie chart, you may want to rotate the pie to display a data point more prominently. Both of these can be accomplished using the 3-D effects (which only work on 3-D charts).
EXAMPLE
1.	Using the Widget Sales info, select A2:B8. This is the 1996 data series for all states.
2.	Using Chart Wizard, create a pie with 3-D visual effect (2 choice, top row of step 1 in Chart Wizard). Choose New Sheet in Step 4 of the Chart Wizard. Otherwise, accept all defaults.
3.	Click on Chart, 3-D view.
4.	Change the Elevation to 30 and the Rotation to 320. Click Apply at any time to see the effects of your changes on the chart. Click on OK to return to the chart.
5.	Save the workbook, using the same name, Widget Sales.
CREATING A DATA MAP Excel can work with geographical maps through a program called Microsoft Map. Data must be presented in some list of geographical names that Excel can recognize, such as states or countries.
If you do not have the map icon on your toolbar, click on Tools, Customize, Commands, Insert, Map. Drag the Map icon to the standard toolbar. A good place to put it is right next to the chart icon. EXAMPLE
1.	Change to Sheet 1 of the Widget Sales workbook.
2.	Select A2:B8, the sales for 1996 throughout the southeastern region.
3.	Click on Map on the standard toolbar.
4.	Scroll down the worksheet downward far enough to have a place to work, then move the mouse over the worksheet. It should have a small black plus sign attached. Draw a box by clicking and dragging down and to the right. When you let go of the button, MS Map should start automatically.
5.	Your first choice is whether or not to include Alaska and Hawaii in the map. We will select the second choice, United States in North America, since we have no need for Hawaii and Alaska. Click on OK. The map of America should appear, along with the Map Control box. If the Map Control box does not appear, click on the Show/Hide Microsoft Map Control button on the toolbar.
6.	To add color to the states, point to the Category Shading icon in the Map Control box. A handle should appear. Click and drag it out into the Map Control box, placing it over the Value Shading icon that was already there. Let go, and the states should change from shades of gray to colors. Other icons in the Map Control box add dot density, graduated symbols, pie charts, and column charts. All of these require that you drag Column A or B down from the top of the Map Control box. The pie and column charts icon add a pie or column to each state. The pies or charts show multiple data series, i.e., if we had chosen the ranges for 1996, 1997, 1998 and 1999 (projected).
7.	Move the Map Control box over by clicking and dragging on the title bar, or turn it off by clicking on the X.
8.	To Zoom the map in on the southern states, click on Zoom Percentage of Map on the toolbar. Change to 550%, either by keying it in or selecting it out of the drop down list. If 550% is not listed, choose an appropriate amount. The point is to get the southern states bigger on the map.
9.	To move the southern states into the center of the map, click on Grab on the toolbar, then point to the map, click and drag until the southern states move to the center.
10.	Continue adjusting the size of the map box and the Zoom until the southern states are featured prominently in the map box. Turn off Grab when you have completed your adjustments.
11.	Change the title by clicking on the current title - North America. The title will be selected. Double-click again on North America and you will go into the editing mode. Delete North America and key in Southeastern States. Click outside the title box. Center the title by clicking on the title box, then clicking and dragging into position.
12.	To change the colors on the map, click on Map, Category Shading Options. Select the appropriate state and change the color.
13.	Expand the legend by right clicking on the legend, then selecting Compact. You can position the legend anywhere you desire by clicking and dragging.
14.	To leave Microsoft Map, click outside the map box. Microsoft Map will close and return to the Excel worksheet. The map can then be cut and pasted into any other MS product.
Your finished map should look similar to this one:
Save Widget Sales.

RANGE NAMES If you are constantly referring to a certain range of cells, assign a range name to save time. Range names take the place of cell references in formulas. Range names must be unique within the workbook. They can contain no spaces.
1.	Select the range of cells to be named.
2.	Click on the Name box, which is located right above the A column.
3.	Key in the range name.
4.	Press Enter.
To delete a range name:
1.	Click on Insert, Name, Define.
2.	Select the range name and click on Delete.
EXAMPLE: In the Widget Sales workbook, select B3:E8. Name the range Sales.
FORMULA REVIEW All formulas start with an =. PEMDAS rules: Parentheses, exponents, multiplication/division, addition/subtraction. You can not do mathematical operation on cells that contain text. Excel contains many built-in formulas such as MAX, MIN, AVERAGE, SUM, and COUNT. You can key these in, or look for them by clicking on the Paste Function icon.
EXAMPLE:
1.	In cell B9, key in =b3+b4+b5+b6+b7+b8.
2.	In cell C9, key in =SUM(c3:c8)
3.	In cell A10, key in Maximum.
4.	In cell A11, key in Minimum.
5.	In cell A12, key in Average.
6.	In cell A13, key in Count.
7.	In cell B10, key in =max(sales)
8.	In cell B11, key in =min(sales)
9.	In cell B12, key in =average (sales)
10.	In cell B13, key in count(sales)
	Your worksheet should look like this:
VIEWING FORMULAS Occasionally, you need to see the formulas on the entire worksheet. Click on Tools, Options, View, Formulas or press Ctrl+`. This shows every formula in the worksheet and changes the width of every column in the worksheet to 8.43 characters. To set the worksheet back to normal, repeat the process.
AUTOSUM The AutoSum icon calculates totals above or to the left of the current cell. This is a very quick way to total numbers.
EXAMPLE
1.	Select cells B9:C9 and press delete. This removes any formulas that were in these cells.
2.	Click on the Name box and select Sales. B3:E8 should be selected. Click on the AutoSum icon. The sums for each year should appear in B9:E9.
3.	Select F3:F9 and click on AutoSum again. The total for each state should appear in F3:F9.
AUTOCALCULATE AutoCalculate will give you a preview of what your results are going to be. It is located in the status bar at the bottom of the screen and appears when you have a range selected.
EXAMPLE
1.	Select F3:F8. You should see SUM=1614 in the AutoCalculate area. If you want to see some other statistic on this range, right click on the AutoCalculate icon and choose from the list.
	Your worksheet should look like this:
USING THE IF FUNCTION The If function is a logical function that allows Excel to make decisions based on criteria you have given it, such as if sales are below a certain figure, that region needs to retrain salesmen. The IF function is written in this manner: =IF(question, do this, or do this). Example: If sales for Alabama are below 250, retrain salesmen, reward salesmen would be written =IF(F3<250,"retrain","reward"). No part of the IF function may be omitted, or an error message may occur. You can use text in the "do this" part as long as it is in " ". Commas separate each section of the IF function, and the commas are not included inside the " ". You may also give some mathematical instructions, such as if the sales in any state are <250, you would like to see a sales increase of 25%, if the sales are >250, you want to see only a 10% sales increase. This would be written for Alabama as =IF(F3<250, F3*25%,F3*10%). There are no spaces in the IF function.
EXAMPLE
1.	In cell G3, key in =IF(F3<250,"retrain","reward")
2.	Copy the formula to cells G4:G8.
	Your worksheet should look like this:
WORKING WITH DATES Excel views dates as serial date numbers, beginning with Jan. 1, 1900 as day 1 and counting forward. By viewing dates as numbers, Excel can perform mathematical calculations on dates. Although Excel views dates as numbers, you can format them to appear as dates by either keying them in as dates, or by clicking on Format, Cells, Number, Date. Occasionally, Excel takes leave of its senses and formats a date that you keyed in in date format as a number. If this happens, used Format, Cell, Number, Date to correct the format.
1.	Pick any cell (i.e. K1) and enter today's date. Hint: Key in =Today() with no space inside the ( )'s.
2.	Move down one cell (i.e. K2) and key in the date for Christmas this year, i.e., 12/25/99.
3.	How many days 'til Christmas? Move down one more cell (i.e. A3) and key in a formula to find out, i.e. =k2-k1. You may need to change the formatting of K3 from a date to a number.
WORKING WITH SHEETS By default, a workbook contains three sheets. More sheets can be added, or sheets can be renamed, re-arranged and deleted. They can also be copied. Sheets can also be grouped so that work can be done on multiple sheets.
ADDING SHEETS
1.	Point to the current sheet tab and right click.
2.	Click on Insert, Worksheet, OK.
RENAMING SHEETS
1.	Point to the sheet tab you want to rename and right click.
2.	Choose Rename, then key in the new name.
OR
1.	Double click the sheet name and key in the new name.
REARRANGING SHEETS
1.	Click on the sheet tab you want to move.
2.	Click and drag it to the desired location. A small indicator will show you the new location for the sheet.
DELETING A SHEET
1.	Click on the tab for the sheet to be deleted.
2.	Right click and select Delete. Click on OK.
COPYING A SHEET
1.	Click on the tab for the sheet to be copied.
2.	Hold CTRL down, then click and drag the tab to the right or left. An indicator will appear showing the location of the new sheet. Release the mouse, and the new sheet name will appear with (2) after it.
EXAMPLE Select sheet 1 and make a copy of it. Rename Sheet 1 as Widget Sales and Sheet 1 (2) as Classwork.
GROUPING SHEETS When sheets are grouped, any work done on one sheet will be duplicated on the other sheets in the group - think of this as carbon paper.
1.	Click on the first sheet in the group, hold Shift down, and click on the last sheet in the group. All sheet tabs in the group should turn white. Anything you do to one sheet in the group will be repeated on all sheets.
2.	To ungroup, right click on any sheet in the group, then click on Ungroup Sheets. You could also click on any sheet tab not in the group.
If the sheets you want to group are not consecutive, hold Ctrl down as you select the sheets instead of Shift.
WORKING WITH LISTS Databases are collections of information organized into rows and columns. Excel calls a database a list. Excel will organize a list and filter out requested information. Excel can not, however, establish relationships between lists, nor can it create forms and reports based on the list. This is the function of a true database program such as Access. Excel (and you) must be able to recognize the list - probably by column headers that have been formatted. Each row in the list is a record, and each column in the list is a field. The column headers are known as the field names, and these must be unique. All the fields for one entity make up the record for that entity. There should be no blank rows in the list, although there may be blank cells if you do not know some of the information. You must take care when entering data into a list. Consistency is a must - decide up front how you will enter data. A good example is state names - will you abbreviate them according to the two character postal format, or will you spell them out? What about zip codes - 5 digits or 9? If your list is inconsistent, containing both GA and Georgia, you will not be able to filter it to find all the records for the people who live in GA. In addition to being useful in Excel, lists can also be used as data files in Word MailMerge, or can be converted to a table in Access. Sheet 2 of the Widget Sales workbook contains a list of sales by state, the region that the state is in, and the number of salesmen in that state.
Example: Rename Sheet 2 as List.
APPLYING THE AUTOFILTER AutoFilter is the easiest method to filter or create a subset of the data you want to see.
1.	Click on any cell in the list.
2.	Click on Data, Filter, AutoFilter. Small arrows will appear on each column header.
3.	Click on the arrow in the Region column. All choices in that column will appear. Select SE, and the filtered list will appear. Notice the blue row headers on the left side of the screen. This indicates that some rows are hidden - not included in the filtered list. Also notice the blue arrow on the column header. This indicates a filter has been applied. You can filter by more than one field. Filter the first column, then using that filtered list, filter by a second column. Filtered lists will print exactly as they appear on the screen. This example shows the list with a filter applied to select all states in the SE region.
REMOVING A FILTER
1.	Click on any of the blue filter arrows, then click on All. If you continue to do this on all filtered columns, the entire list will re-appear.
TURNING OFF AUTOFILTER
1.	Click on Data, Filter, AutoFilter. You do not have to set each column header back to All before removing the AutoFilter.
APPLYING A CUSTOM AUTOFILTER Custom AutoFilters allow you to set conditions, such as greater than 100, as your filter.
1.	Make sure AutoFilter is turned on.
2.	Click on the filter arrow on the column header, then click on Custom. A dialog box will open for the custom AutoFilter conditions.
3.	Select the operator from the first column, such as greater than, equal to, etc., then either select from the second column or key in your entry. You can also choose a second condition, combined with the And or Or button. If you set two conditions and select the And button, both conditions must be true for the record to be returned in the filtered set. If you set two conditions and select the Or button, the record will be included in the filtered set if either condition is true. You can also use wildcards * and ? in your conditions. When you have entered your conditions, click on OK. Remove a custom AutoFilter by clicking on the filter arrow, then choosing All.
WORKING WITH SUBTOTALS Lists can also be totaled using Data Subtotals. Again, you must be consistent in creating your list. The Data subtotal depends on changes in the entries to calculate the subtotals. If you have entered GA and Georgia as entries, you will get a subtotal for both entries instead of one subtotal for the state of Georgia. Lists must be sorted before you apply subtotals.
Example: What is the total number of salesmen in each region in our list?
1.	Remove all AutoFilters
2.	Click anywhere in the Region column, then sort A-Z.
3.	Click on Data, Subtotals. The Subtotal dialog box should appear.
4.	Click the list arrow at the right of the At each change in box. Click on Region.
5.	Click the arrow to the right of the Use function box. Click on Sum.
6.	In the Add subtotal to box, select Salesmen, and deselect all other boxes
7.	In the bottom of the dialog box, be sure that Replace current subtotals and Summary below data are both selected. Click on OK.
	The results for the first region - MW - are shown below:
Notice the 1-2-3 indicators on the left side. By clicking on these you can reduce your list to show all the information, the subtotals and the grand total, or only the grand total. To remove data subtotals, click on Data, Subtotals, Remove all.
LINKING CELLS Information can be shared between programs in a number of different ways. You can cut and paste from one program to another, use Embedding, or create a link. Links consist of two parts, the source and the destination. Links can be used in any program that supports OLE (Object linking and Embedding, and most Windows based programs support this). When a link is established, if the source changes, the destination changes also. This means less time and work for you, since you will not have to remember to make changes in several places. Links can be used between cells on one worksheet, between worksheets or between files. If it is between files, both files in the link do not have to be open for the link to work. If you make a change in the source, the next time you open the destination file, you will be asked if you wish to update the links.
Example: Create a link between cell E9 on the Widget Sales worksheet (the total projected sales for 1999) and a cell A2 on a new sheet.
1.	Remove any Data Subtotals. Insert a new blank worksheet and name it Memo.
2.	In cell A1 of the Memo sheet, key in: Dear Boss: Our new sales projection for 1999 is.
3.	Change to the Widget Sales sheet (sheet 1) and click on cell E9. Click on copy.
4.	Change to the Memo sheet and click on cell A2. Click on Edit, Paste Special, Paste Link. The total from cell E9 on the Widget Sales sheet should appear in the cell.
5.	Change to the Widget Sales sheet. Change the contents of cell E4 to 25. Notice that cell E9 changes. Change to the Memo sheet. The contents of cell A2 should have changed also.
Click on cell A2 on the Memo sheet and look at the formula bar. The entry begins with an =, followed by the worksheet name (in single quotes since there is a space in the name) followed by an !, then finally an absolute reference to the cell address. You could have created the link by keying this entry directly into cell A2.
DO YOU WANT TO KNOW THE SHORTCUT FOR CREATING LINKS? You create the link backwards, starting from the destination cell instead of from the source.
1.	Delete the contents of cell A2 on the Memo sheet. You have broken the link.
2.	Click on cell A2, press =
3.	Change to the Widget Sales sheet and click on cell E9. Press Enter. The link has been established.