How to Create a Law Firm Dashboard in Excel

A law firm dashboard in Excel gives you a clear, data-driven view of your practice's performance, from financial health to client acquisition. It transforms scattered information into a single screen of actionable insights. This guide will walk you through the essential KPIs to track and provide step-by-step instructions for building your dynamic dashboard.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Your Firm Needs a Dashboard

Most law firms operate on a combination of professional experience, gut feelings, and hurried quarterly reviews. While useful, this approach leaves opportunities on the table. A dashboard offers several concrete advantages:

• Visibility: It brings all your key metrics together, providing a real-time health check on your firm's performance without digging through multiple reports.
• Proactive Decisions: Instead of reacting to last quarter’s problems, you can spot trends as they happen. Is one practice area suddenly less profitable? Are client acquisition costs creeping up? You'll see it immediately.
• Accountability: When metrics like billable hours and case progress are transparent, it encourages everyone to stay focused on what drives the firm forward.
• Improved Profitability: By highlighting your most profitable clients, practice areas, and efficient attorneys, a dashboard helps you focus resources where they'll have the biggest impact.

Choosing the Right KPIs for Your Law Firm

Before you open Excel, you need to decide what to measure. The right Key Performance Indicators (KPIs) act as the vital signs for your firm. Here are some of the most critical KPIs, separated into a few key areas.

1. Financial Health

These metrics focus on the core profitability and cash flow of your practice.

• Revenue by Practice Area: Shows which parts of your practice are generating the most income.
• Profitability & Profit Margin: Revenue is great, but profit is what matters. This helps you understand how much you keep after expenses.
• Work in Progress (WIP): The value of unbilled time and expenses. A high WIP can signal issues with billing cycles.
• Billed vs. Non-Billed Hours: A classic efficiency metric. How much of your team's time is directly generating revenue?
• Realization Rate: The percentage of billed hours that are ultimately collected from clients. A low realization rate can indicate billing disputes, unsatisfied clients, or inefficient collection processes. It's calculated as (Total Collected / Total Billed) * 100%.
• Collection Rate: This measures how successful you are at collecting what you invoice. A low rate points directly to cash flow problems. It's calculated as (Amount Collected / Amount Invoiced) * 100%.

2. Client & Case Management

These KPIs provide insight into your client pipeline and caseload management.

• Number of New Matters/Cases: Your firm's growth engine. You can track this monthly to see your business development trends.
• Client Acquisition Cost (CAC): How much does it cost, on average, to acquire a new client? Calculate this by dividing your total marketing and sales-related expenses by the number of new clients in a given period.
• Case Load per Attorney: A crucial metric for managing bandwidth and preventing burnout. It helps you distribute work more evenly and identify who might need support.
• Cases Won vs. Lost: A straightforward measure of your firm’s success rate.

3. Operational Efficiency

These numbers highlight how effectively your firm is operating.

• Average Case Duration: How long does it take, on average, to close a case from start to finish? Tracking this by practice area can reveal process bottlenecks.
• Billable Hours per Attorney: Measures individual productivity and helps with setting targets and performance reviews.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Setting Up Your Data in Excel

Your dashboard is only as good as the data it sits on. The most important step is organizing your information in a clean, structured table. Messy data will make your life much harder later.

Create a new Excel file with two sheets. Rename one Dashboard and the other Data.

On your Data sheet, set up your columns. Your goal is a simple, flat table - one row per recorded item (like a time entry or an invoice). Don’t use merged cells, fancy colors, or multiple levels of headers.

Here’s a good starting structure for your main data table:

Once you've set up your columns and pasted or entered your data, select any cell inside your data range and press Ctrl + T (or Cmd + T on Mac). This converts your range into an official Excel Table. Make sure the "My table has headers" box is checked. Doing this is crucial because Tables automatically expand as you add new rows, ensuring your dashboard components always include the latest information.

Step-by-Step: Building Your Law Firm Dashboard

With your data organized, you’re ready to build the fun part. We will use PivotTables as the engine for our dashboard because they are powerful, flexible, and update easily.

Step 1: Create a Central PivotTable

A PivotTable will summarize your raw data, making it easy to calculate KPIs and create charts.

1. Click anywhere inside your Excel Table on the Data sheet.
2. Go to the Insert tab on the Ribbon and click PivotTable.
3. Excel will automatically select your table. In the dialog box, choose to place the PivotTable on a New Worksheet. Let's rename this new worksheet Calculations.

