the report in excel

• Is a New iPad Pro Coming Soon?
• Get It Now: Spring Tech Deals at Amazon

How to Create a Report in Excel

Using charts, graphs, and pivot tables makes it easy

• University of Maine

In This Article

Jump to a Section

Creating Basic Charts and Tables for an Excel Report

Using pivottables to generate a report from an excel spreadsheet, how to print your excel report.

• Frequently Asked Questions

What to Know

• Create a report using charts: Select Insert > Recommended Charts , then choose the one you want to add to the report sheet.
• Create a report with pivot tables: Select  Insert > PivotTable . Select the data range you want to analyze in the Table/Range field.
• Print: Go to File  >  Print , change the orientation to Landscape , scaling to  Fit All Columns on One Page , and select Print Entire Workbook .

This article explains how to create a report in Microsoft Excel using key skills like creating basic charts and tables, creating pivot tables , and printing the report. The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Creating reports usually means collecting information and presenting it all in a single sheet that serves as the report sheet for all of the information. These report sheets should be formatted in a way that's easy to print as well.

One of the most common tools people use in Excel to create reports is the chart and table tools . To create a chart in an Excel report sheet:

Select Insert from the menu, and in the charts group, select the type of chart you want to add to the report sheet.

In the Chart Design menu, in the Data group, select Select Data .

Select the sheet with the data and select all cells containing the data you want to chart (include headers).

The chart will update in your report sheet with the data. The headers will be used to populate the labels in the two axis.

Repeat the above steps to create new charts and graphs that appropriately represent the data you want to show in your report. When you need to create a new report, you can just paste the new data into the data sheets, and the charts and graphs update automatically.

There are different ways to lay out a report using Excel. You can include graphs and charts on the same page as tabular (numeric) data, or you can create multiple sheets so visual reporting is on one sheet, tabular data is on another sheet, and so on.

Pivot tables are another powerful tool for creating reports in Excel. Pivot tables help with digging more deeply into data.

Select the sheet with the data you want to analyze. Select Insert > PivotTable .

In the Create PivotTable dialogue, in the Table/Range field, select the range of data you want to analyze. In the Location field, select the first cell of the worksheet where you want the analysis to go. Select OK to finish.

This will launch the pivot table creation process in the new sheet. In the PivotTable Fields area, the first field you select will be the reference field.

In this example, this pivot table will show website traffic information by month. So, first, you'd select Month .

Next, drag the data fields you want to show data for into the values area of the PivotTable fields pane. You'll see the data imported from the source sheet into your pivot table.

The pivot table collates all of the data for multiple items by adding them (by default). In this example, you can see which months had the most page views. If you want a different analysis, just select the drop-down arrow next to the item in the Values pane, then select Value Field Settings .

In the Value Field Settings dialog box, change the calculation type to whichever you prefer.

This will update the data in the pivot table accordingly. Using this approach, you can perform any analysis you like on source data, and create pivot charts that display the information in your report in the way you need.

You can generate a printed report from all the sheets you created, but first you need to add page headers .

Select Insert > Text > Header & Footer .

Type the title for the report page, then format it to use larger than normal text. Repeat this process for each report sheet you plan to print.

Next, hide the sheets you don't want included in the report. To do this, right-click the sheet tab and select Hide .

To print your report, select File > Print . Change orientation to Landscape , and scaling to Fit All Columns on One Page .

Select Print Entire Workbook . Now when you print your report, only the report sheets you created will print as individual pages.

You can either print your report out on paper, or print it as a PDF and send it out as an email attachment.

Open an Excel spreadsheet, turn off gridlines , and enter your basic expense report information, such as a title, time period, and employee name. Add data columns for Date and Description , and then add columns for expense specifics, such as Hotel , Meals , and Phone . Enter your information and create an Excel table .

To use Excel's scenario manager function, select the cells with the information you're exploring, and then go to the ribbon and select Data . Select What-If Analysis > Scenario Manager . In the Scenario Manager dialog box, select Add . Name the scenario and change your data to see various outcomes.

In Salesforce, go to Reports and find the report you want to export. Select Export and choose an export view ( Formatted Report or Details Only ). Formatted Report will export in .xlsx format, while Details Only gives you other choices. Select Export when ready.

