Lookup Functions, Advanced Charts and Pivot tables

Discover how lookup functions power advanced charts and pivot tables, and learn essential techniques for dynamic data visualization and analysis.

List the basic types of charts in Excel and describe the functionality of each to meet the lab objectives.

Explore the line chart, an advanced Excel course tool to show trends over time, and review basic chart types for comparing revenues across regions over five years.

Explore how a clustered column chart compares revenue across three years within each region and between regions, enabling comparison of multiple data categories inside and across Sabai items.

Explore stacked column charts to compare items within a value range and reveal how each region's revenue contributes to the whole, year by year.

Explore how a pie chart displays distribution and proportion of each data item, showing each region’s contribution to the total revenue.

Bar charts compare revenues across different regions for a specific period, illustrating how each region performs and highlighting comparisons among items.

Explore the basic types of Excel charts and their functionality as part of mastering charts in a course on lookup functions, advanced charts, and pivot tables.

Describe the functionality of area, scatter, stock, and bubble charts; differentiate subtypes, outline creating and converting charts, modify elements, and enhance appearance with regression analysis, trendlines, and comparisons.

Master area charts in Excel, an enhanced line chart that shows trends over time, sums of plotted values, and the relationship of parts to a whole.

Convert a line chart to an area chart by selecting the chart, choosing change chart type under the design tab in chart tools, selecting area, then adjusting the chart title.

Convert a chart to a 3D area chart to improve readability by right-clicking an empty area, selecting change chart type, and choosing the 3D area option for a better representation.

Adjust transparency to reveal data behind the foreground in a 3-d chart. Right-click each series, choose format, and set fill transparency to the desired level.

Change the chart type to a stacked area chart to reveal at-a-glance revenue insights by region and period, highlighting the highest and lowest performing regions.

Explore how scatter charts display numeric values and plot two datasets with an x y scatter plot to reveal relationships.

Learn data plotting with a scatter chart that maps x and y coordinates, showing orange x values and green y values, using a selected range to reveal axis labels.

Explore regression analysis in Excel, examining the relationship between data sets to determine association, such as advertising expenditures with sales and exercise with lifespan, using an x y scatter chart.

Create a scatter chart to analyze how advertisements relate to sales, selecting data and choosing the scatter x and y option (or bubble chart).

Use trend lines to reveal relationship between fads and sales, add a linear trend line via chart tools, and display the equation Y = 8x + b on the chart.

Highlight the difference between line charts and scatter charts, focusing on how the horizontal axis plots data and how scatter charts use two numerical axes for x and y intersections.

Compare line charts and scatter charts, highlighting axis representations, data categories, and trends; learn when scatter plots reveal associations better than line graphs.

Compare actual temperatures with predicted temperatures using a scatter chart with lines and markers, and switch chart types to show smooth lines that connect data points.

Explore types of scatter charts, including smooth-line scatter, scatter with straight lines, and scatter with straight lines and markers to visualize relationships clearly.

Bubble charts replace scatter points with bubbles, using x and y numeric fields and a third field for bubble size to reveal relationships across items, such as state populations.

Create a bubble chart by using the insert tab, charts group, selecting the 3D bubble option, and sizing bubbles by the percent of total population values.

Color bubbles by applying different fill colors using the format options in the display pane. Check the varicolored box, close the form, and de-select the bubbles to see the result.

Display a legend mapping each bubble color to its state and show percentage values on bubbles. Select a child, enable data labels, increase bubble size, and center labels for visualization.

Stock charts plot data arranged in columns or rows on a worksheet, showing fluctuations in values like stock prices, scores, rainfall, and temperatures.

Explore stock charts, including candlestick charts, and learn the four types of stock charts, with key terms such as open, high, low, close, and volume.

Arrange the data in the exact order required by the stock chart, placing high, low, and close as column headings in that sequence. The column names for a stock chart always appear in the same order, and the volume and open columns may be included or omitted.

Create a high-low-close stock chart by selecting the data and inserting a stock chart, where vertical bars show daily high and low, with markers for the closing price.

Format a stock chart to improve readability by adjusting marker styles and colors using the design tab and chart style options.

Explore how varying analysis periods affect charts, identify vertical bars and markers when using longer time spans, and learn to plot charts by week or by month.

Learn how stock charts convey volumes alongside open, high, low, and close data, highlighting trade volumes and chart formatting options.

Explore using area, scatter, stock, and bubble charts, differentiate chart subtypes, convert between types, compare chart types, organize data to create trendlines and regression analyses.

Describe surface red-eye and combination charts, differentiate subtypes to the suitable child type, create and convert between child types, modify elements, enhance appearance, and combine multiple child types.

Surface charts show a three-dimensional surface that connects points to reveal optimum combinations between two data sets. Colors indicate value ranges, and colors distinguish values rather than data series.

Explore the four types of surface charts: 3D surface, wireframe 3D surface, contour, and wireframe contour, and understand how each presents data.

Create a 3-D surface chart to reveal trends across two dimensions, using color bands to distinguish values and highlight relationships in large data sets.

Open the design tab, change the chart type to a wireframe 3d surface, and click okay, revealing a lines-only 3d surface chart with no color bands.

Explore contour charts that present surface data from above, using color bands to show value ranges and contour lines that connect points of equal value.

Explore wireframe contour charts and surface charts viewed from above, where only lines appear without color bands, making such charts hard to read.

Explore radar charts, also known as spider or star charts, which plot each category on its own axis from the center to the outer ring and reveal competitive data patterns.

Create a radar chart to compare toy sales by item, using the insert tab and radar option. The chart highlights peaks like dolls in March and teddy bears in October.

