با مشاهده این دوره ی آموزشی یاد می گیرید که چطور در اکسل نمودارهایی زیبا و کاربر پسند طراحی کنید که داده ها را به شکلی بسیار اثربخش و مفید در قالب گزارش ها نمایش دهند.

این دوره آموزشی محصول موسسه Udemy است.

سرفصل های این دوره:

  • برقراری ارتباط موثر و ارائه اطلاعات
  • یک مرور کلی بر روی چگونگی تاثیر ارائه داده های کمی
  • گزارش برای اکسل
  • طراحی جدول
  • طراحی نمودار
  • درک محیط ابزار نمودار
  • میانبرهای ساده برای صرفه جویی در وقت
  • بهترین تمرین و نمودار گزینه
  • پشت صحنه اسرار نمودار های پویا
  • توابع اکسل
  • نمودار های پویا
  • تابع برای نمودارهای تعاملی
  • مرتب سازی خودکار – تابع RANK
  • مرتب سازی خودکار – رتبه های منحصر به فرد
  • مرتب سازی خودکار میله
  • ویژگی های قالب بندی شرطی اکسل
  • ترکیب نمودار ستونی
  • استفاده از نمادها در نمودارها
  • و ….

عنوان دوره: Udemy Excel Charts: Visualization Secrets for Impressive Charts

مدت دوره: 7 ساعت و 30 دقیقه

نویسنده: Leila Gharani

 توضیحات:

Udemy Excel Charts: Visualization Secrets for Impressive Charts

Leila Gharani
7.5 hours
Intermediate Level


If you use Excel to generate reports and graphs, my hands-on Excel training provides you with an extremely advanced toolkit worth of knowledge that will take the design of your Excel charts, tables and reports to the next level. It will provide you with the best tricks to create dynamic charts. It will save you tons of time of manually updating your Excel graphs on a monthly basis. The visualization techniques introduce you to some unusual methods to handle and create charts which will enhance readability of your reports as well as impress your readers.
550+ satisfied students!
Lauren says: "I wish she had more courses on advanced excel and access that are formatted like this one. She frames the content in ways that are applicable to my current job. I will be able to improve my current processes and project turnaround time as well as the look/feel of my tables and charts."
John says: "Leila does a fantastic job of walking you thru the process of creating various charts from scratch. From which formula to use to step by step chart creation. As a bonus you have actual exercises to practice your new skills, Absolutely one of the best instructional courses I have taken"
Paul says: "Course is clear, to the point and very dynamic. Excellent!"
Types of Excel Charts & Excel Graphs are presented:
The content and type of Excel charts presented are those that are typical to corporate reporting. Given my background in controlling, finance and project management, I designed the training with this audience in mind and the typical methods used to report, communicate, analyze, check and plan quantitative information.
If you are a student taking this course, rest assured that you are well equipped with advanced Excel visualization & chart design techniques to impress any employer who requires you to create graphs & reports in Excel.
Demonstration will be done using Excel 2010, but all methods will be compatible with older as well as future versions of Excel unless otherwise stated in the video. My main focus is to introduce you to new �methods� of doing things which you can do no matter which Excel version you have.
Why this course is different to other Excel courses:
I will not only demonstrate to you, but you will be able to follow each demonstration in your own Excel workbook. To make sure you have understood the techniques, you will complete an exercise at the end of each section (answers are included but you have to try on your own first).
I place great value on keeping the training not only informative but also interesting. I know technical courses can be boring, especially when taken online. For this reason, I have mixed screen-casts together with talking head and flip chart type of demonstrations to get it as close to a classroom training style as possible.
I have also been told by many students that I have the ability to explain complex topics in an easy to understand manner. I think you will benefit from that.
This course is split to 5 major parts:
Methods to effectively communicate and present data trends � In this section I will also provide you with a crash course in best practices for table and graph design.
Behind the scenes secrets of dynamic charts in Excel, where I introduce you to key functions you need to use to never manually update your Excel charts again �The functions you learn here go beyond graphs and charts. They enable you to do complex look ups in large data tables.
Techniques that highlight Excel chart and table elements to direct the reader attention where it is needed most.
New ways to compare performance: as in Actual data versus Budget, forecasts and previous year
Effective Chart Combinations that are pivotal to management reports
This is an Excel Advanced Chart Course BUT you will be surprised how simple the techniques are once you know them!
What are the requirements?
Existing experience and knowledge of Excel's basic charting options and formulas
This course is aimed for current intermediate to advanced Excel users and will significantly build on existing knowledge
Demonstration is done using Excel 2010. However, the focus of this training is to teach you new methods of doing things which you can do regardless of the Excel version you have
What am I going to get from this course?
Over 75 lectures and 7.5 hours of content!
Significantly improve your Excel reports to create more powerful graphs that communicate your information in the best manner
Learn creative & simple techniques that allow you to create your own Excel charts from scratch
Create dynamic Excel charts. Why? To save time! I have seen many cases where people are unnecessarily manually updating graphs. This costs considerable time and nerves.
Impress your management by including new Excel graphs in your reports (such as my Pin chart for variances)
Apply Best Practice methods to considerably improve the design of your Excel charts and tables
Apply techniques that highlight chart and table elements to direct the reader attention where it is needed most
Use effective Chart Combinations that are pivotal to management reports
Apply best methods to compare performance in your Excel graphs: as in Actual data versus Budget, forecasts and previous year
Learn by doing. Download the Demo Excel Workbook and follow each section with me. Once you complete a section, you will do an exercise to test your understanding. Learning by doing works best!
Learn advanced Excel lookup methods (such as matrix lookups) which you can use in your larger data files. The methods learnt in Behind the scenes secrets of dynamic charts can be applied to many other areas.
Become the Excel data Visualization star in your department by creating impressive Excel charts and graphs in your reports
What is the target audience?
Controller or financial analyst responsible for creating monthly, quarterly or yearly reports
Finance or department manager looking for new effective visualization methods
A student planning to work as a business or financial analyst or in any function that will require data visualization
All in all, if you�re responsible for presenting your data graphically and you need to do this with Excel, you will benefit from the methods I teach in this course