Get the Latest Tech News Delivered Every Day

• The 12 Best Tips for Using Excel for Android in 2024
• How to Create a Dashboard in Excel
• The 5 Best Spreadsheet Apps for Android in 2024
• How to Create a Column Chart in Excel
• Organize and Find Data With Excel Pivot Tables
• How to Create a Pivot Table in Google Sheets
• Power Pivot For Excel: What It Is and How to Use It
• How to Make and Format a Column Chart in Excel
• How to Create a Graph in Excel for iPad
• How to Create an 8 Column Chart in Excel
• How to Make a Calendar In Excel
• How to Create a Histogram in Excel for Windows or Mac
• 5 Cool Things You Can Do With Power Pivot for Excel
• How to Print Labels From Excel
• How to Create a Scatter Plot in Excel
• Plot Area on Spreadsheets

Designing Impressive Reports with Excel

In this article, we're going to look at how to build complex and thorough reports in Microsoft Excel

Ah, the tidy, simple grid of an Excel worksheet. You can’t beat it for organizing your information, whether it’s information about a new product, complex marketing statistics, or detailed scientific data. Whatever text and numbers you need to store and correlate, Excel’s your vessel. 

Of course, when you’re expected to share that information – to report on it – for others to use or evaluate the data, the simple, tidy grid may not be “impressive” enough. If you’re building a report for distribution to shareholders, clients, or for use as a sales tool, you’ll want a more polished, professional look, and you may not want to see the grid at all. You’ll also want to include charts and diagrams, if necessary, to drive home the major takeaways from your data, and you’ll want to position them alongside the data they pertain to. You might even want to include graphics – logos and photos – the create a truly professional-looking, compelling report. 

The good news? It’s easy to achieve a polished, impressive appearance for your Excel worksheet. All of the tools you need to do so are right there within Excel. And it’s just as easy to save and share your report in a format that makes the report’s origins in Excel invisible to the viewer. 

What Does “Impressive” Mean, Anyway? 

First, let’s define the goal. What would make an Excel worksheet-based report look “impressive”? Here’s a list of common features one would expect in a report:

• No visible grid, unless it’s helpful for reading across several columns of data
• Colored backgrounds, including white, to hide the grid and delineate areas of the data 
• Charts and diagrams appearing next to related data
• Clear, legible titles that make sections of the data stand out from each other
• Appropriate number formatting for accounting, currency, and statistical data
• Subtle use of shapes and lines to define areas, highlight information, or explain/instruct the viewer
• Photos, logos, and other graphics to associate brand elements and other images with the data

Combined, these attributes will enable you to export your worksheet – or an established print area within it – as a PDF for sharing with anyone who needs to see, understand, and be persuaded by your data. 

First Things First

Of course, you don’t want to do any of this formatting – other than, possibly, the number formatting – before you’re finished building the worksheet itself. I know I like to have my columns of currency values formatted as such before I even start entering numbers into the cells in question, so number formatting is likely to be in place in my worksheets, long before I start any formatting for a report. But with or without that sort of formatting in place from the start, you’ll want to apply the “impressive” report formatting in this order, starting with the completed worksheet, i.e., all of your data, in place:

• Titles created – including merging and centering major titles
• Formatting titles and section names
• Sizing and formatting text within your data
• Applying number formatting
• Applying alignment – both vertical and horizontal – within your columns and rows
• Wrapping long titles and cell values where necessary
• Background colors, applied to ranges
• Borders applied to cells within colored backgrounds, if needed, for to help viewers read across rows of numbers when data spans across more than, say, five columns, or when you want viewers to see the numbers behind a sum, average, or other calculated result

But That’s Not All

Once the formatting is in place, it’s time to start adding the elements that help people understand the data, that draw people’s eyes to key points within the report, and that instruct people in the use of the information included in the report. With your text and numbers in place (the worksheet built) and all of the aforementioned formatting done, now it’s time to:

• Create, size, and position your charts and diagrams
• Insert shapes – with and without text in them – and draw lines and arrows
• Define the print area, so that nothing beyond the data you wish to include in the report turns up in the exported PDF
• Preview the “printed” report
• Make modifications to content, formatting, and placement of floating charts, diagrams, and graphics, as needed
• Export as a PDF

