Technology How To

Intro to Excel

Friday, October 28, 2011 11:16 am

Microsoft Excel is a spreadsheet program which allows you to easily store, organize, and manipulate data. Simply by entering data in the rows and columns one can then apply mathematical formulas and functions. You can also create graphs, tables, and run statistical analyses on the data, all using Excel’s easy to use tools and spreadsheet options.

**This guide is for Excel 2010. If you are using 2007, or an older version the screen shots will not match your version.

Getting Started

Excel is either located on your Windows 7 toolbar with a green little X symbol, or it can be found in the start menu under Microsoft Office Tools.

When you first open excel you should open to a basic spreadsheet, which looks like this:

Lets familiarize ourselves with the basic toolbar.

Home

•Clipboard- includes cut, copy and paste

•Font- edit font size and style

•Alignment- select alignment of text in spreadsheet field

•Number- format how the numbers in the cells are displayed

•Styles- conditional formatting, tables and cell styles all allow for professional spreadsheets

•Cells- insert, delete or format cells

•Editing- find, select, group and arrange data within cells

Insert

•Tables- insert basic table or pivot table

•Illustrations- insert clip art, picture from file, shapes or NEW Smart Art

•Charts- create any type of chart to represent your data

•Sparklines- insert trend line chart in single cell

•Filter- Quickly sort pivot tables

•Links- insert hyperlink

•Text- insert text box, header, footer, Word Art, symbols, etc.

Page Layout

•Themes- select from previously installed themes for your data

•Page Setup- edit margins, size, print area, orientation, etc.

•Scale to Fit- Stretch or shrink size of data to fit printed pages

•Sheet Options- select whether gridlines and headings are visible

•Arrange- Align and arrange inserted objects

Formulas

•Function Library- insert preset functions for data inputs

•Defined Names- Name cells so you can refer to them in formulas by that name

•Formula Auditing- includes all existing formula options including cell referral

•Calculations- specify when formulas are calculated

Data

•Get external data- retrieve data from the web, from Access, or from text

•Connections- Displays all data connections for the workbook

•Sort and Filter- sort and filer data based on specific criteria

•Data Tools- separate text into columns, remove duplicates, consolidate data, etc.

•Outline- group data for collapsible cells, ungroup cells, insert subtotal cells

•Analysis- Run descriptive and inferential statistics in Data Analysis, or use Solver to find solutions to “what if” analyses

Review

•Proofing- use spell check, research, thesaurus, etc.

Language- Translate novel words into a variety of languages

•Comments- Add new, edit and delete comments for slides

•Changes- Keep track of and protect changes within the workbook

Inking- edit spreadsheet freehand or highlight text

View

•Workbook Views- select from many different viewing formats

•Show/Hide- ruler, gridlines, formula bar and headings

•Zoom- in and out

•Windows- Add more windows to compare changes to workbook

•Macros- record new and view already existing macros

Now that you’re familiar with the basic tools of Excel, lets move on to basic uses of these tools.

Entering Formulas into Excel

Any formula entered into Excel must start with the = sign. In order to add two numbers, click in any cell, start with = and then follow the symbols for the math formula you want to use. After you are done finishing the formula, just press enter and Excel will solve it for you.

  • Addition: =5+5
  • Subtraction: =5-5
  • Multiplication: =5*5
  • Division: =5/5

**Note: Microsoft Excel follows the rules of PEMDAS (Parentheses/ Exponents, Multiplication/ Division, Addition/ Subtraction) when applying mathematical formulas. Therefore if you want to apply more than one function at once, make sure that you are using proper parentheses to make sure the forumlas are ordered the way you want them to be.

For example, if you want to add 5 and 7 and then multiply the total by 20, how would you enter this into excel?

5+7*2? or (5+7)*2?

The second one is correct. Because of PEMDAS, Excel will multiply 7 and 2 before adding 5 if there are no parentheses.

Now these are just simple formulas, but Excel can also do statistics such as finding the standard deviation, an interest rate, or the factorial of a number, just to name a few examples. To see a complete list of Excel’s formulas, simply click on the Formulas tab.

Don’t know what formula to use? Excel makes finding the perfect formula for any problem easy. Simply select Insert Function, as symbolized by the Fx button under the Formulas tab.Then proceed to type into the text box a short description of what you want the formula to do. Excel will compile a list of formulas that match your description and scroll through them, reading their descriptions until you find the one that matches your problem.

As an example of how Excel can make your life easier, pretend that you have a large series of vegetable preferences, as shown below. Let’s say that someone asks you a simple question: How many people prefer carrots? Now you can scroll down the entire list and count how many times you see “carrot”, or you can let excel do it for you. Simply use the “Count If” formula. Which allows you to choose a range, and to count it if the cell has a certain characteristic. For this sample data, you would use the following formula: =COUNTIF (A1:A50, “carrot”). This will give you the total number of carrots, which here is 3.

Data Organization

If you want to format cells to show numbers as percentages, dollar amounts, fractions, etc. simply highlight the cell (s), you want to change and right click, then click format cells. From here you can choose the proper formatting desired.

