Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Data Modelling in Excel Masterclass: Excel Formulas
Rating: 4.4 out of 5(115 ratings)
5,039 students

Data Modelling in Excel Masterclass: Excel Formulas

Art & Science of data modelling in Excel. Master Excel formulas.
Created byExcel Yourself
Last updated 1/2018
English

What you'll learn

  • Plan, design & maintain superior Excel models
  • Develop creative solutions to routine business problems in the business, finance, and accounting world
  • Process "big data"in Excel
  • Source and clean up source data for reporting

Course content

6 sections • 71 lectures • 8h 53m total length

  • Why Art Science with Excel4:02

    Explore the art science of excel modeling through a nine step framework, challenging the idea that art and science don’t overlap and highlighting curiosity in building models, dashboards, and visualizations.

  • Historical Perspectives Art Science2:13

    Explore intersections of art and science through history, revealing how creativity can transform Excel models and formulas. Let curiosity carry you from Leonardo da Vinci to Steve Jobs, fueling innovation.

  • Art and Science of Excel Modelling in 9 Steps1:55

    Apply the nine-step framework for Excel model development by balancing the people, process, and technology trifecta to deliver business value, embrace new technologies, and address the human element.

  • Step 1 Defining the Problem5:14

    Define the problem to guide Excel model development, balancing requirements, design, coding, testing, and deployment while asking targeted questions and rephrasing goals.

  • Step 2 Analyzing the Problem11:28

    Break down a problem into smaller parts, visualize processes with flowcharts, and leverage Excel tools like formula auditing and worksheet relationship diagrams to improve large models.

  • Step 3 Knowing Your Stakeholder1:25

    Identify and classify stakeholders, from sponsors and data providers to end users and super users, to tailor testing and system design.

  • Step 4 Understanding Your Role2:08

    As the leader of this project, you wear many hats—from data wrangler and policy enforcer to power analyst—harmonizing raw data into robust Excel models and dashboards.

  • Step 5 Planning Your Model5:20

    Plan your spreadsheet model by selecting the right type—robust single-user, utility macros, add-ins, or data storage spreadsheets—and weigh simplicity against complexity for stakeholders with a database front end where possible.

  • Step 6 Building Your Model7:51

    Build a modular Excel model by assembling a raw data layer from multiple sources, adding assumptions and scenarios, and separating calculations from outputs into dashboards for clear, shareable results.

  • Step 7 Testing Your Model4:25

    Test early to catch bugs and avoid costly late changes; recruit end users, use standardized feedback, test in realistic environments with consistent hardware and software, and retest after fixes.

  • Step 8 Delivering Your Model5:31

    Deliver your excel model by choosing read-only files, xl templates, add-ins, or excel-based reports, with real-time instructions and Visual Basic training for sponsors, data providers, end users, and super users.

  • Step 9 Maintaining Your Model2:11

    Maintain your Excel model through ongoing upkeep, delete outdated data, and use ex-s formatting with Inquirer to shrink file sizes and improve performance.

  • The 9 Steps Recap2:37

    Define the problem, analyze questions and stakeholders, plan an Excel model in layers—assumptions, inputs, calculations, and outputs—start with small wins, then document, test, deliver, and maintain.

  • Module 1 Quiz

Requirements

  • To keep up during this course, we recommend you have a very solid understanding of the basics of the spreadsheet environment. Formula construction and concepts, in particular structured referencing (proper use of the ‘$’ signs) is also extremely helpful.

Description

The Art Science of Excel Modeling is an in-depth exploration of how to plan, design, and maintain superior Excel models. Ideal for consultants, financial analysts, auditors, accountants, managers, and technical professionals seeking to gain a deeper understanding of the “art-science” of data modeling and how it can lead to improved business decisions.

Kicking off with a bigger-picture, more philosophical view of spreadsheet design principles, our aim is to inspire critical thinking skills and confidence in an increasingly competitive job market. In our “real world based” case studies, we will explore creative solutions to routine business problems in the business, finance, and accounting world. We will also offer practical guidance for newer challenges arising from the ever-increasing volumes of “big” data that seem to be “pushing the boundaries” that Excel is naturally equipped to handle.

Designed with the intermediate to advanced level Excel user in mind, as well as technical professionals seeking to build better alliances with “the Excel people.” To keep up during this course, we recommend you have a very solid understanding of the basics of the spreadsheet environment. Formula construction and concepts, in particular structured referencing (proper use of the ‘$’ signs) is also extremely helpful.

The instructor, Szilvia Juhasz, is an Excel book author and a frequent co-host at Excel TV.

Who this course is for:

  • Designed with the intermediate to advanced level Excel user in mind, as well as technical professionals seeking to build better alliances with “the Excel people.”

Report abuse