You can configure this spreadsheet template to meet the needs of your company. Internet Expenses provides a Microsoft Excel expense spreadsheet template to create a disconnected expense report. The disconnected expense reporting process involves entering expenses in a Microsoft Excel spreadsheet on a personal computer or laptop and then importing this spreadsheet into Internet Expenses. Testing your Custom Microsoft Excel TemplateĮmployees who are travelling or do not have access to the company's intranet can create expense reports using the disconnected expense reporting process.Setting Up Descriptive Flexfields in the Microsoft Excel Template.Configuring the Microsoft Excel Template.This chapter covers the following topics: Up next: Sort, filter, summarize, and calculate your PivotTable data.10/18 Configuring the Expenses Spreadsheet We’ll cover the ANALYZE tab in subsequent videos. When you mouse over them, you get a preview of what the PivotTable will look like.Ĭlick the style you want to change the look of the PivotTable. Under PIVOTTABLE TOOLS, click the DESIGN tab to change how the PivotTable looks.įor example, click Report Layout, click Show in Outline Form, and now Genre and Date are in separate columns.Ĭheck Banded Rows to make it easier to read across the rows of the PivotTable.Ĭlick the down-arrow next to PivotTable Styles there are many options. When you click outside of the table, they go away. When you click any cell in a PivotTable, the PivotTable Fields List and the PIVOTTABLE TOOLS tab appear. And we can see the sales of the genres over time for each store. The dates are grouped under the genres by month, much easier to work with.

I'll use the default, Months, but you can select one or more options, such as Quarters and Years. But this many rows of dates makes the PivotTable hard to use.

I check the Date field and it’s added to the ROWS area. I click and drag the Store field to COLUMNS, and we can see the sales of genres for each store along with grand totals.Īn important factor we don’t have in the PivotTable yet are dates. I don’t want any digits after the decimal place, so I set the Decimal places to 0, click OK, and we can see the total sales for the different genres of books. Right-click a cell in the Sum of Sales Amount column, click Number Format, and click Currency. I check the Genre field and it’s added to the PivotTable as rows of text labels.īy default, text fields are added as rows and numbers as values.Ĭheck the Sales Amount field and it’s added as a column of values that are added using the SUM function. They are the same as the column headings in the source data.Īt the bottom are the four areas of a PivotTable the fields can be added to: ROWS, COLUMNS, VALUES, and FILTERS. In the PivotTable Fields List, at the top, are the fields we’ll use to create the PivotTable. In our example, a new worksheet with an empty PivotTable is created. If you want, you can create it on an existing worksheet instead, by clicking Existing Worksheet and providing the Location. We recommend using a table because, if the table grows, the PivotTable will automatically include the new data when you refresh the PivotTable.įor information about creating a table, see the link Create or delete an Excel table in a worksheet in the course summary.īy default, the PivotTable will be created on a New Worksheet. In this example, the entire SourceData table. All of the source data is automatically selected. There’s a link to both articles in the course summary at the end of the course.Ĭlick any cell in the data. It doesn’t have to be in a table, a range of cells can be used as well.įor information about using external data, see the article Create a PivotTable to analyze external data.įor information about using multiple database tables, see Create a PivotTable to analyze data in multiple tables. The headings are used to name the fields in the PivotTable.Įach column contains the same type of data, for example, text in one column and currency in another, and there should be no blank rows or columns.įor this PivotTable, we’ll use source data that is in a table. It has a column for the Genre of the books, the Date they were sold, the Sales Amount, and the Store where they were sold.īefore you create a PivotTable, the data you are going to use (referred to as Source Data) should be arranged correctly.Īll the columns should have headings. This table contains data about book sales.

Microsoft excel expense report template