Data Analysis with Excel
Module – 2
Working with Charts and Tables
Creating Tables
• Tables in Excel are a structured range of data that makes it easier to analyze
and manipulate the information.
• They come with built-in functionality for sorting, filtering, and formatting,
which can significantly enhance productivity.
Go to the Insert tab.
Click Table.
Choose the number of rows and columns by dragging your cursor over the
grid.
Formatting Tables
• Table Design
Select the table.
Go to the Table Design tab.
Choose from predefined table styles or customize the table’s look by changing the
fill, border, and effects.
• Adjusting Table Size
Drag the edges of the table to resize it.
Use the Table Tools Layout tab to adjust the height and width of rows and
columns.
• Merging Cells
Select the cells you want to merge.
Right-click and choose Merge Cells.
• Splitting Cells
Select the cell you want to split.
Right-click and choose Split Cells.
Working with Charts
• Insert a Chart
Go to the Insert tab.
Click Chart.
Choose the type of chart you want (e.g., bar, line, pie).
Enter your data in the Excel spreadsheet that opens.
• Formatting Charts
Select the chart.
Go to the Chart Tools Design and Format tabs.
Change the chart style, color, and layout.
Customize elements like the title, axis labels, and legend.
Linking Charts and Tables
• Linking a Table
Create your table in Excel.
Copy the table in Excel.
In PowerPoint, go to the slide where you want to place the table.
Click Paste Special and select Paste Link.
• Linking a Chart
Create your chart in Excel.
Copy the chart in Excel.
In PowerPoint, go to the slide where you want to place the chart.
Click Paste Special and select Paste Link.
Advanced Analysis Tools
• In order to execute complicated data analysis, reporting, and visualization
tasks in Microsoft Excel, users must employ a collection of tools, functions,
and features called “Advanced Excel.”
Pivot tables
Lookup features
Data validation
Macros
• Data analysts, accountants, and financial experts among others utilize
advanced Excel to swiftly and accurately analyze data and produce
insightful results.
Pivot Tables
• A pivot table is a data summarization tool that automatically sorts, counts,
and totals the data stored in one table or spreadsheet and displays the results
in a new table (pivot table).
• They are particularly useful for analyzing numerical data and exploring data
relationships.
Lookup features
• Lookup features in Excel are essential for finding and retrieving specific
data from a larger dataset.
• This often takes place when there are multiple worksheets within a
workbook or a large amount of data in a worksheet.
Data validation
• Data validation in Excel is a feature that allows you to control the type of
data or the values that users can enter into a cell.
• This ensures that the data entered into your worksheet is accurate and
consistent, helping to prevent errors and maintain data integrity.
Macros
• Macros in Excel are powerful tools that allow you to automate repetitive
tasks, improve efficiency, and ensure consistency in your workflows.
• Macros are like smart helpers that do repetitive work for you. You can tell
them what to do, and they’ll do it over and over, saving you time. Think of
them as tiny little computer programs for Excel.
Create a table to track monthly expenses and income to keep an organized record of
all financial transactions. Columns include Date, Description, Category, Amount, and
Balance.
Rules
• Apply conditional formatting to highlight expenses exceeding 50000.
• Create a pie chart to show the distribution of expenses by category.
• Ensure the pie chart updates automatically when new expense data is added to the
table.
Develop a table to manage project tasks, including task name, start
date, end date, assigned team member, and status.
Rules
• Use color coding to indicate task status (e.g., Not Started, In Progress,
Completed).
• Create a Gantt chart to visualize project timelines.
• Link the Gantt chart to the project tasks table so updates in task dates reflect in the
chart.
Create a table to record student grades across different subjects to
maintain a structured record of student performance. Columns
include Student Name, Subject, Grade, and Term.
Rules
• Apply conditional formatting to highlight high and low grades.
• Create a line chart to show grade trends over different terms
• Ensure the line chart updates automatically with new grade entries.
Develop a table to collect and organize customer feedback data to
systematically gather and analyze customer feedback. Columns
include Date, Customer ID, Feedback, Rating (1-5), and Comments.
Rules
• Apply formatting to differentiate high and low ratings by giving bold text
for ratings above 4 and red text for ratings below 3.
• Create a bar chart to visualize the distribution of customer ratings.
• Link the chart to the table for real-time visualization by ensuring the bar
chart updates automatically with new feedback entries.