Excel Essentials

Dependent dropdowns or lists, return values for a range using VLOOKUP, automated Gantt charts

Course Summary

In 15 minutes, you will learn how to addresses three key challenges that we face in the three phases of data flow:

  1. Data Input: You work in a collaborative environment, and your colleagues add data in shared Excel sheets basis their preference, leading to huge data inconsistency. Example: First name, Initials, First & Last Name, etc. This leads you to spend more time on data clean-up and reduces the time that you can spend on analysis
  2. Data Processing: You need to evaluate multiple conditions and end up writing long and confusing nested IF formulas. It becomes an even bigger challenge if you are required to update those formulas
  3. Data Presentation: You create project plans in Excel and spend significant time coloring cells to indicate the duration of each activity. And you have to repeat this activity each time the project timelines are revised

How will this course benefit you?

In this course, you will learn tricks for each of these problems. These will help you save considerable time each time you work on these areas.

Data Input:
One of the most robust ways to improve data consistency is by using dropdowns or lists for data input.

Creating a dropdown in Excel is very simple. However, creating a dependent drop-down list, where the content of the dependent list is based on the value of the parent list can be confusing.

You will learn a simple method on how to create a dependent dropdown list and reduce your data consistency woes.

Data Processing:
VLOOKUP formula is used extensively for finding data from large tables based on a known value.

One of the most under-utilized uses of VLOOKUP is to return values for data ranges. Example: Offer 5% discount if the sale is between 10 to 50 units, or 7% discount if the sale is between 51 to 100 units, etc.

You will learn how to return values based on such data ranges. This not only makes it easy to write formulas where you need to evaluate multiple conditions but also very easy to modify them if required.

Data Presentation:
Creating detailed project plans can be very time-consuming. And updating them at the end of every review or meeting can be even more frustrating.

You will learn a simple technique to create project plans or Gantt charts using conditional formatting. It will update the cell colours indicating the timeline information automatically as you update the start and end date for an activity.

And all of this, in about 15 minutes!

What all does the course include?

Short lessons for a quick understanding

Personalized feedback on submission of assignment

Course Certificate on approval of assignment

Earn a Skill Badge on approval of assignment

Link to add certificate to your LinkedIn Profile

Invitations to upcoming webinars

Discussion forum to clarify doubts from our experts

Downloadable worksheets to practice along

Course Curriculum

Reviews

5

Top Rated
  • 5 100%
  • 4
  • 3
  • 2
  • 1
Md Junaid Ansari

It's really knowledgeable for every finance Role

That's amazing

3 weeks ago

Try the course on our complements

  • Free

  • Free

    Enroll Now
  • Get immediate access
  • No credit card required
  • Assignment included
  • Certificate & Badge
  •  

Music Credit: bensound.com