Formatting Titles and Section Headings

For your own sake, it’s a good idea to start with your titles and section headings in terms of formatting. Why? Because that way you can begin to envision the report’s sections for yourself, and that makes all the subsequent formatting easier to map out and apply. For example, if you plan to make all the cells under a given section heading a different color than the cells in an adjacent section, that section heading (and that of its neighboring section) will help you define the range of cells to fill with a different background color. 

I like to merge my titles – the main title across the top of the report and the titles above various sections – so that they’re living in a single cell that spans the width of the report and/or section. As shown in this image, a section that spans four columns is headed by a title that’s in a cell merged from four cells across that span. 

The process of applying merge and center – and formatting titles – is simple, and you can learn more about it in this video . 

https://www.youtube.com/watch?v=Zg0w_fy76uA&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=53

Formatting Text and Numeric Data

The formatting you apply to your text and numbers in the report is dependent upon the nature of that content. Applying bold and italic formatting is simple enough, using the Bold and Italic buttons in the Font group on the Home tab in your worksheet – but specialized formatting, which includes changing the size, font, and color is covered in this video, at the following link:

https://www.youtube.com/watch?v=tWBjWamK9OE&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=56

Formatting your numbers – as currency, accounting values, percentages, etc… is also easy, via the Number group of tools on the Home tab, and you can also learn more in this video. 

https://www.youtube.com/watch?v=XnOc07hag2Y&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=55

You may also be referring to various dates and times in your report, and there are simple formats to choose from in the Number drop list on the Home tab (also in the Number group of tools), but there are many options you may need that go beyond those basic formats. You can learn more about how to apply Date and Time formatting from this video.

https://www.youtube.com/watch?v=MeIaZui-5nc&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=52

Setting Alignments

Where your content appears within cells in the worksheet can significantly impact how well that content translates to a professional-looking report. As shown in the images here, see how much better a series of titles, especially those wrapped within the cell, look when vertically centered. 

To control both vertical and horizontal alignment – and to indent content within cells – you can use the tools on the Home tab, in the Alignment group. Or, check out this video for all the help you’ll need.

https://www.youtube.com/watch?v=sugRLJZbkyQ&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=50

Applying Colored Fills to Ranges

Putting a solid color behind your ranges of related content is an effective way to create sections in your report – both visually and conceptually – for the audience. And it’s unbelievably easy to do. 

To begin, select the range by dragging through the cells that should have the same background color. If it’s a long or wide range (or both!), you can click in the first cell in the upper left of the desired range, and then scroll until you can see the last cell you want to fill with color. Press and hold the Shift key, and then click in that last cell in the desired range, and voila, the entire range is selected. 

Next, with your selection made, go to the Home tab’s Font group – oddly enough – and click the Fill Color drop list, as shown in the image here. 

From the resulting menu, you can choose from Theme , Standard , and/or Recent Colors, or click More Colors for a dialog box (shown here) that gives you two different ways to choose a color by clicking the one you want with your mouse, or you can enter a hexadecimal color if you prefer. 

Whichever color you choose, however you choose it, that’s now the colored fill within the cells in your selected range. Bear in mind this covers up the gridlines – so if this particular range is one that really would benefit from seeing the lines that help a person read from left to right in a row or from top to bottom in a longer color, you can restore them by using the Borders button, and choosing All Borders. Be sure the selection is in place before making this choice. 

Using Borders for Legibility and Delineation

Speaking of borders, you’ll also find them helpful in underlining column headings, boxing key cells or ranges, and as stated previously, to restore the visible table grid when you’ve applied a colored fill.

My favorites are the thick borders – Thick Outside Border and Thick Bottom Border , because they really stand out. To apply them, just select a range and then click the Borders button on the Home tab and make your choice. 

Telling Your Story with Charts & Diagrams

Complex data is always easier to absorb and understand if it’s shared in a way that makes it easy to get the major takeaways “at a glance” – and nothing achieves that more efficiently than a chart or diagram. 

Charts shouldn’t be overly-complex – it’s better to have several separate charts that each visually explain or reveal a single set of related statistics than to have one or two inscrutable charts with lines, columns, bars, etc… in a misguided attempt to consolidate the message. And charts should also have clear main titles and axis titles, so it’s obvious, right off the bat, what’s being shown. 

