Fundamentals of Excel for Business
4.0 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
26 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Fundamentals of Excel for Business to your Wishlist.

Add to Wishlist

Fundamentals of Excel for Business

A gentle but detailed introduction to the effective use of Microsoft Excel in business
4.0 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
26 students enrolled
Last updated 7/2013
English
Learn Fest Sale
Current price: $10 Original price: $50 Discount: 80% off
22 minutes left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3.5 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Functions, cell references, charts
  • Tables, pivot tables, what-if analysis
  • Logical functions, lookup and reference, grouping worksheets
View Curriculum
Requirements
  • Microsoft Excel 2010 (or Excel 2007 / Excel 2013 will do as well) running on Windows operating system
Description

This course covers fundamental concepts in spreadsheet modeling with Microsoft Excel for business by Dr.Ramesh Sankaranarayanan, Associate Professor of Business at the University of Connecticut (UConn). 

This course is designed to help Business School students (undergraduate and MBA) as well as practicing managers and executives become more effective users of excel spreadsheets. All concepts are explained using business examples demonstrated via short videos. Each video is classroom-tested and explains the main concepts as well as things to watch out for.

Each video lesson is designed to be self-contained: you can view the video and work on the spreadsheet without referring to other videos. Supplementary materials include one “before” and one “after” excel file. For best results, you should download the “before” file, follow along with the video, and compare your completed spreadsheet with the “after” file. 

After completing this course you should have the fundamental concepts necessary to use Excel successfully in your workplace or school. While this course uses Excel 2010 to illustrate the concepts, you can apply these concepts in Excel 2007 or 2013 as well.

Who is the target audience?
  • Business undergraduate and MBA students
  • Masters' students in business related areas (e.g. Masters in Finance)
  • Executive MBA students
  • Practicing managers and executives
  • Secretarial and administrative staff
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 43 Lectures Collapse All 43 Lectures 03:25:16
+
Functions and cell references
5 Lectures 29:40
Provides an overview of basic Excel functions, including SUM, AVERAGE, COUNT, MEDIAN, MIN, MAX, TODAY, YEAR; and covers the different ways in which you can invoke these functions in your spreadsheet.
Preview 08:06

In this lecture you will learn about relative and absolute cell referencing in Excel. This will be useful when you have to apply an excel formula to multiple values. Understanding relative and absolute cell referencing will help you copy formulas correctly, and avoid having to repeatedly type in formulas.

Cell references in Excel
11:09

Learn to use the Vlookup function to lookup values (with exact match) from one table and insert them in another table. This is a valuable time saver and helps avoid typos and mistakes in your spreadsheet.
Preview 04:03

Vlookup can be used to lookup values with "fuzzy" match - this video shows how and when to use vlookup with fuzzy match.
VLookup with fuzzy or approximate match
02:49

Learn (i) how to name a range of cells, for easier reference in formulas, (ii) how to edit or delete these range names, and (iii) print a list of range names and their cell ranges.
Naming cell ranges
03:33
+
Charts
6 Lectures 27:16
Learn to create a basic bar chart and pie chart from sales data.
Preview 03:41

Learn to create a graph with clustered columns for sales in four regions over four years. Learn to (i) select specific columns to include in your graph, (ii) remove unwanted columns from your graph, (iii) switch rows and columns, and (iv) display your data as stacked columns. Next, learn to display trends using spark lines, and highlight high points in your data.
Charts with clustered columns
06:51

Learn to create an informative pie chart: (i) label segments, (ii) choose different chart styles, (iii) change the chart title, and (iv) move chart to a new worksheet in your spreadsheet.
Preview 05:00

Learn to add chart titles, and add horizontal and vertical axes labels.
Adding chart titles and axes titles
03:24

Learn to add and change colors to your charts - for specific columns, for the chart background. Next, see how to add multiple trend lines for your chart.
Charts - colors and trendline
04:46

Learn to add data labels to your chart. Specify locations for your datalabels - e.g. outside end, etc. See how to move data labels around.
Charts - data labels
03:34
+
Managing tables with data
8 Lectures 35:25
Learn to format cell ranges based on specific criteria - e.g. all values greater than some number; all above-average values; and so on. Learn to highlight high, medium and low values in different color schemes. 
Preview 04:12

Learn to create a table in excel: using a stock market dataset on a company stock, you will learn to convert this data into a table, insert "table rows" into the table, use the sort and filter functionality, and change the table format.
Creating a table
04:34

View a dataset as a table, and filter it using various criteria. Covers filters for multiple fields, for numerical as well as text fields.
Preview 03:35

Learn to filter the data in a table using multiple criteria, and output the results in a separate table
Advanced fiiter
05:37

Learn to freeze rows and columns; or freeze rows only; or columns only.
Freezing rows and columns
03:35

