Home > Excel Class Guide > Intermediate Excel

Intermediate Excel Class for Business and Industry

Intermediate Microsoft Excel Training

Next Class: 9/06/2022

More Dates >

Learn to use Excel's formulas, functions, pivot tables, and data tools to quickly analyze, calculate, and report business and industry data.

See What You Master
Close
Harness your data's power.

How our class can help you.

Our 1-day class shows you how to start harnessing Excel’s powerful calculation, data processing, and reporting abilities.

It covers must know Excel skills like how to build worksheet tables, filter data for reports, control what you type in a cell, summarize data with PivotTables, and make professionally formatted charts.

Our class also covers important formula topics like how to summarize and count data, perform table lookups, use logic to make data decisions, and parse dates / text. We'll also demonstrate how to layout your problems on the worksheet.

Join us and our class will show you how to use Excel in new ways that will allow you to complete your tasks rapidly.

Free Repeats
Repeat your Excel class with us for an entire year for free.
Free Repeats
NASA Project
See how we helped NASA with the Mars Rover using Excel.
NASA
Discounts
We provide discounts to active / retired US Military and DOD.
Discounts
Customers
View our customers like Apple, Boeing, and the US Army.
Customers

Key intermediate Excel topics covered in class.

Intermediate Excel Preview

Click to enlarge, scroll < > if hidden.

Detailed class syllabus.

Class Syllabus Dates / Signup

Available: Public >, Virtual >, Onsite >

How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Laying Out Problems, Entering Data, and Building Formulas on the Worksheet

Building Worksheet and Excel Tables to Store Data

Using Data Validation and Conditional Formatting to Control and Flag Data

Filtering, Sorting, and Grouping Worksheet Data to Make Reports

Performing Table Lookups and Merging Worksheet Data

Using Logic to Make Decisions in Your Formulas

Using Logic to Summarize and Count Worksheet Data

Analyzing Dates and Times in Your Worksheet Data

Importing, Parsing, and Cleaning Text Data on the Worksheet

Creating and Formatting Industry Grade Charts

Creating Basic PivotTables / PivotCharts to Rapidly Summarize Data

Additional Classes:
Class Syllabus Dates / Signup

Excel skills needed for our class.

Select this training if you or your group have:

  • Opened and saved a workbook file
  • Copied and pasted worksheet data
  • Performed basic cell formatting tasks like coloring a cell, bolding, aligning...
  • Typed data in worksheet cells
  • Built basic formulas like =A1+A2 and know how to use cell references
  • Used a worksheet function like =Sum(A1:A10) in a formula or have seen it before

The class details.

If you need to contact us about our class.

Phone Number: 1.805.498.7162

Business Hours: 8:30 - 5:00 PM PT

You can email us at info@emagenit.com >

Contact us by form.

Click to View Form

FAQ for Intermediate Excel Class

How to Turn Off and On the Calculation Mode of Excel

Back to FAQ for Intermediate Excel >

Microsoft Excel has the capability for a user to control its workbook calculation mode. A user will set this mode to manual disabling all calculations when a workbook takes a long time to calculate when new values are entered. Generally this time lag occurs when you have thousands of complex formulas in a workbook or if it has large array formulas.

To set the calculation mode to manual, proceed to the Ribbon, select the Formulas tab and then find the Calculation grouping on the tab. Click on the Calculation Options button and select you guessed it Manual. To turn it back on, select Automatic. Please note that this feature effects all open workbooks.

Turn Off/On Microsoft Excel's Calculation Mode

Click to enlarge, scroll < > if hidden.

The example above demonstrates how to turn off Excel's calculation mode. By setting this mode to manual, it allows faster user entry in workbooks that take a long time to calculate.

How to Calculate a Workbook / Worksheet When the Calculation Mode Is Set to Manual

Back to FAQ for Intermediate Excel >

Once manual calculation mode is engaged, Excel will only recalculate its workbooks when you tell it to. You can recalculate all workbooks by pressing the F9 function key or you can click the Calculate button in the status bar at the bottom left-hand corner of the Excel screen. Note that Shift+F9 can be pressed to only calculate the worksheet that you are on.

You can also press the Calculate Now and Calculate Sheet buttons in Ribbon pictured above to perform the same calculations. By the way, looking at this button when a workbook first opens or is being used is a great way to know instantly if the calculation mode has been set to manual. The Calculate button in the Status bar only appears when the workbook needs to be calculated.

Also if you set the calculation mode to manual and save your workbook, that setting will be stored with the workbook so when the file is reopened, Excel reengages manual calculation mode if it is opened first before any other workbooks. This feature can vary depending upon what is open so always check it.

Be careful saving workbooks in this mode because many Excel users are not used to seeing the calculation mode disabled and become confused when first encountering it. Also look on the Excel Options dialog box (File / Options) on the Formulas tab for the settings just discussed. There is also a check box on that tab that controls recalculating the workbook before saving if it is in manual calculation mode.

