Excel+for+Librarians

 **Introduction**

 This professional development is designed to help librarians become familiar and comfortable with Excel applications. We will point out uses and possible integrations of Excel applications found in Microsoft Office. We will then discuss more advanced uses at the bottom of the page.

Professional Development Standards for This Training
ISTE NETS 2008 for Teachers

Teachers use their knowledge of subject matter, teaching and learning, and technology to facilitate experiences that advance student learning, creativity, and innovation in both face-to-face and virtual environments. Teachers: a. promote, support, and model creative and innovative thinking and inventiveness. b. engage students in exploring real-world issues and solving authentic problems using digital tools and resources.
 * 1. Facilitate and Inspire Student Learning and Creativity **

Teachers exhibit knowledge, skills, and work processes representative of an innovative professional in a global and digital society. Teachers: a. demonstrate fluency in technology systems and the transfer of current knowledge to new technologies and situations.
 * 3. Model Digital-Age Work and Learning **


 * Why Use Excel?** Students make charts and graphs from spreadsheet records and real world data, learning to **organize** their ideas and **present information** to an audience. **Charts** add meaning to information, helping students to analyze and interpret data, as they identify the maximum and minimum, mean, median and mode of a data set. The computer can generate **bar graphs, line graphs** and **pie charts**. Pie charts reinforce the idea of **percentages** as these are represented visually, and help students compare **ratios**. Students make their own Bar Graphs by filling cells with color, and Clip Art is used to make **Pictographs**.

The term spreadsheet was derived from a large piece of paper that accountants used for business finances. The accountant would spread information like costs, payments, taxes, income, etc out on a single, big, oversized sheet of paper to get a complete financial overview.


 * Relevancy** Spreadsheets allow the use of real time data. The Internet provides students with access to current data that cannot be found in any other source. Real time data is dynamic changing information such as weather conditions, current news stories, stock market prices, census data, or volcano and earthquake conditions. Textbook data can be outdated. There are many scientific posts on the Internet that students can attain data to analyze and evaluate. These sites allow students to become actively involved in their learning and assume the role of researchers exploring trends and correlations and creating their own connections. We will explore some real time data sites that you could use as a the data source for spreadsheet activities.

1. If you have text all in one column that you would like in two different columns, such as, first name and last name in one single column. 2. Click on the **Data** tab in the Ribbon. 3. Under the heading of Tools, click on **Text to Columns**. 4. Make sure **Delimited** is selected, click **Next**. 5. Selected the way that the data is separated, such as, tab, space, comma, etc. Click **Next**. 6. Select Column data format: general, text, date for each column. Click **Finish**.
 * Separating Text into Columns**

**Tips for Entering Formulas** 1. Formulas are just equations. Instead of adding or subtracting numbers you are adding and subtracting the contents of a cell. 2. Excel includes a number of predefined functions to use as well. You can combine the predefined functions into your own formula. 3. When you type in formulas, the order of operations must be correct. The correct order is: parentheses, exponents, multiplication, division, addition, then subtraction. 4. Remember if you use formulas with equations in the numerator and denominator, you need to use parenthesis to force the spreadsheet to calculate them properly. 5. Make sure when copying a formula that you are copying the formula and not the results.

