Lesson 3.3: Creating Charts and PivotTables

Introduction to Creating Charts with Agent Mode

In the realm of legal practice, data visualization is not merely a tool for presentation but a crucial mechanism to analyze case trends, financial metrics, and discovery timelines efficiently. Microsoft Excel’s Copilot, particularly its Agent Mode, empowers legal professionals to craft insightful charts directly within their workbooks using natural language prompts. Unlike the traditional manual process of chart creation, Agent Mode allows attorneys and paralegals to instruct Excel to generate specific charts with a simple, conversational approach, reducing time spent on formatting and increasing accuracy. This is especially important in environments where billing accuracy, trust account management, and case progress tracking rely heavily on precise data visualization.

Agent Mode replaces the previous “App Skills” feature and introduces a seamless Preview and Approve workflow. This means your prompt generates a chart preview within your worksheet, allowing you to review and refine before finalizing. The result is a more interactive and error-resistant chart creation process. To maximize Copilot’s performance, ensure your data is organized as Excel Tables (using Ctrl+T) and stored on OneDrive or SharePoint with AutoSave enabled. This setup guarantees that Copilot accesses well-structured data in real time without compromising confidentiality — Microsoft explicitly does not use firm data to train its models, maintaining compliance with legal data privacy requirements.

Common Chart Types and Legal Use Cases: Prompt Guide

Below is a curated table detailing five common chart types, tailored legal use cases, and example Copilot prompts to generate these charts using Agent Mode. Understanding how to frame your prompts for specific chart types will streamline your workflow and enhance your data storytelling capabilities.

Advanced Chart Customization in a Single Prompt

One of the most powerful features of Agent Mode is the ability to customize charts within the same prompt used to generate them. Instead of creating a chart and then manually adjusting colors, labels, and legends, legal professionals can specify these formatting preferences upfront, saving valuable time and maintaining consistency across reports. This is particularly useful when preparing client-facing presentations or internal dashboards where clarity and branding are critical.

For example, you might want to create a column chart of billable hours by attorney but also specify that the bars be blue, the chart title to be bold and centered, the legend placed at the bottom, and data labels shown above each bar. Agent Mode can interpret these instructions simultaneously, producing a fully formatted chart in one step. This capability reduces the back-and-forth of manual adjustments and aligns with the needs of busy legal teams who require polished deliverables quickly.

“Create a column chart showing billable hours by attorney for the last 12 months with blue bars, bold centered title ‘Monthly Billable Hours’, legend at the bottom, and data labels above each bar.”

Moreover, you can specify axis titles, gridline styles, and even chart size or placement. For example, when working with settlement amount trends, a legal analyst might request a line chart with a thick red line, gridlines only on the horizontal axis, an italicized chart title, and the legend removed for simplicity. Agent Mode’s natural language understanding enables this level of detail, making chart creation both efficient and tailored to professional standards.

“Generate a line chart plotting settlement amounts by year for the last five years with a thick red line, horizontal gridlines only, italicized title ‘Settlement Trends’, and no legend.”

Such advanced customization prompts substantially reduce the need for manual chart tweaking, which often consumes significant time during report preparation. Legal professionals can thus focus on data analysis and strategy rather than formatting minutiae.

Creating PivotTables Using Natural Language Prompts

PivotTables are indispensable tools for legal professionals managing complex datasets such as billing records, case statuses, or discovery document inventories. Copilot’s Agent Mode simplifies PivotTable creation by allowing users to describe their desired analysis in plain English, then automatically generating the PivotTable layout. This eliminates the traditional multi-step drag-and-drop process, making data summarization accessible and faster for attorneys and paralegals alike.

Here are three detailed examples illustrating how natural language prompts can generate powerful PivotTables tailored to legal workflows:

1. Summarizing Billable Hours by Attorney and Month
   A legal billing manager wants a breakdown of billable hours segmented first by attorney and then by month, including total hours for each. The prompt might be:

   “Create a PivotTable summarizing total billable hours by attorney and month.”

   Agent Mode will produce a PivotTable with attorneys as rows, months as columns, and the sum of billable hours as values, allowing quick identification of workload distribution and overtime trends.

2. Tracking Case Status Counts by Practice Area
   A litigation coordinator needs to understand how many cases are currently open, closed, or on hold, grouped by practice area. The prompt could be:

   “Generate a PivotTable counting cases by practice area and case status.”

   This results in a matrix that helps prioritize resources by showing where active cases cluster and where bottlenecks exist.

3. Analyzing Discovery Document Counts by Case and Document Type
   A paralegal overseeing discovery wants a summary of document counts broken down by case and document type (e.g., emails, contracts, affidavits). The prompt example is:

   “Create a PivotTable showing total discovery documents by case and document type.”

   The output facilitates quick assessment of discovery completeness and highlights areas needing further collection or review.

These examples demonstrate how natural language-driven PivotTable creation can transform complex legal data into actionable insights with minimal technical expertise, enhancing both efficiency and data comprehension.

Combining Charts and PivotTables for Interactive Dashboards