What Is an Excel Worksheet Function?

Back to FAQ for Intermediate Excel >

Microsoft Excel worksheet functions are basically small computer programs you run from a worksheet formula >and look like =Max(A1:A10) when typed in a formula. Once entered, they run and return a result value through their syntax. So in your mind, when you see Max(A1:A10) visualize a value in its place in the formula and you have the concept.

One key concept to realize is that worksheet functions obey their own set of calculation rules and do not have to follow the rules a standard worksheet formula (i.e. = A1 + A2) does. So be sure to read about a function's capabilities in Excel Online-Help before using it. Some common worksheet tasks that functions allow you to do include:

the list is quite extensive.

Variety of Excel Worksheet Functions

Click to enlarge, scroll < > if hidden.

The sample worksheet above demonstrates the wide variety of worksheet functions available in Excel. Worksheet functions perform tasks that normal formulas in Excel cannot.

How to Run a Worksheet Function from a Formula

Back to FAQ for Intermediate Excel >

To run a function from a worksheet formula type an equal sign, the function name, a beginning (,  its argument list, and an ending ) and press Enter. For example =SUM(A1:A10). This typed syntax (i.e. grammar) is referred to as a Call statement in programming and that is actually what you are doing here, calling and running computer code.

The key to making any function run is its argument list. An argument list is the information the function needs in order to execute and return a value, they are its inputs. Bad inputs = bad result. An argument list is contained between the two ( )'s as just stated and the arguments are separated by commas. If you see 2 commas, you have 3 arguments. The argument list for a function can be viewed in its Excel On-Line Help.

When typing a function call in a worksheet formula, function arguments come in many flavors but the basic ones are these:

Worksheet Functions Arguments

Click to enlarge, scroll < > if hidden.

The sample worksheet above demonstrates the types of basic arguments that can be used in formulas running worksheet functions.

A Quick Way to Run a Worksheet Function's Online Help

Back to FAQ for Intermediate Excel >

The complete list of Microsoft Excel functions and their help on how to run them can be quickly found by clicking the fx button at the top of the worksheet headers next to the formula bar (white box). This is the Insert Function tool discussed above. Before clicking this button, make sure you are clicked on an empty cell so you do not overwrite anything when this screen is activated.

When the Insert Function screen appears look through the drop downs, select a function name then click the Help on this Function blue hyperlink in the bottom left hand corner of the screen. This is a very quick way to access a function's help screen. The help screen will describe how to type the function in a formula, called its syntax, and will also describe the uses of the function.

A picture of these screens is illustrated below. Once the help screen is displayed, you can click the Cancel button on the Insert Function screen to cancel the operation but the help screen will remain. Note that you can also get a list of functions by clicking on the Formulas tab in the Ribbon and looking in the Function Library section. If a function name is clicked on in this section, that to will activate the Insert Function tool.

How to Run Help for a Worksheet Functions
The example above demonstrates the various ways to activate Excel worksheet function help. Note that you can also press the function key F1 and type the name of the function to lookup in help.

How to Nest Excel Worksheet Functions in a Formula

Back to FAQ for Intermediate Excel >

When calculations involving multiple operations have to be performed in a formula, the need will arise to nest Excel functions. This saves worksheet space as only one cell is used to do multiple operations.

The simple concept behind nesting functions is to place the function syntax that runs the function in the argument list of another function in the same formula. This allows the inside function to calculate first then feed its result value to the function whose argument list it was placed in.

To nest a function within another function in a formula:

  1. First build the syntax that runs the functions to be used in separate formulas. Place static values in place of where the nesting will be used. This allows each function formula to be tested without the hassle of a nested structure.
  2. In the formula where the nesting will take place, put it into edit mode (F2 or double click on cell) and place an apostrophe in front of the equal sign. Press Enter. The apostrophe turns the formula into a piece of text so it keeps it from calculating. This way if you have multiple nestings, you can do it piece meal and not have the formula error out during construction.
  3. Double click on the function formula to be nested. Highlight the syntax for the function call only in the formula and press Ctrl+C. This copies the syntax as text. Do not include anything around it. Press the Esc key to escape the formula without damaging it.
  4. Double click on the formula to nest in. Highlight the static value in the formula that is the place holder for where you want to nest and paste the syntax over it. You can put spaces around the value before pasting as this will not hurt the formula and allows a better visual ID of the place holder to be pasted over.
  5. After pasting, remove the apostrophe from the front of the formula and press Enter to calculate. Makes sure the formula is calculating correctly.
Nest Excel Worksheet Functions

Click to enlarge, scroll < > if hidden.

The sample worksheet above demonstrates a simple method to build and test nested functions within an Excel formula.
Need Help? Please call us at 1.805.498.7162

Copyright © 2002-2022

EMAGENIT All Rights Reserved