How to Make a Sales Report in Excel

Published on
March 1, 2021

Creating a good-looking and accurate sales report in Excel can really be a grind. First there’s collecting data, then putting it into the spreadsheet. You must make the report look professional as well, using formulas to extract insights about customers or generate forecasts of incoming revenue.

Don’t worry—you’re not alone if this is a familiar challenge. One in five organizations are believed to use Excel spreadsheets as the only means of communicating internal data. Other businesses use enterprise resource planning (ERP) systems or business intelligence platforms to make sales reporting visible in real-time. But almost 100% of the world’s businesses still use Excel in some capacity, especially to slice numbers outside of systems that serve as a single source of truth.

Now that we have a little bit of context for the circumstances and challenges of creating a sales report in Excel, let’s take a deeper dive into how exactly to create one, and the Excel reporting add-on that can take yours to the next level.

How to Make a Sales Report in Excel

To begin making your sales report in Excel, you need to put sales information into the spreadsheet. Leave cell A1 blank and make a list in the A column starting in cell A2. Sales managers might list each seller on the team and prepare to report on their deals closed or dollars won. Sellers creating their own reports might list their customers and active leads in the first column and prepare to enter the value or anticipated value of each contract.

The data you put in, and how it is structured, depends on the insights you want to report on. There are many types of sales reports sellers and leadership may need to create, including the following types of sales reports:

  • Revenue by seller
  • Average sales cycle length
  • Loss rate/win rate by sales stage
  • Sales call report
  • Average deal size

But the goal of the sales report is just one consideration. There’s also the scope of the report. Is the data from within the last quarter, month, week, or even day? Here’s how the timeframe you are reporting on can impact how you create a sales report.

How to Create a Monthly Sales Report in Excel

Monthly sales reports allow sellers to update their teams on deals closed and forecast what is coming in the month ahead. Creating a monthly sales report requires tracking the costs associated with closing a deal to describe the revenue the contract will generate, not just its value. Monthly reporting is also often connected back to annual reporting, showing the quarterly and annual goals and how close each seller is to meeting or exceeding them. This is part of what makes generating these reports tricky at times, because goals or targets might change but never get updated in a spreadsheet.

How to Create a Weekly Sales Report in Excel

Weekly sales reports often dial down into tracking the daily activities of the seller and how they impacted the pipeline. That means keeping track of phone calls made, emails sent, and meetings taken. At many organizations, sellers track time spent on existing clients and prospective clients separately, reporting weekly on each. This gives transparency into the work being done and how revenue is growing, whether that’s upselling or selling to new clients. Expenses like meals, rooms, and mileage are also often included in weekly Excel sales reports to show the net gain achieved that week by the seller.

How to Make Daily Sales Report in Excel

Making a daily sales report helps sellers stay focused on their progress toward monthly goals and quotas. Knowing the overall mission, sellers work backwards to figure out a daily average value they need to be closing, and then can report on if they have achieved that or not. Tracking daily sales against the total monthly target can help sellers notice early on if they are going to need support. These reports should be simple, since they are filled out at the end of the day, and many sellers may simply turn to their customer relationship management (CRM) tool for these granular insights.

The reason many sellers defer to the CRM for daily sales reporting instead of creating an Excel report is simple—no one likes to have to type the same data twice, and many organizations require that the CRM be updated.

But then, later, when work does need to be done in Excel for monthly or quarterly reporting, that means spreadsheets need significant rework and formulas quickly fall apart. All this makes it far too easy for the importance of the sales report to be overshadowed by the tedious process to create it.

Level Up Excel with XLCubed

XLCubed is a custom lens into all your organization’s data through the Excel platform. XLCubed connects Windows Excel to outside data sources, meaning no more concerns about a report breaking or being out of date. This allows businesses to achieve significantly more impactful reporting using the Excel platform they are already familiar with. Once sellers have harnessed the power of data-connected Excel, they can do even more with their data. XLCubed lets you dial into the full story of which seller behaviors are most effective and which clients are most profitable. Plus, XLCubed makes translating these insights into beautiful views and dashboards as easy as a simple click. 

Financial, operational, and other company-wide reporting are all easier when Excel is combined with XLCubed. Explore our platform today and see the potential for yourself. 

Amber Peckham
Fluence Technologies

Connect with Fluence

Discover how Fluence can help your organization plan better and close faster with more confidence.