Section 1: Introduction & Scope
Lecture 1
Course Introduction & Scope
07:10
Watch this free preview to obtain a good understanding about the content and structure of the course.
Lecture 2
Resources to Download
01:14
Please download the two Excel Workbooks you see in the Resources tab. One is the Demo Workbook which I use for demonstration and the other is the Exercise book. The solution to the exercises is also provided in the Exercise book in separate tabs.
Section 2: Effectively Communicate & Present Data Trends
Lecture 3
Effective Presentation of Quantitative Data - Overview
01:51
An overview on how to effectively present quantitative data for Excel reports.
Lecture 4
Table Design - Best Practices
02:57
Learn best practices on how to present data tables in Excel. This lecture provides you a with good overview on the methods you can use to bring attention and focus to specific sections of your data tables and what you should avoid.
Lecture 5
Chart Design - Best Practices
05:00
Learn best practices on how to create Excel charts with an impact. It takes you through the methods that will help your readers to understand the message of your charts faster and not waste time trying to figure it out. It also shows you what you should avoid when designing charts.
Lecture 6
Understanding the Chart Tools Environment
18:13
This demo provides a comprehensive overview of the chart options and features available in Excel.
Lecture 7
Simple Shortcuts to Save Time
08:30
In this lecture you'll learn some tips and tricks on how to become faster when creating & designing charts. You'll also learn the important shortcut keys to properly position your Excel charts in your reports.
Quiz 1
Best Practice & Chart Options
5 questions
Section 3: Behind-the-Scenes Secrets of Dynamic Charts
Lecture 8
Never Manually update your Charts again!
03:56
Many people update their charts manually, never knowing that Excel can actually do the work for them! This section provides you with a good overview on what Excel is actually capable of when it comes to dynamic charts. Each of the topics mentioned here will be discussed in the next 4 sections.
Lecture 9
Top Excel Functions that will save you time
01:28
This lecture introduces you to the main advanced Excel functions that will provide you with dynamic and interactive charts. These functions also go beyond charts. They can be used to create interactive dashboards, dynamic reports and advanced lookups. The next 4 sections take you through each function in more detail.
Section 4: Behind-the-Scenes Secrets - Chart Updates When New Data is Added
Lecture 10
Tables are a Time Saver
08:27
Excel Tables are definitely a must as they provide a simple way to create dynamic charts and formulas.
Lecture 11
When Tables fail Use this Function
10:58
In some cases, you might not want or be able to turn your data into an official Excel Table. In this case there is a great formula that can come to your rescue. Find out which one, in this lecture.
Lecture 12
Name Manager & OFFSET as a Great Team
09:15
Many Excel users are generally not sure how to properly use the Name Manager feature. This lecture shows you one practical use for it.
Lecture 13
OFFSET without Name Manager to Update your Chart
09:11
The OFFSET function is a very powerful function. A must-know for anyone looking to gain advanced Excel skills and learn advanced formulas.
Lecture 14
Exercise - Dynamic Charts
Article
Section 5: Behind-the-Scenes Secrets - Getting the Interactive Dashboard Effect
Lecture 15
Index and Match for an Interactive Chart that shows Different Views
02:19
INDEX & MATCH is one of my favorite functions. It's actually two functions used as one. It can do complex lookups. It can help you create dynamic dashboards and dynamic charts. I have dedicated a whole section to this to make sure I do it justice.
Lecture 16
Index & Match - The Index Part
04:36
Lecture 17
Index & Match - The Match Part
02:55
Lecture 18
The Dream Team - INDEX & MATCH
07:00
Lecture 19
Mix it all for Major Time Saving - Yes it becomes a Dashboard!
12:53
Lecture 20
Exercise - Function for Interactive Charts
Article
Section 6: Behind-the-Scenes Secrets - Automatic Sorting of Bars
Lecture 21
Automatic Sorting of the Bars in a Bar Chart - Overview
01:18
Lecture 22
Automatic Sorting - The RANK Function
06:30
Lecture 23
Automatic Sorting - Unique RANK
04:38
Lecture 24
Exercise - Automatic Sorting of Bars
Article
Section 7: Behind-the-Scenes Secrets - Automatic Legend Positioning
Lecture 25
Perfect Positioning of Series Labels
02:03
Avoid Excel's default legends whenever possible. For example, if you have two line series, position the series labels, right by the series and remove the default legend. This creates a leaner, more elegant and easy to interpret chart. You can also do this if you have stacked column charts. The trick is not to do this manually - which most people do by using text boxes - but instead to use some smart formulas and methods that lets Excel do the work for you.
Lecture 26
Chart Series Label Positioning - Simple Line Series
07:39
In this lecture you will learn how to dynamically position the line series label right beside the line series. It also teaches you how you can add a correction factor to it to fully control its positioning in your Excel Chart.
Lecture 27
Chart Series Label Positioning - Changing Line Series
06:20
When the length of your time series is changing - for example as you add data for the next months -, you need your series label to move with your line. To avoid any manual work, follow the tips and tricks shown in this section. This is the only time I like errors in my formulas!
Lecture 28
Chart Series Label Positioning - Stacked Column Chart
08:46
In this lecture, you will learn how to position the legend (stacked series labels) dynamically for a stacked column chart. We will do this using formulas and smart techniques. Your readers will benefit from having the label of the stack right beside it and no longer need to look at the legend and match the color with the color of the stacks. Time-saving for everyone!
Lecture 29
Exercise - Automatic Legend Positioning
Article
Section 8: Secret Techniques that Bring Attention to Key Chart Elements
Lecture 30
Highlight key Areas for Faster & Easier Readability
03:06
In this section I am going to teach you the main Excel tricks you need to know to highlight key chart elements for improved and faster readability
Lecture 31
Conditional Format Column Colors for Best Impact
09:22
Learn how to dynamically change the color of specific bars in your column charts. Don't manually do this by clicking on a specific column and changing its color. Use this method instead!
Lecture 32
Conditional Format Bar Colors
03:06
Learn how to dynamically change the color of specific bars in your bar charts. Don't manually do this by clicking on a specific bar and changing its color. Use this method instead!
Lecture 33
Vertical Lines to Create Dividers
09:05
Whenever there is some sense of grouping of the categories in your chart, you might want to add vertical dividers to make it easier for your readers to compare grouped categories together.
Lecture 34
Error bars as Dividers
07:53
First introduction to my favourite technique of using error bars in Excel charts to get the impact you need. In later lectures you will be using this method in different ways. This knowledge is definitely a must for anyone wanting to become an expert in Excel Chart design.
Lecture 35
Use Dynamic Annotations to Direct Reader Attention
19:33
Sometimes it makes sense to add a comment or annotation inside your Excel chart to highlight a specific event. For example you started a marketing campaign or introduced a new product and would like to direct the reader attention to this section or use it as an explanation for your data point. This lecture not only shows you how to add annotations to your chart but also how to make these dynamic so that once your data point shifts, the annotation moves with it.
It also uses the functions we learnt in Secrets to Dynamic Charts to create a mega dashboard effect!
Lecture 36
Exercise - Bring Attention to Key Chart Elements
Article
Section 9: Design Tables that Highlight & Impress
Lecture 37
Conditional Formatting for Easy-to-Read Tables - Overview
02:30
Excel tables are used when you'd like to show exact values and compare specific values with one another. In this section you will learn how you can get the most out of your Excel data tables and direct reader attention where it's needed most. Two techniques are shown in this section
Use symbols in tables
Use Excel's Conditional Formatting feature
Lecture 38
The Conditional-Formatting-Free method - Use Symbols
08:12
In case you don't want to use the Conditional Formatting feature in your Excel tables, you can use a very simple technique to get a similar effect. Watch this lecture to see how you can use symbols in tables to get a similar effect.
Lecture 39
Highlight Table Rows Based on Values
06:59
This lecture shows you how to use Excel's Conditional Formatting feature to format table rows differently depending on a certain criteria. Conditional Formatting can be tricky sometimes but all you need to do is to watch out for one key setting, which is explained in this lecture.
Lecture 40
Using Smart Color Coding in Tables
04:22
This lecture shows you how to use Conditional Formatting to make certain parts of your table stand out so that it is easier to read and digest. The Smart formatting techniques help emphasize the sections of your report that need attention most. To ensure you do not have too much color everywhere, you can use a threshold.
Lecture 41
Use Symbols or Bars for Easier Readability
04:55
This lecture shows you how to use data bars and symbols in conditional formatting to create impressive tables.
Lecture 42
Exercise - Design Tables with Focus
Article
Section 10: Measuring Performance - Actual Versus Budget/Target
Lecture 43
Measuring Performance, doing Comparisons & predicting the Future
02:35
This lecture provides you with an overview of different methods used for visualizing performance comparisons, deviations, as well as future estimated performance. It introduces you to the topics and charts that you will learn in this section as well as the next two sections.
Lecture 44
Column Chart Combinations
07:39
When comparing Actual to Budget, many reports show this comparison as columns. Although this is a good method, watch this lecture for two other ways to compare variables with one another that are also highly effective and take less space.
Lecture 45
Using Line Series - Make Sure you do this to Avoid Crashing Lines
08:01
This is specially required if the range for your line series chart is based on an Excel data table that has formulas and you would like to cut the line series in the middle. Using the common "" in your formulas does not work as the line series will crash all the way down to the X-axis. You will have to use a special trick to get to "break" your line series. Watch this lecture to see how.
Lecture 46
Variances With a Bar Chart - The Professional Method #1
18:13
One really effective and neat way of showing Actual to Budget variances for different categories such as products, departments or companies is to present it as a bar chart and show the variance to budget using thinner bars. When Actual is above Budget, the difference is shown as green and when Actual is below Budget, it is shown in red. The variance amount is also shown as a percentage. Watch this lecture to see what this "professional" method is all about.
note: it does involve my favorite trick!
Lecture 47
Variances With Two Bar Charts - Professional Method #2
21:53
In this lecture you will learn another effective method to show Actual data and the Variance to Budget. The method includes two bar charts that are positioned beside each other and the bars are "connected" using subtle lines that connect the bars from the chart representing Actual data to the Variance chart. It uses three different techniques covered in this course and brings them all together creatively to get a great impact.
Lecture 48
Measuring Performance - Bullet Charts Overview
01:48
Bullet charts were developed by Stephen Few. They are a variation of the bar chart and allow you to compare the development of your values not just to a target but also to acceptable ranges. They are the more effective version of speed meters and thermometers used in dashboards. The next 4 lectures take you through how to design vertical and horizontal Bullet charts in Excel.
Lecture 49
Vertical Bullet Chart with Percentages
09:53
Lecture 50
Vertical Bullet Chart with Absolute Values
06:31
Lecture 51
Horizontal Bullet Chart
11:22
Lecture 52
Horizontal Bullet Chart - The Easy Method
03:40
Lecture 53
Exercise - Effective Charts to Measure Actual to Budget Performance
Article
Section 11: Effective Comparisons - Actual Versus Previous Year
Lecture 54
Use Symbols in Charts - The Simple Method
08:10
How do you bring symbols (such as up/down arrows) in your horizontal axis in your Excel charts to reflect deviations? Watch this lecture and find out!
Lecture 55
Use symbols in Charts - The Impressive Method
06:56
In the previous lecture we learnt how to get symbols in the horizontal axis of your charts to reflect deviations. The one disadvantage of the method shown was that you can't control the color of positive and negative deviations. My preferred method is the one I show you in this lecture where the symbols can be color coded separately to show red for negative and green for positive deviations. I think it brings the message across better. Also to avoid having arrows everywhere, we can include a threshold. We leave the sections that fall within the threshold empty and only bring attention the bigger deviations. Watch this lecture to learn how to conditionally format the labels in your x-axis (yes - it's actually a simple trick!)
Lecture 56
Include a Reference Region
08:59
Reference lines or a reference region can be an effective method to show how you have performed this year as compared to last year or a certain min and max value. For example if you consider last year as �the norm� then you�ll have a good way of seeing if you outdid your norm this year and which month you achieved that. In this lecture you'll learn how to combine two different Excel chart types to create a line series and compare this to a reference region.
Lecture 57
Learn how to Create a Pin chart for Variances
09:43
Generally bar charts are used to reflect deviations. I prefer to use a Pin Chart instead. I find it to be more effective and elegant and when paired together with a column chart tells a complete story. Learn how to create a Pin Chart in Excel in this lecture.
Lecture 58
Exercise - Effective Chart to Compare Actual to Previous Year
Article
Section 12: Predictive Analysis - Showing Outlook Development
Lecture 59
Use Continuous Line Series to show Outlook Development
02:12
An effective way of presenting your forecast is to show this as a continuation of actual data series. One thing to be careful though is that you want your reader to be very clear where your actual ends and where your forecast begins, so make sure you do use differentiating colors. This lecture shows you how to setup your chart data to get one line series for forecast, actual and budget with different colors.
Lecture 60
Great trick - Add a Divider to emphasise where Outlook Starts
06:59
Adding a divider or a vertical line in your Excel chart makes it clear to the reader until which month you are showing actual data and from which month you have outlook data. If you are using a divider in your chart for the first time, you'll need to add an explanation for the divider. After that, be consistent in your reports and your readers will benefit. This lecture, not only shows you how to use a divider, but also applies the automatic legend positioning we learnt in a previous lecture, as well as a conditional formatting trick to emphasize parts of your chart data table.
Lecture 61
Exercise - Show Outlook Development
Article
Section 13: Most Effective Chart Combinations to Enhance your Reports
Lecture 62
Effective Chart Combinations - Overview
01:30
This Lecture provides you with an introduction to the charts within this section. You will learn how to combine different Excel chart types and sometimes the same chart type coupled with a specific technique, to get the optimum impact for the information you�d like to convey.
Lecture 63
Scatter & Bubble Charts
07:20
Excel Scatter & bubble charts are useful when you�d like display a relationship between two sets of quantitative variables. This lecture provides an example of each.
Lecture 64
Comparing Distributions with Box Plots
10:38
Box plots are very useful when comparing distributions of different sets of categories with one another. What they show is a range of values, spread from the minimum to the maximum with a mark somewhere inside that represents the middle or median part of the distribution. This way of visually showing data can be very insightful for organizational reports. By default, Excel does not have a Box Plot chart. With the techniques shown in this lecture, you'll have an Excel Box Plot chart in no time!
Lecture 65
Parts-to-whole Chart - It's Not the Pie Chart
11:38
Organizations can be obsessed with the Pie Chart when showing Parts-to-whole relationships. The method shown in this lecture is an Excel Pie-Chart-Free method that is highly effective and information dense. Introduce this to your Reports & impress your managers!
Lecture 66
Pareto Chart - 80-20 rule
03:25
Excel Pareto charts are a good way to illustrate the key factors and contributors to the whole picture. In this lecture you will learn to create a dynamic sorted Pareto Chart - no manual sorting here! instead we use the formulas we learnt in the section Behind the Scenes Secrets for Dynamic Charts.
Lecture 67
Panel Charts - Showing Many Variables at Once
11:13
When you have too many variables to show in Excel, don�t show it all in a single graph. It would create a color collage and take too much energy from your readers trying to understand your message. What you can do instead, is to create an Excel Panel Chart, which looks like multiple charts beside each other. It is however, just one chart. We will use my favourite technique here to give the impression that we have multiple charts beside each other.
To create a chart matrix in Excel you can position various horizontal panel charts above each other as also shown in this lecture.
Lecture 68
Sparklines for Compact Charts
05:04
Sparklines were introduced from Excel 2010 and represent a compact chart that fits in one cell. They are an easy way of creating a simple Panel Chart.
Lecture 69
Exercise - Effective Chart Combinations
Article
Section 14: Waterfall / Bridge Chart
Lecture 70
Waterfall Chart Overview
01:46
Effective Excel Waterfall or Bridge charts, tell a story about the development of a certain value and the factors that influence this development. Waterfall charts can be tricky to make, especially if you'd like it to have connectors between the bars and show negative total values, i.e. go through the negative vertical axis.
In this lecture, I will show you a super easy and dynamic method to create an Excel Waterfall chart that will also show negative total values and have connectors between the bars to enable readers to better follow the development of your variable.
Lecture 71
Easiest Method for the Most Flexible Waterfall Chart
08:19
Lecture 72
Waterfall Chart - Step by Step
12:38
Lecture 73
Exercise - Waterfall Chart
Article
Section 15: Final Words
Lecture 74
Final words
00:57
Lecture 75
Bonus: More From Leila
Article