Learn to do two kinds of sorting: (i) sort by a single column or field, and (ii) sort by two or more columns.
Sorting a table
04:44

Create formulas in a table using structured references to other columns (fields)
Structured reference table
03:18

Learn to sort data by multiple columns, group the sorted data and total by groups and sub-groups.
Subtotals
05:50
+
Pivot tables
7 Lectures 35:40
Learn to create a basic pivot table with rows, columns, and sums of values
Preview 06:26

In a pivot table, a report filter lets you filter your pivot table by categorical values in a specific field. E.g. you have a pivot table on college enrollment, which you can filter by state. This video shows you how to create a report filter. 
Pivot table - report filter
02:35

Add a calculated field in your pivot table. A calculated field uses the values of other fields and calculates a new value.
Calculated fields
05:16

Display pivot table values as calculated values - e.g. percentage of row totals, or percentage of column totals, etc. Also shows hwo to apply conditional formatting to highlight prominent values.
Calculated values
05:23

Learn to sort and filter by column and row labels in your pivot table.
Label filters
04:00

Learn to add a slicer in your pivot table. A slicer is a nice set of buttons that let you or another user select specific categories from your pivot table.
Slicer
05:54

Learn to create a pivot chart from a data table: learn to select specific data to include in your pivot chart, change the measure from count to sum, modify the format of numbers into currency, change the axes titles, select the chart type and style, tweak the sort order of categories, and edit chart title. 
Pivot chart
06:06
+
What-if analysis
4 Lectures 21:19
Perform what-if analysis on mortgage data by varying one value in multiple formulas, in a table format. Also see how to format headings in the data table using the custom-general formatting option.
One variable data table
07:26

Perform what-if analysis on mortgage by varying two values, one by row and one by column, and observe how a calculated formula varies in a two-variable data table.
Two variable data table
05:20

Learn to create best-case, worst-case, most-likely, and other kinds of scenarios in a nicely formatted table
Create scenarios
05:48

Goal seek is a handy feature that lets you specify a goal (value) for a cell, and excel varies the value in another cell until your goal value is reached. In this example we calculate the loan amount you can borrow to reach a goal amount of monthly payment.
Goal-seek
02:45
+
Logical functions
7 Lectures 31:51
The IF function is a logical function that lets you specify one value if a condition is true, and another value if the condition is false. 
Preview 03:29

The SUMIF function lets you compute sums for specific categories (one column only) of data in a table.
SUMIF function
05:09

The SUMIFS function lets you compute sums for specific categories (two or more columns) of data in a table. 
SUMIFS function
05:51

The AVERAGEIF function lets you compute averages for specific categories of data in a table.
Average if
05:11

Database functions let you select specific records from a table that satisfy your specified criteria, and find averages, min, max, count, etc. for these records.
Database functions
05:24

Learn to use the nested IF function, where you can embed an IF statement inside another IF statement to create complex logical statements.
Nested IF
04:06

Learn to embed the AND function within the IF function to combine two conditions in your logical operation.
IF with AND
02:41
+
Lookup and reference
2 Lectures 11:42
The MATCH function helps you find the table row number of a cell containing a particular value. There are three kinds of matches that are explored in this video: (i) exact match, (ii) a match of the largest number less than the given number, and (iii) a match of the smallest number larger than the given number.
Matching in a table
06:36

The INDEX function can take the index value returned by the MATCH function, and retrieve other fields in the row referenced by the index value.
Matching with index
05:06
+
Worksheets
4 Lectures 12:23
Learn to group multiple worksheets, and copy data across worksheets without typing.
Group worksheets
04:24

Format and add numbers across multiple grouped worksheets
Data across worksheets
04:57

Learn to create hyperlinks to worksheets in your spreadsheet
Worksheet hyperlinks
02:38

Conclusion video
Conclusion
00:24
About the Instructor
Ramesh Sankaranarayanan
4.0 Average rating
3 Reviews
26 Students
1 Course
Associate Professor of Management Information Systems, UConn

Dr.Ramesh Sankaranarayanan is Associate Professor of Management Information Systems, School of Business, University of Connecticut at Storrs. He has over a decade of experience as an instructor teaching Microsoft Excel, Access, web programming, database design, SQL, e-commerce, online marketing, and business case analysis to undergraduate Business students and masters students, and teaches in the Executive MBA program at UConn, Hartford. He has a Ph.D. from New York University's Stern School of Business, in addition to a B.Tech. from IIT Madras, and MBA from IIM Ahmedabad, India. He has earned certificates in Business Case Discussion Leadership and Negotiation Leadership from Harvard Business School. He has consulted/worked for leading multinational corporations, including GE, Morgan Stanley, PricewaterhouseCoopers, Wipro and ICICI.