**I****nserting a Chart** 1. Select columns that you want to graph (make sure that you include the titles). 2. On a PC, click F11 and your chart will appear. 3. On a Mac, click the **Chart icon on the** Ribbon. **(Or Click** __I__nsert > Ch__a__rt... **The "Chart Wizard" window appears.)** 4. Select the type of graph (chart) that you would like to create (bar, pie, linear, etc.). 5. If using the **Chart Wizard**:  a. Then select **Next>>** three times.  b. Type in the **Title** and a **Subtitle**, if desired.  c. Then select **Finish**. 6. You can change the titles, colors and preferences by double clicking on the graph on the items you would like to change. 7. You can insert the chart on a different sheet by going to **Chart** on the top menu and selecting move chart.

 **Absolute and Relative Cell Referencing**  1. Enter 10 in A1 and 20 in A2. <span style="font-family: Arial,Helvetica,sans-serif;"> 2. Click in cell B2 and type =A1+A2, then Enter. You should get 30. (10+20=30) <span style="font-family: Arial,Helvetica,sans-serif;"> 3. Now click in cell B2, Cntl Click, Copy. <span style="font-family: Arial,Helvetica,sans-serif;"> 4. Click in cell B3, Cntl Click, Paste. I got 20?? <span style="font-family: Arial,Helvetica,sans-serif;"> 5. Odd 10+20 is not 20. This is because the default for cell referencing in Excel is Relative. Which means that when we thought we were copying the formula into Cell B3 we actually copied the movements of up one and left one which is where A1 and A2 are relatively located from B2. <span style="font-family: Arial,Helvetica,sans-serif;"> 6. Absolute cell referencing will cause this to work properly. To reference a cell using absolute cell referencing you need to use a dollar sign ($). To actually copy the formula not the spaces moved you need to put the formula in as =$A$1+$A$2. <span style="font-family: Arial,Helvetica,sans-serif;"> 7. You can mix relative and absolute cell referencing.

Example of Absolute and Relative Cell Referencing

<span style="font-family: 'Arial Black',Gadget,sans-serif;">How to Keep Headings when Scrolling - If you are creating a long list and would like your heading to remain visible throughout the document when viewing: 1. Click in cell A2 to freeze horizontal heading, and click cell B1 to freeze the vertical heading. 2. Go to Window>Freeze Pane. (You should see a line appear under your heading.)

To keep both vertical/horizontal headings, put cursor in B2 and then click Window>Freeze Pane.

If you would like to print the heading on each page: 1. Click the Layout Tab. 2. Click Repeat Titles Icon, and then highlight your heading that you would like repeated.

<span style="font-family: 'Arial Black',Gadget,sans-serif;">How to Create a Banner in Excel - <span style="font-family: Arial,Helvetica,sans-serif;">If you are one of the schools that doesn't have a poster maker and would like to create a banner, here are the instructions for creating a banner in Excel. Please realize that it will involve some cutting and taping/gluing in order to create.

<span style="font-family: 'Arial Black',Gadget,sans-serif;">How to Create a Drop Down Menu - If you would like to create drop down menus in an excel document, this document will show you how.

<span style="font-family: 'Arial Black',Gadget,sans-serif;">Conditional Formatting - <span style="font-family: Arial,Helvetica,sans-serif;">This feature allows you to quickly see differences in a set of data by color coding the data for you. You set the conditions and color/style that you want and it does the rest. Quick Start Guide to Conditional Formatting Data for Conditional Formatting Example:

<span style="font-family: 'Arial Black',Gadget,sans-serif;"> Sparklines - <span style="font-family: Arial,Helvetica,sans-serif;">If you would like a small chart beside of your data for different rows, you might want to check out Sparklines. They are like little mini charts for your data. You can find Sparklines under the Charts Tab. Select the cell where you would like the Sparkline to appear. You select which type of Sparkline will best fit your data: line graph, column graph or win/loss graph. Select the data you would like to appear in the Sparkline.
 * Use same data from Conditional Formatting for an Example.

<span style="font-family: 'Arial Black',Gadget,sans-serif;">How to Link Across Workbooks/Sheets - If you would like to link one worksheet to another or a part of a worksheet to another, here are the instructions.

<span style="font-family: Verdana,Geneva,sans-serif;">**Tutorials** <span style="font-family: Verdana,Geneva,sans-serif;">**Excel 2010 PC version tutorial:** <span style="font-family: 'Arial Black',Gadget,sans-serif;">@http://office.microsoft.com/en-us/excel-help/getting-started-with-excel-2010-HA010370218.aspx

<span style="font-family: Verdana,Geneva,sans-serif;">**Excel 2011 Mac version tutorial: <span style="font-family: Verdana,Geneva,sans-serif;">@http://www.microsoft.com/mac/excel/getting-started-with-excel ** <span style="font-family: Verdana,Geneva,sans-serif;">**<span style="font-family: Verdana,Geneva,sans-serif;">[|How to Make a Pictogram] **