Explore the types of radar charts and compare how markers and filtrate influence their visual representations.

Explore combination charts that blend multiple chart types, such as column and line charts, to emphasize diverse data and handle varying city value ranges with dual axis capabilities.

Create a combination chart to effectively visualize mobile handsets sales data, showing why a column chart alone may be insufficient and how combining chart types enhances insights.

Learn how to change chart type to a combo chart by using the design tab, preview different chart types, and adjust the second axis for a clearer visualization.

Add axis titles with the chart elements button to clarify the vertical axis, then set a suitable chart title that reflects sales and average handset price across months.

Apply chart styles from the design tab under chart tools to enhance your charts by selecting a suitable style.

Explore surface radar and combination charts, differentiate chart subtypes, convert between chart types, modify chart elements, and enhance charts by combining multiple chart types.

Define chart templates and their utility, explain chart customization, and outline how to save and apply templates to new and existing charts, including moving or deleting templates.

Discover chart templates to reuse and customize charts, apply templates when creating new charts, or change an existing chart's type using a .crx template.

Customize a chart by applying full color, adding chart elements, choosing a word art style from the format tab, and applying a shape effect before saving the worksheet.

Save a chart as a template by right-clicking a blank area, choosing save as template, naming the file, and clicking save.

Select the detail to plot on the insert tab in the charts group, then choose a recommended template from the left pane and click okay to create a formatted chart.

Select an existing chart, open the design tab, and choose change chart type to apply a template. Then apply the desired template and adjust the child size as required.

Move or delete a child template by removing it from the child's template folder or deleting it; access templates via the insert tab charts group and choose cut or delete.

Explore the utility of templates, customize charts, save a tight template, apply templates to new and existing charts, and move or delete chart templates.

Define spark lines and chart types, and outline creating spark lines. Explain altering spark line design, differentiate groups from individuals, and manage empty or hidden cells.

Explore sparklines, tiny charts embedded in a worksheet cell that provide a visual representation in the background of the cell.

Compare sparklines and charts, sparklines provide a clear overview for many rows, placed near source data to show relationships and trends, while charts offer greater detail for comparing data.

Explore the three sparklines types: line sparklines, column sparklines, and a third option that shows positive or negative values instead of magnitude.

Create sparklines by selecting the data range, choosing a line type in the insert tab's sparklines group, and placing the sparkline in the adjacent cells to visualize trends.

Alter sparklines design to highlight the highest and lowest points, display markers, and customize line style and colors using the design tab.

Create and compare sparkline groups and individual sparklines, understanding how a group of sparklines reacts to changes while single sparklines can be edited independently, including June and July data.

Select the cells containing sparklines and delete them via the design tab, group, and clear commands.

Compare sparklines within a group by scaling each line to its own max and min, showing high points in red and others in blue with equal spike line heights.

Modify the display range to spotlight SPARC lines by setting minimal and vertical axis maximum values, applying the same settings to all lines within the group for easy comparison.

Learn how to manage missing data in spark lines by adjusting hidden and empty cell settings in Excel, choosing to display missing values as gaps or markers.

Enable option 2 in the hidden and empty cell settings, set missing values to zero, and observe spark lines in cells x4 and x6 with six data points each.

Explore option 3 for handling empty cells: connect datapoints while ignoring missing values, so spark lines in cells 4 and 6 show only the available points.

Explain how Excel's hidden and empty cell settings show data in hidden rows and columns, displaying values on start lines even when their rows or columns are hidden.

Compare spark lines and charts, learn line types, and create spark lines with design changes. Distinguish sparkline groups from individual sparklines, and manage empty or hidden cells and spot lines.

Introduction to lookup functions, advanced charts, and pivot tables. This lecture outlines the foundational concepts of these tools in the course.

State the utility of paper charts, outline how to create and format pivot charts, and explain the impact of applying filters to pivot tables and charts.

Create pivot charts from pivot tables to visualize month-wise totals by salesperson, selecting a chart type and style, and using a slicer on the salesperson field.

Manipulate pivot table options to control chart data and view, showing how filters on the pivot table affect the pivot chart and remove hidden months from the chart.

Explore using field buttons in pivot charts and paper charts to apply filters directly, instantly updating the pivot table display.

Use slicers to dynamically change the data shown, selecting one or more salespeople so the pivot chart and pivot table update automatically to reflect the new data.

Create a pivot chart before a pivot table by selecting data, using the insert tab to add a chart, and dragging fields into legend, axis, and values to generate charts.

Learn how to format pivot charts like other charts by applying a chart style from the design tab in pivot chart tools.

Learn to create and format charts, manipulate pivot charts, and apply filters to pivot tables and charts, including chart creation before pivot tables.

Outline the process of creating tables and charts, analyze data with pivot tables and charts, and use slices and timelines to enhance tables and charts.

Learn to analyze employee data in Excel using pivot tables and charts, creating a table in a new worksheet and exploring range selection and data visualization.

Use a pivot table to count how many months each male and female employee has solved by placing gender and months in the fields and using count instead of sum.

Create pivot charts to visualize data using different chart formats, revealing attrition peaks and resignation counts by months through graphs that support data analysis.

Create and use calculated fields to count employees resigned per program, organize results with pivot tables, and visualize insights with charts in Excel.

Add and use data slicers to filter a data table by fields such as program name, show the count of employees and the program code, and reset the view.

Utilize the new timeline filter option to view monthly data, filter employees by join month, and reveal join and departure details by program.

Choose from various pivot table styles to modify the table, and apply similar styling to the timeline and spaces to enhance the look of our analysis.

Learn how to create pivot tables and pivot charts, analyze data using tables and charts, and use slices and timelines to explore data.