| Understanding |
| Creating charts by selecting data on the same worksheet |
| Creating charts by selecting data on a different worksheet |
Creating Chart by simply clicking a cell on the block of data ( surrounded by empty cells) |
You see an alert of creating a chart as soon as you select the data on the lower right hand corner |
By Selecting data and going to insert tab , click recommended charts |
By Selecting data and going to insert tab , look for some of the chart options |
| Use the link on the side to see all chart types and how to use them |
| After creating a chart, you can select different chart styles |
Once you have selected a chart styles, you can select quick layout |
| Once you have selected the layout, you can change colors |
How to effect the entire workbook by using the page layout tab, it changes the background, colors as well as font |
Swap rows to columns - use it frequently, it displays data in a different way |
What if you like both charts: original and after swapping : using Ctrl + D |
How to quicky create chart with first 3 year and first 5 years from a time series data of 12 years using Alt+F1 |
How to move a chart to a different sheet using right Click and on chart design tab |
| How to move a chart to a different sheet with 'object in' |
| How to resize a chart using the four corners |
| How to resize the chart keeping the aspect ratio same |
| How to make the chart wide or tall on both sides |
How to make the chart line up with the boundaries of the background |
| Components of charts are called chart elements |
| The outer area is called the chart area |
| The inner area is the plot area |
| legend |
| vertical grid lines |
| Data labels |
How to add chart element by the add chart element at the left |
| How to add chart element by the + button on the right |
How to see all the chart elements in a chart, by hovering mouse over elements of + |
How to exclude a data column from the bar chart, by clicking on that column on the chart |
| How to add a data column to a chart |
| How to add a data column to a chart another method |
| How to add a data column to a chart third method |
| How to select data in the chart |
| How to select data in chart ribbon |
| How to put zero to the missing values |
| How to connect missing data points with line |
How to hide some rows from the data so as not to show on the chart |
| How to see the hidden rows in the chart |
How to change the default chart type when you click Alt + F1 |
How to keep a default set of features when you click alt + f1 |
| How to remove the default set of features of a chart |
| How to adjust the highest value on the y axis |
| How to adjust the lowest value on the y axis |
| How to change the chart title |
| How to change the chart title: another way: Quick Layout |
| How to change the axis title |
| How to change the titles: another way: Add chart elements |
| How to change the titles: fourth way: Plus + Button |
| How to remove a title |
| How to link titles to content so that column headers show as titles in the chart |
| How to link titles to content: another way |
| Adding Data Labels: First way : through quick layout |
Adding Data Lables: Second way: Use Chart Element Button |
Adding Data Labels: Third Way: Use Plus button at the right |
| How to Add data labels to one of the series only |
| Adding data labels to the pie chart: Using chart design tab |
| Adding data lables in pie chart: Using Plus button |
| Adding Percentages to the pie chart |
| How to add value also to the pie chart |
| How to add category name also to the pie chart |
| How to add leader lines to the pie chart |
Leader lines have to be moved individually, one label at a time |
| How to show the data along with charts: Quick Layout way |
| How to show the data along with charts: Plus Button Way |
| Changing Gridlines: The Chart Style way |
| Changing Gridlines: The Quick layout way |
| Adding Minor gridlines: The Quick layout way |
| How to make the gridlines more prominent |
| How to change the appearance of minor gridlines |
| How to change gridlines: The plus way |
| How to change legends: Chart style way |
| How to change legends: Quick Layout way |
| How to change legends: The Plus Button way |
| How to change legends: The Add chart element way |
| How to format legends, font size, bold, italic, |
So we want to just see how much will be one standard error, or some percentage or some standard deviation away from this value |
| How to add error bars |
| How to add only the top errro, or the bottom error bar |
| How to change the error amount |
| Adding error bars from Plus button |
| Removing error bars from the add chart element |
| Using Drop line |
| Adding drop line from chart design element |
Adding High low lines two show differences between two series |
| How to remove the lines with adding chart elements |
| How to add a trend line by clicking the line |
| How to add an exponential trend line |
| How to add logrithmic, polynomial, power |
| How to add a trend line of moving average |
| How to display R-square factor on the chart |
| How to extrapolate the linear trend line by six periods |
| How to have a trend line on a column chart |
| How to use pictures instead of columns |
| How to stretch the pictures |
| How to stack the pictures |
| How to stack and scale the pictures |
| How to change the gap between the pictures |
| How to use icons instead of columns |
| How to insert a box, arrows or pictures in a graph |
| Setting up chart in an F |
| Leveraging color schemes |
| Use Adobe Color CC to choose color scheme |
| Using color in Power BI from Adobe |
| How to format |
| How to change the background |
How to add a text box in the chart and convert it to free floating text |
How to put a text which is associated with data or text that's on worksheet |
| How to change all the major titles at once |
| How to change the angle of the x axis |
| Apply the Wordart style to the title |
| Bar charts are column charts rotated ninety degrees |
| What are clustered charts |
| What are stacked charts |
| What are 100% stacked charts |
| Column charts are good to show volume not value |
| How to rotate the 3-D column charts |
| Line chart is best if you are trying to show a trend |
| How to smoothen a line |
| How to create an area chart |
| How to create a stacked area chart |
| How to drag all the parts of a pie |
| How to draw out one wedge |
| How to add data labels to a pie chart |
| How to add percentage and even category |
| How to create a donut chart |
| How to reduce the size of the donut hole |
| How to add data labels |
| Adding percentages to the donut charts |
| Used when two series are widely different in magnitude |
| How to create combo charts |
| How to manipulate the secondary axis |
| How to change chart type in a combo chart |
| How to use stock chart |
| How to plot stock chart with volume |
When we are trying to show the relationship between two series |
| How to add a scatter chart |
| How to change the min and max in the x axis |
| How to change the min and max in the y axis |
How to change the min and max in the y axis specially in time when it cannot be done by formatting |
| How to add a trend line |
When we are trying to show the relationship between three series |
| How to insert bubble chart |
| What to do if the bubble chart is not coming right |
| How to change the min and maximum in the bubble chart |
| How to change the bubble size |
When we are trying to show the relationship between two series |
| How to insert radar chart |
| How to exclude a point in the radar charts |
| How to change the chart styles |
How to exclude the weekend in calculating the schedule with WORKDAY |
| How to create a gantt chart |
| How to change the categories in reverse order in axis |
A Gantt chart is actually a stacked bar hinding the position of the bars |
| When to use treemaps |
| How to use treemaps |
Use it when the data is organized hierarchically and has fewer categories |
Sunburst chart to be used when the data is organized hierarchically and has many categories |
| How to add datalabels in the tree map charts |
| Use when the data is like Treemaps |
| How to add data in sunburst |
| To find out how many people are in between two values |
| How to insert histogram |
| How to change the bin width or number of columns |
How to get the underflow Bin, where you can start your lowest value |
How to get the overflow Bin, where you can start your highest value |
| How to add data labels |
| How to create or add a box plot |
| The Box represents half of the entries |
| Top of the box is the max entry among the middle half |
| Bottom of the box is the lowest entry among the middle half |
| X represents the mean |
| The line above represents the median |
| How to add data labels in box and whisker plot |
| How to remove data labels individually |
Earlier You need to calculate cumulative frequency and cumulative frequency percentage. Now you don't need |
You need to put them in descending order, now you dont need |
| Now you just need a category and a value column |
| How to insert Pareto chart |
A waterfall chart can be used to present the sales timeseries data |
| How to insert waterfall chart |
| How to change the gap |
| How to format the columns |
| For Sales funnel prospects--> Qualified--> Converted |
| How to insert Funnel Chart |
| How to Create Chart from Multiple Data Source |
| How to accommodate the chart for more data, manually |
How to update chart automactically, by adding additional rows |
| How to print a chart on the separate sheet |
| How to print a chart out of a number of charts |
| How to print more than one chart on an excel sheet |
How to print all charts in one page, if they are coming outside of the page |
| How to copy a chart on word and powerpoint |
| How to paste the chart as a chart and as a picture |
| How to copy the chart on to the powerpoint |
| Use it for cross tab between two values |
| How to insert sparklines |
| How to show the axis on the sparklines |
| How to change the sparklines colors |
How to change the min value same for all the column sparklines |
| How to represent a number by a series of symbols |
| How to create in cell bar charts |
How to show bars only, use when you want to show the relative magnitude but not show the numbers |
| How not to make bar overlap the numbers |
| what are the key principles of data visualization |
| What is the 10 second rule of data visualization |
| Data for Practicing Visualisation |
| Visualisation Slides |
It is useful to get rid of the gridlines when you are working with visualization |
| How to get rid of the gridlines |
| How to create charts of non adjacent colummns |
| How to remove some of the ticks from y axis |
| How to add a combo chart |
| How to add a secondary axis |
| How to reset the changes in the axis |
How to modify a combo chart with Column and Line: chart: Ideal |
| How to save as a template |
| How to apply the template |
| How to make slight adjustment to the template |
Which chart to use to compare numerical data across categories |
| Which chart to use to visualize sales by product type |
| Which chart to use to visualize population by country |
| Which chart to use to visualize revenue by department |
| Which chart to use to visualize revenue by quarter |
| Which chart to use to group by subcategories |
| which chart to use to compare multile metrics |
Which chart to use : By month by genre, by department by quarter, |
We use the bar and column chart for a crosstab between two numerical series |
| How to insert a bar chart |
| What to do if we get a blank chart |
| How to change the axis label range |
whcih chart to use to show the distribution of a continuous data set |
Which chart to use to show frequency of test scores among students |
Which chart to use to show distribution of population by age group |
| Which chart to use to show distribution of height or weight |
We use pareto chart to show the cumulative effect of each bin, ordered by significance |
Column charts are designed to show volumes, histograms are designed to show frequencies |
| How to create histogram |
| How to create a pareto version of it out of histogram |
How to change the histogram for one series to another and copy |
| Which chart to use for visualizing trends over time |
| Which chart to use to show stock price by hour |
Which chart to use to show average temperature by month |
| Which chart to use to show profit by quarter |
| How to create line charts |
Which chart to use to show changes in data composition over time |
Which chart to use to show sales by department, by month |
Which chart to use to show % of total downloads by browser, by week |
Which chart to use to show % of total downloads by continent, by decade |
Keep the number of unique categories low to maintain clarity |
Use data validation and custom formatting to dynamically highlight specific data series |
| How to create an area chart |
Area charts tell the same story that we tell using the stacked column charts |
| How to make the area charts transparent |
| Where we are going to use a stacked area chart |
How to convert an area chart to a 100% stacked area chart |
Which chart to use for comparing proportions totalling 100% |
Which chart is used to show Percentage of budget spent by department |
Which chart is used to show proportion of internet users by age range |
Which chart is used to show breakdown of site traffic by source |
Keep the number of slices small ( <6 ) to maximize readability |
Use a donut chart to visualize more than one series at once |
Use transparent segment to create a custom "race track" visualization |
| How to create pie chart |
| We use racetrack as a KPI or target vs. achievement |
| How to convert a pie chart into a donut chart |
| How to add a data series to the donut chart |
How to add a data lable to the inner and outer circle of donut chart |
| How to change the donut hole size |
How to differentiate the color between inner and outer circles in a donut chart |
How to convert a donut chart to a racetrack chart or a gauge |
| How to add a scatter plot |
| How to change the origin from 0 to some other value |
How to reduce the transparency of points and prominency of trend lines |
Which chart is used to add a third dimension to the scatter plot |
Which chart is used for showing relationship between Product sales, revenue and market share of a company |
| Which chart is used for X, Y and size of a data |
Use color as a dimension to differentiate between categories |
Use Cell formulas and form controls to create a dynamic, animated bubble chart |
Create a bubble chart or convert a scatter plot to bubble chart |
| How to create a bubble chart from empty chart |
| How to scale the bubble size |
Which chart is used to show statistical characteristics across data series |
whcih chart is used for comparing annual rainfall across cities |
Which chart is used for analysing distribution of values and identifying outliers |
| Comparing mean and median height and weight in a city |
| How to add the box and whisker plot |
| How to hide/show points |
| How to show mean line |
Which charts are used for visualizing hierarchical data with natural groups/subgroups |
Which charts are used for Book title, Sub genre and Genre |
Which charts are used for Number of employees by department and office |
| Which charts are used for population by state and region |
Use Treemap charts when you are visualizing 1 or 2 hierarchical level |
Use Sunburst charts to visualize the depth of multiple hierarchical level |
Make sure that your raw sourced data is grouped and sorted before creating hierarchical charts |
| How to create a treemap |
| How to create a sun burst chart |
Which chart is used for showing the net value after a series of positive and negative contributions |
| Which chart is used for corporate balance sheet analysis |
| Which chart is used for personal income and spending |
| Use Subtotals to create checkpoints |
| How to create waterfall charts |
| How to adjust the waterfall charts from the beginning |
Which chart is used to show progress through the stages of a funnel |
Which chart is used for the following: views, clicks and sales |
Number of runners who reach each checkpoint in a marathon |
use "Percent of Total" Calculations to show % of users (rather than #) at each funnel stage |
Customize colors to emphasize Progression towards the end goal |
| How to add a funnel chart |
| How to have a solid filled radar charts |
| Visualizing trends or relationships using color scales |
| Which chart is used to:depict |
| Accident rate by time of day and day of week |
| Average temperature by city, by month |
| Average sentiment by hashtag |
Use intuitive color scales ( red to green) and apply custom formatting to hide cell values |
Use data validation and custom formatting to dynamically create heat maps |
| How to create heat maps |
Which chart is use to Plot data in three dimensions to find optimum combination of values |
| How to create surface and contour charts |
| How to rotate surface chart |
| How to convert the contours of the chart from green to red |
| Can be used for KPI |
How to have a thermometer and a thumsup icon for remaining |
How to highlight the values of some dates in a time series, eg. promo dates |
| How to de crowd the x axis |
| How to make the text of the y axis completely invisible |
How to make the chart dynamically updated using OFFSET and COUNTA |
| How to use OFFSET command |
How to make chart update as you add new rows using table |
How to make chart update as you add new rows using OFFSET and COUNTA |
You can make charts that can automatically scroll and zoom |
| How users can manipulate scross bars in the workbook |
| How to use form controls to build interactivity in the chart |
| How to animate changes over time |
How to use Group as a tool to help creating dynamic dashboard |
How to use Data Validation as a tool to help creating dynamic dashboard |
How to use Sumif as a tool to help creating dynamic dashboard |
How to use Conditional Formatting as a tool to help creating dynamic dashboard |
How to use Format Painter as a tool to help creating dynamic dashboard |
| How to create charts out of your dynamic dashboard |
| How to create the charts dynamically for any combination |
How to create charts dynamically by creating a dynamic source data array |
How to use INDEX and multiple MATCH as a tool to create a dynamic source data arrary |
How to use OFFSET and MATCH as a tool with NAMED RANGE to create a dynamic source data arrary |
How to highlight any point based on a certain condition eg. >50 |
How to use PERCENTRANK function for value based dynamic formatting |
How to split rows of data into four quartiles using PERCENTRANK |
How to show an area chart in which each area is highlighted when selected |
How the values are pacing up to achieve a target value or goal a KPI |
How to use a donut chart and Scatter plot to create a Gauge and Needle effect KPI |
| How to fill a grid basis the percentage KPI |
How to create a column chart showing values and the markers showing target |
How to create a column chart that "alerts": changes colors depending upon a conditional criteria |
| Charting alert for top/bottom, n, quartiles, percentiles |
Adding a visual indicator to top/bottom n, quantile and percentile chart |
| How to create bullet graph instead of Gauges |
| How to create a variance chart |
| How to create a gantt chart |
How to create a chart that shows whether the project is ahead or behind |
| How to create control charts |
No comments:
Post a Comment