To learn more about creating any kind of chart from your data, check out this video:

https://www.youtube.com/watch?v=NFAa8lPY50c&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=8

And to make sure your chart is an attractive, cohesive part of your report – in terms of colors used, fonts, and so forth, you’ll want to watch this video on good editing and formatting techniques:

https://www.youtube.com/watch?v=xx4QtLnUW4U&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=6

Now, if your report also requires explaining relationships between people, departments, products, or any other concept that would benefit from a picture rather than an assumed understanding, diagrams help, too. Here’s a video on using Excel’s SmartArt to show processes, connections, hierarchies, and other concepts, through illuminating, colorful, and eye-catching diagrams:

https://www.youtube.com/watch?v=NqpvF6dPaNE&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=7

Using Shapes, Lines, and Arrows to Control Viewer Focus

While most worksheets that are set up to serve as reports are already laid out so that people can easily spot key information, the addition of shapes, lines, and arrows (which are just another kind of shape) are helpful additions. In the image shown here, an oval is used as a text box, with a key total showing inside it, and icons have been added, too – showing a series of building types that the insurance company in question covers.

Adding shapes – be they polygons, lines, or arrows – is easy, utilizing the Illustrations button on the Insert tab. From there, choose Shapes and then choose the one you want to draw from the drop list as shown here. Learn the full process of inserting, resizing, and formatting your shapes – including adding text to them, so they serve as a way to highlight important data, give instructions, or background info to the viewer – with these videos:

https://www.youtube.com/watch?v=72XhCmKqZ_4&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=5

With these added features in place, your report takes another major step away from simplistic grid packed with text and numbers toward professional-looking report!

A Picture’s Worth…a Lot

Photos aren’t used quite as often as charts, diagrams, and shapes in a worksheet, but for reports, they can play a useful role. Maybe your report, on the status of new product that’s going into production, would benefit from a photo of the product, or an aspect of its assembly. Maybe a photo of a building’s blueprint, the team responsible for a major accomplishment, or from an event, the registration data you’re reporting on, would help the report seem more “real” to its viewers. 

To add a photo or other image to your report:

• Click the Illustrations button again on the Insert tab.
• Choose  Pictures , and then select where those pictures are – on your device or online. If you have an account with a stock images service (Shutterstock, for example), you can choose that, instead.
• Note that the This Device will include any network or cloud-based drives to which you have access, not just your hard drive.
• Select the image you want – the exact process will vary depending on where your image is – and it appears in the worksheet, at which point you can reposition and resize it as desired. 

Sharing Your Masterpiece

There’s no point in creating a report and going to all the trouble of making it pretty if you’re not going to share it, right? 

Sharing all starts with the File tab, where you can choose the form in which you’ll disseminate your report. You can, if you’re going to go “old school,” opt to print the report to paper. The process is pretty simple, but there are many options – so you’ll want to watch this video that explains everything you can control about what’s included in your printout, how many pages it prints out on, and more:

https://www.youtube.com/watch?v=BU05l2YMhek&list=PL_eVGUXO7Anha4lv9dp_Nk96KP1WuBpKi&index=70

Saving or Exporting as a PDF

If you want to send your report as a PDF – your best bet, as everyone has the ability to view PDF files, even if they don’t have a copy of Adobe Acrobat – just choose Export from the File tab, and then click the Create PDF/XPS document button. 

If you have Adobe Acrobat, you’ll also see the option to Create Adobe PDF.

After making the choice that’s right for you, you’ll be able to name and choose a location to save your PDF, as you would any other file in any Save operation. 

Attach it to an email or share it via a link to its location via Teams – it’s up to you!

How to Learn Excel

Master Excel with hands-on training. Excel is the leading spreadsheet application used by over 750 million people worldwide.

• Excel Bootcamp at Noble Desktop : 18-hour live, instructor-led course available in NYC or live online
• Find Excel Classes Near You : Search & compare dozens of available courses in-person
• Attend an  Excel class live online  (remote/virtual training) from anywhere
• Find & compare the best online Excel classes (on-demand) from the top providers and platforms
• Train your staff with  corporate and onsite Excel training