Post by PickyChicky on Apr 7, 2014 0:24:46 GMT -6
How to Use Excel is a series of Excel tutorials on About.com that is designed with the absolute beginner in mind. These tutorials include step by step examples and will show you how to use Excel to create a basic spreadsheet. You'll notice that the tutorials jump between Excel 2003, 2007, 2010, and 2013, but you'll find that the same information applies to each.
While going through the various help pages, you'll see a number of glossary terms that are linked to in blue. If you're unsure of what something is, simply right-click it and opt to open it in a new tab for more information. Just be sure not to get off track and go back to the previous tab to continue with the tutorial.
To get started, you can either go directly to the source link (below) or pace yourself by going through the individual pages organized in the above tabs (the latter is recommended for true beginners):
How to Use Excel - Excel Tutorials for Beginners
[PTabbedContent=Beginners Guide to Excel][PTab=Anatomy of a Spreadsheet]This section identifies the main parts of the Excel spreadsheet work area. There are links from each label to glossary terms describing each part in greater detail:
Overview
Excel is an electronic spreadsheet program that is used for storing, organizing and manipulating data.
Data is stored in individual cells that are usually organized in a series of columns and rows in a worksheet. This collection of columns and rows is referred to as a table. Tables use headings in the top row and down the left side of the table to identify the data stored in the table.
Excel can also perform calculations on the data using formulas. And to help make it easier to find and read the information in a worksheet, Excel has a number of formatting features that can be applied to individual cells, rows and columns, or entire tables of data.
Since each worksheet in Excel contains billions of cells per worksheet, each cell has an address known as a cell reference (or cell address) so that it can be referenced in formulas, charts, and other features of the program. For example, the very first cell's reference is A1 for column A, row 1. Keep this in mind for later when Absolute and Relative References are discussed under The Basics tab.
Before you begin with everything that is covered in this reference, I strongly recommend that those who are truly beginners first go through this Excel Step by Step Basic Tutorial. Doing so will better assist you in recognizing and understanding the various aspects, features and functions discussed in this reference.
The 9-step tutorial covers the steps necessary to create and format a basic spreadsheet containing the data table, formulas, and formatting seen in the image at the top of the page linked to above. The topics included in the tutorial are:
[/PTab][PTab=The Basics]This section covers the basics of using Excel. For details and tutorials, please click the corresponding link:
Basic Formatting in Excel
This two part tutorial on Microsoft Excel formatting covers a variety of formatting options that will help improve the look of your spreadsheets and make them easier to read. If you want to go through it step-by-step, see the Excel Step by Step Format Tutorial.
Tutorial Topics
Basic Formatting in Excel 2003 - Part 1 || This tutorial covers three areas of formatting available in Excel:
Basic Formatting in Excel 2003 - Part 2 || This tutorial covers changing the alignment of text in a cell. It also covers a number of other Excel features, that, while not formatting, they are commonly used features in Excel that will help to complete the spreadsheet:
Conditional Formatting || Excel's conditional formatting features let you control how a cell is formatted (ie, changing colors) depending on the data the cell contains. If the cell data meets the conditions you set, then the formatting is applied to the cell. The tutorials in this list cover a number of options for using conditional formatting in Excel and include step by step examples.
Shortcuts and Toolbars
The instructions for this tutorial make use of the options found in the Format Cells dialog box. Several of the steps in this tutorial, such as bolding data, can also be accomplished using toolbar buttons on the formatting toolbar or through shortcut keys on the keyboard. If you know these other options, feel free to use them.
Managing Columns & Rows
Headers and Footers
This section provides an overview of headers and footers as well as how to view and customize them.
Relative vs. Absolute References
This section covers the difference between relative and absolute cell references and how each can be used:
Printing in Excel
Excel Math
The step-by-step tutorials linked to on the Basic Math: How to Add, Subtract, Divide, and Multiply in Excel page cover basic Excel math operations. If you want to know how to add, subtract, multiply, or divide numbers in Excel, these tutorials will show you how.
[/PTab][PTab=Formulas & Functions]Formulas are one of the most commonly used features of Excel. They can be used to carry out simple addition and subtraction or far more complex mathematical calculations.
All formulas in Excel, no matter how complex, always begin with the same two steps:
Excel formulas are the small business owner's friend as they allow you to perform calculations on data you have entered into the spreadsheet. You can use them for basic number crunching, such as addition or subtraction, as well as more complex calculations such as payroll deductions or a student's average on test results.
Additionally, once you have entered the formula, you can change the data and Excel will automatically recalculate the answer for you. Excel formulas are great for working out What if scenarios that compare calculations based on changing data. Once the formula is entered, you need only change the amounts to be calculated.
You don't have to keep entering plus this or minus that like you do with a regular calculator. So, working out the sales tax you will pay if you spend this amount or that on an item, or calculating what your income will be if you work so many hours a week, is easily done with Excel formulas. Personally, I use the IF, AND, OR and AVERAGE functions quite frequently on my inventory spreadsheets where I'm calculating things like FVFs, shipping, etc.
Using formulas and their related functions is a more complex feature of Excel. However, there are some basic formulas that would be quite useful in your business spreadsheets. As you get the hang of using them, you'll find that the more complex formulas really aren't all that complex. As with any sort of programming, you just have to make sure you have all of the characters where they should be or your formula won't work, but Excel is usually pretty good about pointing out where your errors are.
This first set of links will serve to get you started with formulas and the most commonly used functions. The more involved formulas and functions will be covered afterward:
Overview, Basic Components in Formulas & Tutorials
Specific Functions Used in Formulas
Lookup Functions
There are a variety of ways to search for data within a spreadsheet and have it return a value you can use in a formula. Just to name a few, there's LOOKUP, HLOOKUP, VLOOKUP, MATCH, and CHOOSE. The following links will provide more information as well as tutorials to help teach you how to use these important functions.
[/PTab][PTab=Pies & Charts]More information for the Pies & Charts section will be coming soon. In the meantime, feel free to check out the one link included so far.
[/PTab][PTab=Database and Data Mgmt]This section is still under construction and will include information and tutorials on creating and managing Database and other Data Management Functions.
[/PTab][PTab=General Tips]This section is still under construction, but currently includes links to various quick tips to help you get around and work in Excel more quickly and easily.
Using Shortcut Keys
[/PTab][/PTabbedContent]
While going through the various help pages, you'll see a number of glossary terms that are linked to in blue. If you're unsure of what something is, simply right-click it and opt to open it in a new tab for more information. Just be sure not to get off track and go back to the previous tab to continue with the tutorial.
To get started, you can either go directly to the source link (below) or pace yourself by going through the individual pages organized in the above tabs (the latter is recommended for true beginners):
How to Use Excel - Excel Tutorials for Beginners
[PTabbedContent=Beginners Guide to Excel][PTab=Anatomy of a Spreadsheet]This section identifies the main parts of the Excel spreadsheet work area. There are links from each label to glossary terms describing each part in greater detail:
- Parts of the Excel 2013 Screen
- Parts of the Excel 2010 Screen
- Parts of the Excel 2007 Screen
- Parts of the Excel 2003 Screen
Overview
Excel is an electronic spreadsheet program that is used for storing, organizing and manipulating data.
Data is stored in individual cells that are usually organized in a series of columns and rows in a worksheet. This collection of columns and rows is referred to as a table. Tables use headings in the top row and down the left side of the table to identify the data stored in the table.
Excel can also perform calculations on the data using formulas. And to help make it easier to find and read the information in a worksheet, Excel has a number of formatting features that can be applied to individual cells, rows and columns, or entire tables of data.
Since each worksheet in Excel contains billions of cells per worksheet, each cell has an address known as a cell reference (or cell address) so that it can be referenced in formulas, charts, and other features of the program. For example, the very first cell's reference is A1 for column A, row 1. Keep this in mind for later when Absolute and Relative References are discussed under The Basics tab.
Before you begin with everything that is covered in this reference, I strongly recommend that those who are truly beginners first go through this Excel Step by Step Basic Tutorial. Doing so will better assist you in recognizing and understanding the various aspects, features and functions discussed in this reference.
The 9-step tutorial covers the steps necessary to create and format a basic spreadsheet containing the data table, formulas, and formatting seen in the image at the top of the page linked to above. The topics included in the tutorial are:
- Entering the data into the table
- Widening individual worksheet columns
- Adding the current date and a named range to the worksheet
- Adding the deduction formula
- Adding the net salary formula
- Copying formulas with the Fill Handle
- Adding number formatting to data
- Adding cell formatting
[/PTab][PTab=The Basics]This section covers the basics of using Excel. For details and tutorials, please click the corresponding link:
Basic Formatting in Excel
This two part tutorial on Microsoft Excel formatting covers a variety of formatting options that will help improve the look of your spreadsheets and make them easier to read. If you want to go through it step-by-step, see the Excel Step by Step Format Tutorial.
Tutorial Topics
Basic Formatting in Excel 2003 - Part 1 || This tutorial covers three areas of formatting available in Excel:
- number formatting - using percent, comma, date, and currency formatting
- cell formatting
- text formatting
Basic Formatting in Excel 2003 - Part 2 || This tutorial covers changing the alignment of text in a cell. It also covers a number of other Excel features, that, while not formatting, they are commonly used features in Excel that will help to complete the spreadsheet:
- adding a range name
- adding a formula
- using the NOW function to include the current date on the spreadsheet
Conditional Formatting || Excel's conditional formatting features let you control how a cell is formatted (ie, changing colors) depending on the data the cell contains. If the cell data meets the conditions you set, then the formatting is applied to the cell. The tutorials in this list cover a number of options for using conditional formatting in Excel and include step by step examples.
Shortcuts and Toolbars
The instructions for this tutorial make use of the options found in the Format Cells dialog box. Several of the steps in this tutorial, such as bolding data, can also be accomplished using toolbar buttons on the formatting toolbar or through shortcut keys on the keyboard. If you know these other options, feel free to use them.
Managing Columns & Rows
- Add/Delete Columns and Rows
- Change Column Widths in Excel
- Hide/Unhide Columns and Rows in Excel
- Freeze Panes Step by Step Tutorial || When working with very large spreadsheets, headings located at the top and left side of the worksheet often disappear if you scroll too far down or to the right, making it difficult to keep track of which column or row of data you are looking at. Excel's freeze panes feature solves this problem by freezing (or locking) specific columns or rows so that they remain visible at all times.
Headers and Footers
This section provides an overview of headers and footers as well as how to view and customize them.
Relative vs. Absolute References
This section covers the difference between relative and absolute cell references and how each can be used:
Printing in Excel
- Printing in Excel 2003 || Printing in Excel 2003 is a bit different than printing in some other programs, such as a word processor. One of the main differences is that Excel has five locations in the program that contain print-related options. The first, the Print button, is located on the standard toolbar. The other four of these are listed under the File menu: Print Preview, Page Setup, Print Area, and Print.
- Printing in Excel 2007 & 2010 || Printing in Excel 2007 and 2010 is a bit different than printing in some other programs, such as a word processor, and is even different from printing in Excel 2003. One of the main differences is that Excel has five locations in the program that contain print-related options, whereas Excel 2003 has four locations.
Excel Math
The step-by-step tutorials linked to on the Basic Math: How to Add, Subtract, Divide, and Multiply in Excel page cover basic Excel math operations. If you want to know how to add, subtract, multiply, or divide numbers in Excel, these tutorials will show you how.
- How to Add in Excel
- How to Subtract in Excel
- How to Multiply in Excel
- How to Divide in Excel
- Changing the Order of Operations in Excel Formulas
[/PTab][PTab=Formulas & Functions]Formulas are one of the most commonly used features of Excel. They can be used to carry out simple addition and subtraction or far more complex mathematical calculations.
All formulas in Excel, no matter how complex, always begin with the same two steps:
- Click on the cell where you want the formula's result to be displayed.
- Type an equal sign ( = ) to let Excel know you are creating a formula.
Excel formulas are the small business owner's friend as they allow you to perform calculations on data you have entered into the spreadsheet. You can use them for basic number crunching, such as addition or subtraction, as well as more complex calculations such as payroll deductions or a student's average on test results.
Additionally, once you have entered the formula, you can change the data and Excel will automatically recalculate the answer for you. Excel formulas are great for working out What if scenarios that compare calculations based on changing data. Once the formula is entered, you need only change the amounts to be calculated.
You don't have to keep entering plus this or minus that like you do with a regular calculator. So, working out the sales tax you will pay if you spend this amount or that on an item, or calculating what your income will be if you work so many hours a week, is easily done with Excel formulas. Personally, I use the IF, AND, OR and AVERAGE functions quite frequently on my inventory spreadsheets where I'm calculating things like FVFs, shipping, etc.
Using formulas and their related functions is a more complex feature of Excel. However, there are some basic formulas that would be quite useful in your business spreadsheets. As you get the hang of using them, you'll find that the more complex formulas really aren't all that complex. As with any sort of programming, you just have to make sure you have all of the characters where they should be or your formula won't work, but Excel is usually pretty good about pointing out where your errors are.
This first set of links will serve to get you started with formulas and the most commonly used functions. The more involved formulas and functions will be covered afterward:
Overview, Basic Components in Formulas & Tutorials
- Excel Formulas Overview || Includes several links to tutorials, as well, which are most likely already linked to on this page.
- A Quick Guide to Using IF Functions in Excel ||
- Using Cell References in Excel Formulas || This is where knowing the difference between relative and absolute cell references is important.
- Using Labels in Excel Formulas and Functions || Using labels in formulas can make it easier to include specific sets of data by their labels (ie, the column heading "Expenses").
- Using Functions in Formulas || Lists and discusses several of the most commonly used functions.
- Basic Calculations in Excel Spreadsheets || Provides additional quick links to some of the more helpful pages, including various tutorials and other information.
- Excel 101: An Intro to Formulas || This series of Excel tutorials and articles will teach you the basics of using formulas perform basic calculations in Excel spreadsheets.
- A Beginner's Guide to Excel Formulas || A Step-by-Step Tutorial for Beginners that covers the absolute basics you need to know about using formulas.
- Working with Data, Formulas and Functions Step-by-Step Tutorial || A tutorial specifically for Excel 2010.
Specific Functions Used in Formulas
- Using Nested Functions in Formulas || A nested function in Excel refers to one or more function being placed inside another. This is done to extend the capabilities of a function. These tutorials include step by step examples of using nested functions in Excel.
- Excel's SUM Function Tutorial
- Entering the Date with Excel's DATE Function
- Excel IF Functions
- IF Function Step by Step Tutorial (Excel 2003)
- IF Function Step by Step Tutorial (Excel 2007 & 2010)
- IF Function Step by Step Tutorial (Excel 2007, 2010 & 2013)
Lookup Functions
There are a variety of ways to search for data within a spreadsheet and have it return a value you can use in a formula. Just to name a few, there's LOOKUP, HLOOKUP, VLOOKUP, MATCH, and CHOOSE. The following links will provide more information as well as tutorials to help teach you how to use these important functions.
- Dissecting the Excel HLOOKUP Function
- HLOOKUP Step by Step Tutorial || Excel's HLOOKUP function, short for horizontal lookup, can help you find specific information in large data tables such as an inventory list of parts or a large membership contact list. HLOOKUP works much the same Excel's VLOOKUP function. The only difference being that VLOOKUP searches for data in columns while HLOOKUP searches for data in rows.
- VLOOKUP Step by Step Tutorial || Excel's VLOOKUP function, which stands for vertical lookup, can be used to look up specific information located in a table of data or database. VLOOKUP normally returns a single field of data as its output.
- Excel Lookup Function Tutorials || Including those for LOOKUP, MATCH, CHOOSE, and more.
[/PTab][PTab=Pies & Charts]More information for the Pies & Charts section will be coming soon. In the meantime, feel free to check out the one link included so far.
[/PTab][PTab=Database and Data Mgmt]This section is still under construction and will include information and tutorials on creating and managing Database and other Data Management Functions.
[/PTab][PTab=General Tips]This section is still under construction, but currently includes links to various quick tips to help you get around and work in Excel more quickly and easily.
Using Shortcut Keys
- Save a Worksheet
- ALT-TAB Switching in Windows
- Jump Between Worksheets
- Undo Changes or Mistakes
- Insert an Excel Worksheet
- Summing (Adding Up) Data in a Worksheet
- Select Non-Adjacent Cell Using Shortcut Keys
- Select Non-Adjacent Cells with Keyboard and Mouse
- Using Excel's Go To Command
- Apply Italics Formatting
- Apply Number Formatting
- Apply Currency Formatting
- Apply Percent Formatting
- Add the Date with Just a Keystroke
- Formatting the Date
- Add the Current Time
- Formatting the Current Time
- Select All Cells in a Worksheet
- Select All Cells in a Data Table
- Select An Entire Row of Data
- Quickly Hide Rows in a Worksheet
- Quickly Unhide Rows in a Worksheet
- Quickly Hide Columns in a Worksheet
- Quickly Unhide Columns in a Worksheet
- Using the Fill Down Command
- Wrap Text on Multiple Lines in a Single Cell
- Insert a Hyperlink
- See All Formulas Within the Worksheet
- Add Borders Instantly
- Edit Cells with the F2 Function Key
[/PTab][/PTabbedContent]