Legal professionals often need to present data in a dynamic, interactive format that allows them to explore different perspectives without creating multiple static reports. By combining PivotTables and charts, and leveraging Excel’s interactivity features, you can build powerful dashboards to monitor case progress, financial metrics, or discovery status. Below is a practical five-step workflow to create a professional legal dashboard using Agent Mode.

1. Prepare Your Data as Excel Tables:
   Ensure all datasets (e.g., billing records, case logs) are formatted as Excel Tables (Ctrl+T). This structure enables Copilot to reference data accurately and allows slicers and timelines to interact correctly with PivotTables.
2. Create PivotTables with Specific Prompts:
   Use natural language prompts to generate PivotTables that summarize key metrics such as total billable hours by attorney, case counts by status, or trust account balances by quarter.
3. Generate Corresponding Charts:
   For each PivotTable, instruct Agent Mode to create an appropriate chart type that visually represents the summarized data—column charts for billing, pie charts for case distribution, line charts for financial trends.
4. Add Slicers and Timeline Filters:
   Insert slicers for categorical fields (e.g., attorney name, practice area) and timeline filters for date fields (e.g., billing month, case open date) to enable interactive filtering across all PivotTables and charts.
5. Refine and Arrange Dashboard Layout:
   Use Agent Mode to customize chart colors, titles, and legend placement, then arrange all components on a single worksheet page for an intuitive, user-friendly dashboard experience.

This workflow streamlines the dashboard creation process, allowing legal teams to quickly analyze and communicate vital information. Interactive dashboards are particularly valuable for managing law firm performance metrics, client case status reporting, and financial oversight.

Using Slicers and Timeline Filters for Interactive Dashboards

Slicers and timeline filters transform static dashboards into interactive analytical tools that allow legal professionals to drill down into specific subsets of data without rebuilding reports. Slicers provide clickable buttons to filter categorical fields such as attorney names, practice areas, or case types. Timeline filters specialize in filtering date ranges, enabling users to analyze data for particular periods quickly.

For example, a managing partner might use slicers to focus on cases handled by a particular team or filter billing data by client matter. A paralegal might use a timeline filter to examine discovery document counts only within a certain quarter or year. These interactive controls sync across all connected PivotTables and charts, updating them dynamically to reflect the selected filters.

To add slicers and timeline filters:

• Select the PivotTable or chart linked to the data.
• Navigate to the Insert tab and choose “Slicer” or “Timeline.”
• Select the fields to filter (e.g., Attorney Name, Case Status, Billing Date).
• Position the slicers or timeline controls on your dashboard for ease of use.
• Use Agent Mode prompts to further customize their appearance and sizing.

In legal dashboards, slicers and timelines provide a powerful way to explore multidimensional data, enabling attorneys and staff to uncover trends and outliers with just a few clicks, rather than wading through multiple reports or spreadsheets.

Practical Tips for Chart and PivotTable Creation in Legal Workflows

While Copilot’s Agent Mode greatly simplifies chart and PivotTable generation, applying best practices will ensure your visualizations are both accurate and impactful in legal settings. Here are five practical tips to enhance your data presentations:

1. Always Use Excel Tables for Source Data: Formatting data ranges as Excel Tables (Ctrl+T) is critical because Copilot relies on structured, named data sets for accurate references. Tables also auto-expand as data grows, keeping charts and PivotTables up to date.
2. Be Specific and Detailed in Prompts: The more detailed your natural language prompt, the better the output. Include chart types, colors, labels, and layout preferences in a single prompt to minimize revisions.
3. Leverage Agent Mode’s Preview and Approve Workflow: Always review the suggested chart or PivotTable before approving. This step helps catch any misinterpretations of prompts and ensures data accuracy, which is paramount in legal reporting.
4. Use Consistent Color Coding Across Visuals: For example, use the same color for a particular attorney or case type across all charts and tables. This consistency improves readability and client comprehension.
5. Incorporate Slicers and Timelines for User Interaction: Adding interactive filters allows end-users to explore the data themselves, fostering greater engagement and deeper insights without requiring technical expertise.

Applying these tips will help legal professionals produce polished, insightful charts and PivotTables that support better decision-making, client communication, and firm management.

Additional Blockquote Examples of Copilot Prompts for Charts and PivotTables

“Create a bar chart displaying the number of discovery documents by case, with green bars and data labels inside each bar.”

“Generate a PivotTable summarizing total settlement amounts by year and practice area, and then create a line chart based on this PivotTable.”

“Produce an interactive dashboard featuring a column chart for monthly billable hours, a pie chart for case type distribution, and add slicers for attorney and case status.”

Conclusion

Mastering the creation of charts and PivotTables with Copilot’s Agent Mode elevates the ability of legal professionals to analyze and communicate complex datasets efficiently. By leveraging natural language prompts, attorneys and paralegals can quickly generate fully customized charts, build dynamic PivotTables, and combine these elements into insightful, interactive dashboards. The Preview and Approve workflow ensures accuracy and reduces manual effort, while features like slicers and timeline filters empower users to explore data from multiple angles. Adhering to best practices such as using Excel Tables and crafting detailed prompts maximizes the benefits of Copilot in legal data visualization. This intermediate skill set is essential for modern law firms looking to enhance data-driven decision-making, streamline reporting, and provide superior client service.