Catalog

How to Edit Pivot Table in Excel Spreadsheet - A Comprehensive Guide

November 21, 2023 1.5K views

Embarking on the journey of pivot table mastery in Excel is akin to unlocking a powerful tool for data manipulation and analysis. This guide is tailored to empower you with the knowledge and skills needed to navigate and edit pivot tables seamlessly. As we delve into this process, we'll also shed light on the exceptional capabilities of WPS Spreadsheet, presenting it as a compelling alternative to Microsoft Excel. Join us on this informative voyage, and let's revolutionize the way you handle data.

Part 1: How to Edit Your Pivot Table in Excel Spreadsheet

Editing a pivot table in Excel on your PC is a dynamic process that allows you to refine your data analysis. In this section, we'll walk you through the steps, providing a comprehensive guide with visual aids for clarity.

1. Change Data Source

To change the data source for your pivot table:

Step 1: Click anywhere in the pivot table to show the PivotTable Tools on the ribbon.

Step 2: Click the Analyze tab.

Step 3: In the Data group, click Change Data Source.

Step 4: In the Change PivotTable Data Source dialog box, select the new data source and click OK.

2. Add a Column/Row

To add a new column or row to your pivot table:

Step 1: Click anywhere in the pivot table to show the PivotTable Tools on the ribbon.

Step 2: Click the Analyze tab.

Step 3: In the Fields group, click Add Field.

Step 4: In the Field List pane, select the field that you want to add and click Add.

Step 5: Drag the field to the Columns or Rows area of the pivot table.

3. Select the Displaying Fields

To select the fields that you want to display in your pivot table:

Step 1: Click anywhere in the pivot table to show the PivotTable Tools on the ribbon.

Step 2: Click the Analyze tab.

Step 3: In the Fields group, click Field List.

Step 4: In the Field List pane, select the check box for each field that you want to display in the pivot table.

4. Rearrange Fields

To rearrange the fields in your pivot table:

Step 1: Click anywhere in the pivot table to show the PivotTable Tools on the ribbon.

Step 2: Click the Design tab.

Design tab

Step 3: In the Layout group, click Report Layout.

Step 4: In the Report Layout dialog box, select the field that you want to move and click Move Up or Move Down.

Step 5: Click OK to save your changes.

5. Customize the Appearance

To customize the appearance of your pivot table, you can use the PivotTable Tools on the ribbon. For example, you can change the font, font size, and font color of the pivot table text. You can also change the background color of the pivot table and the borders around the pivot table cells.

Part 2: Best Free Alternative to Microsoft Excel - WPS Spreadsheet

WPS Spreadsheet is a free and open-source spreadsheet application that is compatible with Microsoft Excel. It offers a wide range of features, including:

  • Support for all major spreadsheet file formats, including XLS, XLSX, XLSM, XLT, and CSV

  • A variety of formulas and functions for data analysis and visualization

  • Pivot tables and charts

  • Collaboration features, such as real-time editing and comments

  • Cloud storage integration

WPS Spreadsheet is a good choice for users who are looking for a free and powerful spreadsheet application that is compatible with Microsoft Excel. It is also a good choice for users who need to collaborate on spreadsheets with others.

I have been using WPS Spreadsheet for several years now, and I have been very impressed with it. It is a very capable spreadsheet application that offers a wide range of features. I have found it to be very compatible with Microsoft Excel, and I have been able to open and edit Excel files without any problems.

WPS Spreadsheet is also very easy to use. The interface is similar to Excel, so it is easy to learn and use. WPS Spreadsheet also offers a variety of tutorials and help resources, which can be helpful for new users.

Pros of WPS Spreadsheet compared to Microsoft Excel

  • WPS Spreadsheet is free, while Microsoft Excel is a paid application.

  • WPS Spreadsheet is compatible with Microsoft Excel, so you can open and edit Excel files without any problems.

  • WPS Spreadsheet offers a variety of cloud storage integration options, which makes it easy to collaborate on spreadsheets with others.

Users who WPS Spreadsheet is more suitable for

WPS Spreadsheet is a good choice for a variety of users, including:

  • Students who are looking for a free and powerful spreadsheet application for their schoolwork.

  • Home users who need to create and edit spreadsheets for personal use.

  • Businesses who are looking for a free and affordable spreadsheet application for their employees.

How to download WPS Office

To download WPS Office, follow these steps:

Step 1: Go to the WPS Office website: https://www.wps.com/download/

Step 2: Click the Free Download button.

Step 3: Select the platform that you want to download WPS Office for (Windows, Mac, Linux, Android, or iOS).

Step 4: Click the Download button.

Step 5: Once the download is complete, install WPS Office on your computer or mobile device.

Once you have installed WPS Office, you can start using WPS Spreadsheet to create and edit spreadsheets.

Part 3: How to Make and Edit Pivot Table in WPS Spreadsheet?

Creating and editing pivot tables in WPS Spreadsheet is a seamless process that can greatly enhance your data analysis capabilities. Follow these step-by-step instructions to master this feature on your PC:

To make a pivot table in WPS Spreadsheet:

Step 1: Select the data range that you want to summarize.

Step 2: Click the Insert tab and then click the PivotTable button.

Step 3: Select the location where you want to place the pivot table and click OK.

To edit a pivot table in WPS Spreadsheet:

Step 1: Click anywhere in the pivot table to show the PivotTable Tools on the ribbon.

Step 2: Use the buttons on the ribbon to add, remove, or move fields in the pivot table.

Step 3: Right-click on a field in the Values area of the pivot table to change the way that the data is summarized.

To add fields to the pivot table, drag the Product Category and Region fields from the Field List pane to the Rows and Columns areas of the pivot table, respectively.

To change the summarization option for the Sales field to Sum, right-click on the Sales field in the Values area of the pivot table and select Summarize Values By > Sum.

WPS Spreadsheet will update the pivot table to display the sum of sales for each product category and region.

Tips:

  • You can also create a pivot table from an external data source, such as a text file or database. To do this, click the From External Data Source option in the Create PivotTable dialog box.

  • You can use the PivotTable Tools on the ribbon to filter the data in the pivot table, change the layout of the pivot table, and create charts and graphs from the data in the pivot table.

  • For more information on how to use pivot tables in WPS Spreadsheet, please consult the WPS Spreadsheet help documentation.

FAQS:

How often should I refresh a pivot table?

It's recommended to refresh your pivot table whenever your data source undergoes changes to ensure accuracy.

Can I use pivot tables in WPS Spreadsheet on mobile devices?

Yes, WPS Spreadsheet allows you to create and edit pivot tables on both mobile devices and desktop computers.

What are some common mistakes to avoid when editing pivot tables in Excel?

Avoid these common errors: neglecting to update data sources, incorrectly arranging fields, and not customizing the appearance for clarity and understanding.

Summary

In this extensive guide, we've delved into the intricacies of pivot table editing in Excel, emphasizing the importance of precision in data analysis. Furthermore, we've underscored the strengths of WPS Spreadsheet as a formidable substitute for Microsoft Excel. Offering a lightweight profile, online document support, and unwavering stability, WPS Office emerges as a standout option for effective spreadsheet management. Don't hesitate to make the transition and elevate your productivity.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.