In order to organize your data more effectively, it may be necessary to create a table or graph.

Graph:

In order to make a graph, make sure first that you have labels for the columns you want in the graph.

i. The column headers will turn into the x axis

ii. The row headings will turn into the lege

nd

iii. The data in the middle will turn into the data points

 

Here I made the labels Name and Sales. To create a graph, simply highlight the columns you want to include, click the Insert tab, and then the type of graph you want: column, line, pie, bar, etc. I chose to make a bar graph.

If you messed up the x and y axes, and wish to switch them, don’t worry, just simply click the “switch rows/ columns” button located under the chart tools tab.

In order to edit the title of the graph or to add titles to the x or y axes simply change the chart layout. The different layouts can be seen under the chart tools tab.

In addition to the first two uses you can also edit the chart styles, chart type, and data used under this tab.

Making the Graph into a Story:

If people can’t understand what your graph is saying, then it isn’t going to be an effective graph. Below are some tips to make sure that your graph is understood by the average viewer.

Adding Labels to the Graph itself

Adding labels to the individual columns in a graph is a great way to make sure that people understand exactly what they are looking at. For example, in the graph below, you cannot really tell what column is what country (especially if the colors are distorted when the graph is printed).

 

In order to fix this problem, right click on a column, select Add Data Labels. This will automatically add the y value (life expectancy) over the column. In order to get the country, right click again, and select Format Data Labels. From here you can click the check boxes so that the label contains the series name (which in this example is the country), the value (which in this example is the life expectancy), or the category name (the x axis label for that bar, in this example it is life expectancy-female/male).

After the labels are added, your data will be much easier to interpret.

Adding a Second Axis:

Sometimes it is necessary to put variables into a graph that have totally different scales. For example, population of a country would be on a much larger scale than life expectancy. If these two variables are simply added to a graph, it will not be easy see the variable with the smaller scale, as shown in the example below.

In order to fix this problem it is sometimes helpful to put the smaller variable in a different type of graph.You can accomplish this by right clicking on one of the life expectancy columns and selecting Change Series Chart Type, and then selecting a different type of graph from the menu that pops up. In this case I chose to graph life expectancy in a line graph.

However, this didn’t help in this case due to such the large extreme difference in axes. For this reason it is necessary to create a second axis for the life expectancy data. In order to do this, right click again on the data you want the new axis for (in this example, the red line). Select Format Data Series, and then in the Plot Series menu which appears, choose to plot on a secondary axis. After doing this, you still might want to add data labels to make it clear which axis corresponds to which data, but by doing this you will get a much more readable graph, as shown below.

 

Remember: if your audience doesn’t understand your graph, then your graph isn’t of much use. Make it tell a story!

Table:

In order to make a table, simply highlight the rows and columns you want to put in the table, click the Insert tab, and then the “table” button.

**Make sure your rows and columns have headers.

Excel will ask you what rows and columns you want in your table (it will automatically put the coordinates of the ones you highlighted), and then ask if you want headers.

If you keep this checked, Excel will automatically make the first row in all columns highlighted the headers. Otherwise, it will insert headers entitled column 1, column 2, etc.

?

I chose to keep the headers checked, giving me this table:

Under the Table Tools tab, you can change the change the table style, size, and colors, as well as export it to other programs.

Useful Uses of the table

Besides making your data look neater, a table also allows you to effectively sort data. By clicking on the drop down arrows next to each column you can choose to sort your data by alphabetical order, by color, or by row. For example: if you had a very long list of data and only wanted to see Molly’s sales, you could un-check everyoneelse’s names by clicking on the drop down arrow to the right of “name”and then only leave Molly’s name checked.

By hitting the “Ok” button, you will see a chart pop up that only shows Molly’s sales.

What is cool about this feature is that the graph will also follow the chart settings, so that all other bars on the graphdisappearexcept for Molly’s.

 

Printing

In order to print your graph or table, simply click on the Page Setup icon in the main toolbar. This button looks like a printer with a page next to it. I suggest first looking at the print preview in order to see what exactly will be printing.

**Doing this option will only print the graph and/or table

In order to print the spreadsheet information as- is, you must highlight the information you want printed first, then proceed normally.

 

Other Useful Tools

Wrap text

Under the Home tab in the Alignment column you will find the wrap text button. This is useful if you have a long cell of text and prefer for the cells to be longer, rather than wider, so that all the text can fit.

When using this option, you can go from this:

To this:

Merge and Center

Under the Home tab in the Alignment column you will find the merge and center button. This is useful if you have a heading in one cell, but choose to list sub-headings in two adjacent cells rather than just down the column. By using the merge and center button, you can center the heading between the two sub headings, and create a cleaner table that looks like this:

 

Click here for an accompanying spreadsheet that highlights many of these functions of Excel.

 

 



Archives
February 2013
March 2012
November 2011
October 2011
February 2011
August 2010
April 2010
March 2009
August 2008
April 2008
Categories
Microsoft Excel
Uncategorized
Zotero
Tags

Powered by WordPress.org, protected by Akismet. Blog with WordPress.com.