What is Excel and When Would I Use it?
Excel is an Electronic Spreadsheet Program
Excel is an electronic spreadsheet program that can be used for storing, organizing and manipulating data.
When you look at the Excel screen (refer to the example on this page) you see a rectangular table or grid of rows and columns.
The horizontal rows are identified by numbers (1,2,3) and the vertical columns with letters of the alphabet (A,B,C). For columns beyond 26, columns are identified by two or more letters such as AA, AB, AC.
The intersection point between a column and a row is a small rectangular box known as a cell. A cell is the basic unit for storing data in the spreadsheet. Because an Excel spreadsheet contains thousands of these cells, each is given a cell reference or address to identify it.
The cell reference is a combination of the column letter and the
row number such as A3, B6, AA345.
Data Types, Formulas, and Functions
The types of data that a cell can hold include numbers, text or formulas. Just as in math class, formulas are used for calculations usually involving data contained in other cells. Excel includes a number of built in formulas used for common tasks known as functions.
Excel and Financial Data
Spreadsheets are often used to store financial data. Formulas and functions that are used on this type of data include:
- Performing basic mathematical operations such as summing columns and rows of figures.
- Finding values such as profit or loss.
- Finding the average, maximum, or minimum values in a specified range of data.
Excel's Other Uses
Excel Projects for Teachers:
Other common operations that Excel can be used for include:
- graphing or charting data to assist users in identifying data trends.
- sorting and filtering data to find specific information.
The information displayed and collected in a spreadsheet can easily be incorporated into electronic presentations, web pages, or printed off in report form.
Different types of graphs serve different purposes.
Pie Graphs -- are used to show percentages. For example, a pie graph could be used to show what percentage of your total daily calorie intake is represented by one quadruple cheese and bacon hamburger.
Bar Graphs -- are used to show comparisons between items of data. Each column in the graph represents the value of one item of data. An example of this would be to compare the calories in a quadruple cheese and bacon hamburger with the calories in a glass of water and a bowl of beet greens.
Line Graphs -- are used to show trends over time. Each line in the graph shows the changes in the value of one item of data. For example you could show changes in the values of three different stocks over a period of months.
There are many choices when it comes to charts in Excel - pie charts, bar charts, line charts, and column charts are just a few of the chart types available. No matter which chart type you choose, remember, the first step in creating a chart is to select the data you are going to use before creating it.
Plan Your Spreadsheet
Before you begin entering data into a spreadsheet it is a good idea to do a bit of planning before you begin to type.
Points to consider
- What is the purpose of the spreadsheet?
- What information needs to be included?
- What headings are needed to explain the information in the spreadsheet?
- What is the best layout for the information? in rows or columns?
Some General Rules
- Whenever possible, don't leave blank rows or columns when entering your data.
◦Leaving blank rows and columns in data tables can make it difficult to use a number of Excel's built in features such as graphing and functions.
- Enter your data in columns when possible - as seen in the image above.
◦When laying out your spreadsheet, place a title describing the data at the top of the first column of the table with the data below.
◦If there is more than one data series, list them one after the other in columns (left to right) with the title for each data series at the top.
Entering your data into a spreadsheet is always a three step process.
These steps are:
1. Click on the cell where you want the data to go.
2. Type your data into the cell.
3. Press the ENTER key on the keyboard or click on another cell with the mouse.
Speeding up data entry
Many people use the mouse when moving around their spreadsheet. Using the mouse, though, is the slow way of doing anything on a computer. It's fine if you have only a small amount of data to enter or if you're not in a hurry.
To speed up your data entry use the keyboard. Below is a list of keys that you can use when you want to quickly enter your data.
- Enter key: enters the data and moves the active cell highlight down to the next cell in the current column.
- Tab key: enters the data and moves the active cell highlight to the next cell in the current row.
- Arrow keys: enters the data and moves the active cell highlight to the next cell in the direction of the specific arrow key pressed. For example, if the up arrow is pressed, the active cell highlight moves up to the next cell in the current column.
- Esc key: cancels the current data entry.
Using AutoComplete to Enter Data
- Excel’s AutoComplete feature is intended to simplify the task of data entry.
When entering labels down a column, if you begin typing text that has previously been entered, Excel will display a black box containing the previous entry in the cell you are entering data into.
- If you want to reenter the same text, press the Enter key and Excel enters the text for you.
- If you are entering a different label, continue typing and the AutoComplete box will go away.
Limitations of AutoComplete are:
- It only works for data being entered in columns - it will not work if you are entering text across a row.
- It only works for columns of continuous data. As soon as an empty cell is left in a column, AutoComplete is interrupted.
Types of data in Excel There are three different types of data in Excel:
- labels
2. values
- dates/times
A label is an entry that is usually used for headings, names, and for identifying columns of data. Labels can contain letters and numbers. By default, labels are left aligned in a cell.
A value contains numbers and can be used in calculations. By default, values are right aligned in a cell.
Date/time data is just that, a date or the time entered into a cell. By default, date/time data is right aligned in a cell. Sometimes dates are combined with numbers and considered to be values instead of a separate type of data.
Excel Formatting Tips
How to Use Excel's AutoFormat Option
Formatting a spreadsheet effectively can make the most important information on the spreadsheet stand out from the rest of the data. This tip shows you how to use one of the 17 AutoFormat styles available in Excel to give your spreadsheets a professional look that makes them easy to read.
How to Use Excel's Format Painter Option
This tip shows you how to quickly copy formatting from one cell or group of cells to another part of the spreadsheet using the format painter in Excel. The format painter is very useful when you are extending a spreadsheet to accommodate new data and you need to format the new cells to match the original data area.
Changing the Sheet Tab Color in Excel
In Microsoft Excel 2003 it is possible to color code the tabs of worksheets. This tip show you just how this is done. Changing the color of worksheets tabs containing related information can make it easier to navigate through very large worksheets.
Add and Delete Columns and Rows in Excel
It’s a rare occasion when a spreadsheet doesn’t need a change of layout before it is finished. This tip will show you how, with a click of the mouse, you can easily add that extra column or delete those unwanted rows.
Excel Chart Tips
Creating a Custom Excel Chart
If you have created a chart in Excel, complete with custom formatting, that you would like to use again with other charts save it as a custom chart type. Step by step instructions on creating a custom chart type in Excel.
Changing Excel's Default Chart Type
If you don't like the default Excel chart type you can change it. This article shows you how to change the default chart type in Excel.
One Touch Excel Charts
You can create an Excel Chart with one keystroke. If you ever need a chart in a hurry or you just want to check on certain trends in your data, you can quickly create a chart in Excel that uses all the default chart settings.
How Do You Use Excel's Flowchart Template?
Among the templates offered for free by Microsoft is one for creating simple flowcharts in Excel. The template works in all versions from Excel 97 on. A flowchart is a graphical representation of a process. This tip walks through how to download and use the flowchart template from Microsoft.
Other Excel Tips
Show Excel Toolbars on Two Rows
The default setting for Excel toolbars is to display them on a single row, thereby hiding many of the toolbar buttons. This tip shows you how easy it is to change the default settings to show Excel toolbars on two rows.
Excel Function Tips
Date Functions
There are a number of date functions available in Excel. Depending on your needs, you can use a date function in Excel to return the current date, the current time, or the day of the week. This article covers the most commonly used date functions used in Excel.
Excel's COUNT Function
Excel’s COUNT function is one of a group of “Count Functions” that can be used when you need to total the number of cells in a selected range.The COUNT function will add up the number of cells in a selected range that contains numbers.
Using the COUNTA Function in Excel
Although similar to the COUNT function above, the COUNTA function is a little more versatile. The COUNTA function will count up the number of cells containing data – either numbers or labels in a selected range rather than just cells containing numbers, which is what the COUNT function does.
Excel AVERAGE Function
Excel’s AVERAGE function is one of the “Statistical Functions” that are included with the program. The AVERAGE function can be used to find the average, or arithmetic mean, of values in a selected range of cells.
The Excel OR function
The OR function is one of Excel’s Logical Functions. Logical functions, in general, only give you only a true or false answer. They evaluate the contents of a cell to see if it meets the specified criteria. If so, the function returns a True response. If not, a False statement is returned.
Excel IF Functions
The IF function is one of Excel’s most useful functions. What it does is test to see whether a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else. Below are a number of tips on using the IF function in different situations.
Nesting IF Functions in Excel
To increase the flexibility of the function, one IF function can be "nested" inside another. Read this article to find out how to nest one IF function inside another.
Leaving Blank Cells With the IF Function
One option for using the IF function is to have it leave cells blank, depending on whether the tested condition is true or false.
Enter Text with the IF Function
A second option when using the IF function is to have it enter different words into a cell, depending on whether the tested condition is true or false.
Using the IF Function to Enter Data
A third option for using the IF function is to have it enter values or numbers into target cells, depending on whether the tested condition is true or false.
Performing Calculations with the IF Function
A fourth option for using the IF function is to have it perform different calculations depending whether the function returns a true value or not.
IF Function Comparison Operators
The logic test section of an Excel IF function is always a comparison between two values. To accomplish this, a “comparison operator”, such as the greater than symbol ( > ), is always placed between the two amounts being compared. This article is a handy list of the different comparison operators that can be used in Excel IF functions.
Excel Formula Tips
Show Formulas in Excel
Show formulas makes it possible to look at all of the formulas and functions in your spreadsheet. With a quick key combination from the keyboard, you can:
- check to see which cells contain formulas
- read through all formulas to check for errors
- print the spreadsheet while showing all formulas
Using Cell References in Excel Formulas
Cell references describe the location of a cell in an Excel spreadsheet. Using cell references in formulas has several advantages including:
- Using pointing when creating the formula
- Allows you to reuse formulas by copying them to other cells
- Copied formulas automatically update cell references for their new location
- If the data used in the formula changes, the formula automatically updates the answer
"What if" Questions and Excel Formulas
Excel formulas can easily be used to answer "What if" - type questions. "What if" questions involve changing the data used in Excel formulas to give different answers. This article covers:
•How to setup "What if" questions
•The importance of using cell references in formulas
•A step by step example of how to use "What if" questions
Excel Math: How to Add Numbers
This tutorial covers:
•how to add numbers using a formula
•an example of addition in Excel
•how cell references will make it easy to update your calculations if your data should ever change
Excel Math: How to Subtract Numbers
This tutorial covers:
•how to subtract numbers using a formula
•an example of subtraction in Excel
•how cell references will make it easy to update your calculations if your data should ever change
Excel Math: How to Multiply Numbers
This tutorial covers:
•how to multiply numbers using a formula
•an example of multiplication in Excel
•how cell references will make it easy to update your calculations if your data should ever change
Excel Math: How to Divide Numbers
This tutorial covers:
•how to divide numbers using a formula
•an example of division in Excel
•how cell references will make it easy to update your calculations if your data should ever change
*********For more information go to:
Excel Tips, Tricks and Shortcuts for Microsoft Excel. See Also Excel Tips & Tricks Here
Shortcut Keys
On most of Excels menu items you will see the shortcut key associated with it. To see a complete list push F1 and type "Shortcut Keys".
Quick Help
To get quick help on any menu item push Shift+F1 and click the menu item
Insert Today's Date
To insert Today's date push Ctrl+; (semicolon)
Insert Current Time
To insert the current time push Ctrl+Shift+: (Colon)
Show the Paste Function (Function Wizard)
Push Ctrl+F3
Show the GoTo dialog
Push F5
Show the Paste Names dialog
Push F3. This will only work if you have named ranges.
Name a Range
To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name.
Go To a Named Range
To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5.
Edit a Named Range
To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3.
Headings as Range Names
Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3.
Named Formula
To make a Name refer to a constant formula e.g. "TaxRate", go to Insert>Name>Define and type TaxRate in the "Names in Workbook" box and 36% in the "Refers To". Now enter =(10*TaxRate) anywhere on the Worksheet.
Named Range List
To obtain a list of all Named Ranges and where they refer, select any blank cell (make sure you have no data underneath or 1 column over) and go to Insert>Name>Paste then Paste List.
Nested Formulas
To help write nested formulas (more than 1 formula in a single cell) use the "Paste Function" i.e. Insert>Function or Shift+F3. Select the function that you need, enter the reference, number or text then select the drop arrow to the left of the formula bar to add more Formulas. Doing it this way ensures all your parentheses are in the correct places.
Debugging Formulas
To troubleshoot complex formulas select the cell containing it and then click the = (Equal sign) to the left of the formula bar, this will activate the "Paste Function". To step through your formula simply click in the part of the formula you want to debug.
Personal Help
To add your own text to any of the Office Assistants help files, push F1, enter your question then open the file. Go to Options>Annotate and type in your own text then click OK. You will now notice a paperclip symbol next to the heading, this will let you know that you have added your own Help in a way you will understand.
Different Help
Sometimes the Office Assistant is not very helpful to your needs, so try the "Context and Index" help by either clicking Help>Context and Index or selecting "Help Topics" from any "Help" file.
Customizing Toolbars
Right click on any Toolbar and select "Customize" or push Ctrl+Shift+F10 twice then "Customize". Now click the "Commands Tab" and drag menu items both on and off the Toolbars. If things get a bit messy simply click the "Toolbars" tab and click "Reset". This will return all menu items to their default.
Quick Charts
To create quick charts, click anywhere within your data and push F11.
Worksheet Template
Set up your Worksheet how you want it e.g. formatting, formulas etc then delete all other sheets in the Workbook. Now go to File>Save or Alt+F2 and select "Template (*.xlt)" from the "Save as Type". Type a name and click "Save" Now right click on the sheet tab and select Insert you should see your Template sheet.
Secret Menu
Click in any cell, then move your mouse pointer over any border of the cell until the mouse pointer changes to an arrow, right click and drag to it's destination and then release.