If the data is in ranges, simply create tables from them using CTRL+T or Insert > Table option. To create pivot reports from multiple tables, This technique opens up doors for advanced data analysis with ease. What if you need to create a pivot from data that is in two or more tables? You can use “Data Model” feature of Excel to connect multiple tables and create pivots from them. You can use the same approach, with a few extra steps, described here.We can all create pivot reports from single tables or ranges of data. Note: the same problem can occur with dates are grouped as months, and no data appears in a given month. To force the pivot table to display zero when items have no data, a zero is entered in general pivot table options:įinally, the Accounting number format has been applied to the Sales field to display empty cells with a dash (-). To force the display of items with no data, 'Show items with no data' has been enabled on the Layout & Print tab of the Color field settings, as seen below: Region has been configured as a Row field, Color as a Column field, and Sales is a Value field.ĭata has been filtered by Region to exclude East:

The pivot table shown is based on three fields: Region, Color, and Sales: Normally the Blue column would disappear, because there are no entries for Blue in the North or West regions. However, Blue remains visible because field settings for color have been set to 'show items with no data', as explained below. In the example shown, a filter has been applied to exclude the East region. This is because pivot tables, by default, display only items that contain data.

When a filter is applied to a Pivot Table, you may see rows or columns disappear.

Excel 2013 create pivot table options