You now have a blank PivotTable. This will serve as the engine for all your charts and numbers.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Calculate Your Core KPIs

Your Dashboard sheet should display your most important KPIs as large, easy-to-read numbers.

First, let’s get the raw numbers from our PivotTable. On your Calculations page:

• Drag Invoice Amount into the Values area of the PivotTable Fields pane.
• Drag Amount Collected into the Values area.
• Drag Billed Hours into the Values area as well.

Now, go to your Dashboard sheet. Designate a few cells at the top for your KPI cards. For example:

Total Revenue

In a cell on your dashboard, type "=" and then navigate to the Calculations sheet and click on the cell containing the Sum of Invoice Amount in your PivotTable. Press Enter. This links the cell directly to the PivotTable. Repeat this process for Total Amount Collected and Total Billed Hours.

Realization Rate

For calculated metrics like the Realization Rate, you can create a simple formula on your dashboard sheet that references your linked KPI cells. If your Sum of Amount Collected is in B2 and Sum of Invoice Amount is in B3, the formula is:

=B2/B3

Format this cell as a percentage. Style these KPI cells with a larger font and bold text to make them stand out.

Step 3: Create Visuals with PivotCharts

Charts bring your numbers to life. We’ll create a couple of charts directly from our main PivotTable.

Revenue by Practice Area (Bar Chart)

1. Go back to your Calculations sheet and click on your PivotTable.
2. In the PivotTable Fields pane, drag Practice Area into the Rows section and Invoice Amount into the Values section.
3. With the PivotTable selected, go to the PivotTable Analyze tab and click PivotChart.
4. Choose a Clustered Bar or Column chart and click OK.

A new chart will appear. Right-click the chart and select Cut. Then, go to your Dashboard sheet and press Ctrl + V to paste it. You can now resize and position it.

Clean up the chart: Right-click on the gray field buttons (like "Sum of Invoice Amount") and select Hide All Field Buttons on Chart to get a cleaner look.

Billable Hours Over Time (Line Chart)

Repeat the process for another chart. Go back to your PivotTable, remove Practice Area from Rows, and drag your date field (e.g., Date Opened) to Rows. Drag Billed Hours to Values.

Excel will automatically group the dates. You can right-click the dates and choose Group to group them by Months or Quarters. Then, create another PivotChart, this time selecting a Line Chart. Cut and paste this onto your dashboard sheet, too.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Make It Interactive with Slicers

Slicers are the secret sauce that transforms a static report into a dynamic dashboard. They are essentially user-friendly filters.

1. Click on any of your PivotCharts on the dashboard.
2. Go to the PivotChart Analyze tab and click Insert Slicer.
3. A dialog box will appear with all your data fields. Check the boxes for Attorney and Practice Area. Click OK.

Two slicer boxes will appear on your dashboard. Now, when you click on an attorney's name or a specific practice area, all of the charts and KPI numbers linked to your PivotTable will update instantly! To connect a slicer to multiple PivotTables (if you've built charts from different tables), right-click the slicer, choose Report Connections, and check all the PivotTables you want it to control.

Maintaining Your Dashboard for Long-Term Success

Once you’ve built your dashboard, the work isn't completely done. Here are a few tips to keep it valuable:

• Keep Your Data Fresh: Schedule a time each week or month to add your new data to the Data sheet. After adding new rows, simply right-click any PivotChart or PivotTable and select Refresh. Everything will update automatically.
• Keep it Simple: Resist the urge to add too many charts and numbers. A cluttered dashboard is an ignored dashboard. Focus only on the metrics that drive key decisions.
• Iterate and Improve: Your firm’s goals will change. Revisit your dashboard every quarter to ensure the KPIs you're tracking are still the most relevant ones.

Final Thoughts

Building a dashboard in Excel puts the power of data directly in your hands. It allows you to move beyond gut feelings and make strategic, informed decisions about your firm's finances, client management, and operational efficiency, giving you a serious competitive advantage.

Of course, manually updating an Excel file, even one as powerful as this, takes time and discipline. That's a friction we wanted to eliminate. At Graphed, we've built a tool that connects securely to your data sources and automates the entire reporting process. You can just ask in plain English - "show me a dashboard of collected revenue vs. billed revenue by attorney for last quarter" - and instantly have a live, interactive dashboard that keeps itself up-to-date. If you’re ready to spend less time crunching numbers and more time acting on insights, you might love what you can build with Graphed.