Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
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.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Functions and cell references|
|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.|
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.
|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.|
|Vlookup can be used to lookup values with "fuzzy" match - this video shows how and when to use vlookup with fuzzy match.|
|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.|
|Section 2: Charts|
|Learn to create a basic bar chart and pie chart from sales data.|
|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.|
|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.|
|Learn to add chart titles, and add horizontal and vertical axes labels.|
|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.|
|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.|
|Section 3: Managing tables with data|
|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.|
|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.|
View a dataset as a table, and filter it using various criteria. Covers filters for multiple fields, for numerical as well as text fields.
|Learn to filter the data in a table using multiple criteria, and output the results in a separate table|
|Learn to freeze rows and columns; or freeze rows only; or columns only.|
|Learn to do two kinds of sorting: (i) sort by a single column or field, and (ii) sort by two or more columns.|
|Create formulas in a table using structured references to other columns (fields)|
|Learn to sort data by multiple columns, group the sorted data and total by groups and sub-groups.|
|Section 4: Pivot tables|
|Learn to create a basic pivot table with rows, columns, and sums of values|
|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.|
Add a calculated field in your pivot table. A calculated field uses the values of other fields and calculates a new value.
|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.|
|Learn to sort and filter by column and row labels in your pivot table.|
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.
|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.|
|Section 5: What-if analysis|
|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.|
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.
|Learn to create best-case, worst-case, most-likely, and other kinds of scenarios in a nicely formatted table|
|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.|
|Section 6: Logical functions|
|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.|
|The SUMIF function lets you compute sums for specific categories (one column only) of data in a table.|
|The SUMIFS function lets you compute sums for specific categories (two or more columns) of data in a table.|
|The AVERAGEIF function lets you compute averages for specific categories of data in a table.|
|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.|
|Learn to use the nested IF function, where you can embed an IF statement inside another IF statement to create complex logical statements.|
|Learn to embed the AND function within the IF function to combine two conditions in your logical operation.|
|Section 7: Lookup and reference|
|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.|
|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.|
|Section 8: Worksheets|
|Learn to group multiple worksheets, and copy data across worksheets without typing.|
|Format and add numbers across multiple grouped worksheets|
|Learn to create hyperlinks to worksheets in your spreadsheet|
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.