Chapter 1 Getting Started with WPS Spreadsheet Basics
【Introduction】
WPS spreadsheet, as an important part of Kingsoft Office suite, provides a powerful and easy-to-operate spreadsheet processing tool for the majority of users. Whether you are a newcomer to the workplace or a senior white-collar worker, as long as you need to organize, analyze, visualize and other operations of data, WPS spreadsheets will be your powerful assistant. In this chapter, we'll take you into the world of WPS spreadsheets, starting with the basics and step-by-step mastering its features and application techniques.
1.1 Understand the basic features and interface of WPS spreadsheets
1.1.1 Understand the basic functions and interface of WPS spreadsheets
As a powerful spreadsheet software, WPS spreadsheet has the characteristics of user-friendliness and efficiency in its basic functions and interface design. After understanding its basic functions and interface, we can make better use of this tool for data processing and analysis.
The interface design of the WPS spreadsheet is simple and straightforward, and users can easily find the functional modules they need.
Figure 11- Basic functionality and interface of a WPS spreadsheet
The main interface includes sections such as menu bar, toolbar, worksheet area, and status bar. The menu bar provides a wealth of command options, such as file manipulation, editing, formatting, and more. The toolbar integrates commonly used function buttons, such as saving, printing, undosing, etc., which is convenient for users to quickly complete common operations. The worksheet area is the main place for users to enter and process data, and supports a variety of data formats and formula operations. The status bar displays the status information of the current worksheet, such as row and column numbers, cell coordinates, etc.
When it comes to data processing, WPS spreadsheets provide powerful functional support. Users can quickly locate and analyze data through filtering and sorting functions, and gain insight into the internal relationships of data through pivot tables and pivot charts. At the same time, WPS spreadsheets also support a variety of functions and formula operations, such as sum, average, maximum, minimum, etc., to help users easily complete data calculation and analysis.
Figure 1-2 Filtering and sorting functions to quickly locate and analyze data
In addition to basic data processing functions, WPS spreadsheets also support diagramming and beautification. Users can create various types of charts, such as column charts, line charts, pie charts, etc., to visualize the data.
Figure 1-3 WPS spreadsheet chart creation and beautification
At the same time, WPS spreadsheets also provide rich chart beautification options, such as adjusting colors, fonts, data labels, etc., to make the charts more beautiful and easy to read.
Table 1-1 WPSPS spreadsheet chart beautification options
| Options | description |
| Adjust the color | The color of the chart can be changed to make it more aesthetically pleasing and legible |
| font | You can change the font in the chart to make it more beautiful and easy to read |
| Data labels | Data labels can be added to make the chart more readable |
In conclusion, understanding the basic features and interface of WPS spreadsheets is the key to mastering this tool. By taking full advantage of the functional support it provides, users can process and analyze data more efficiently.
1.1.2 WPS spreadsheet startup and interface overview
The WPS spreadsheet launch process is simple and efficient, and users can quickly access this powerful data processing tool by simply double-clicking on the icon on the desktop. As the launch animation ends, users will be presented with a clear, intuitive interface that is fully prepared for the work ahead.
The interface is designed to be user-friendly, with the top menu bar neatly lined up with various functional options, such as "File", "Edit", "View", etc., so that users can quickly find the functions they need. The toolbar provides a series of commonly used action buttons, such as "New", "Open", "Save", etc., which can be easily completed by users with just clicks.
Figure 1-4 Overview of the WPS spreadsheet startup and interface
In WPS spreadsheets, worksheets are the main place for users to do data processing. Each worksheet is made up of rows and columns, forming a vast data grid. Users can input, edit, and calculate data in this grid to achieve a variety of complex data processing tasks.
Figure 1-5 Worksheet in WPS spreadsheet
It's worth mentioning that the interface of WPS spreadsheets also supports personalization. Users can adjust the interface theme, font size, toolbar position, etc. according to their preferences and habits, so that the interface is more in line with their own usage habits. This high degree of customizability enables WPS spreadsheets to meet the needs of different users and improve work efficiency.
All in all, the WPS spreadsheet's startup and interface overview demonstrates its efficiency, intuitiveness, and personalization. Whether you are a beginner or an experienced user, you can find your own way of processing data on this platform and easily meet various challenges.
1.1.3 Status bar, etc
The status bar plays an important role in WPS spreadsheets, providing users with real-time data feedback and action tips. Through the status bar, users can quickly know the status of the current worksheet, such as row number, column number, cell address, etc., which is essential to improve work efficiency and accuracy. For example, when a user is editing a table with a large amount of data, they can quickly locate a specific cell through the status bar, thus avoiding tedious scrolling and lookup operations.
Figure 1-6 Status bar
In addition, the status bar provides a wealth of functions and tools to help users analyze and process data. For example, you can use functions such as sum and average in the status bar to quickly calculate the sum or average value of data in a column or row in a table. These functions not only improve the speed of data processing, but also ensure the accuracy of the results. At the same time, the status bar also provides advanced features such as conditional formatting and data validation to help users better manage and standardize data.
Figure 1-7 Status bar functions and tools
In practical applications, the role of the status bar cannot be ignored. Take a financial analyst, for example, who usually has to deal with large amounts of financial data and make decisions based on that data. Through the status bar, he can quickly understand the distribution of data, outliers and other information, so as to more accurately judge the rationality and reliability of data. This not only improves his work efficiency, but also provides strong support for the company's decision-making.
As an indispensable part of WPS spreadsheets, the status bar is an important tool to help users better utilize and manage this resource. By making full use of the functions and features of the status bar, users can process and analyze data more efficiently, thus providing strong support for the development of enterprises.
1.1.4 Basic concepts of worksheets and cells
In a WPS spreadsheet, the worksheet and cells are the basic elements that make up the whole table. A worksheet can be understood as a two-dimensional table, consisting of rows and columns, and each cell is an independent unit in this table that is used to store and manage data. This structure allows WPS spreadsheets to remain clear and organized when working with large amounts of data.
Figure 1-8 Worksheet (a 2D table, consisting of rows and columns)
Each cell is an independent unit in this table that is used to store and manage data.
Take, for example, an e-commerce company that uses WPS spreadsheets to manage sales data. Each worksheet represents a different sales quarter, while each cell stores specific sales data, such as sales, sales volume, number of customers, etc. With flexible manipulation of worksheets and cells, they can easily filter, sort, summarize, and analyze sales data to derive valuable business insights.
WPS spreadsheets make data management and analysis more efficient and convenient through the organic combination of worksheets and cells. Whether it is an individual user or a business user, you can give full play to the power of WPS spreadsheets by grasping the basic concepts of worksheets and cells to achieve better results in your work.
1.1.5 Editing and Formatting
In WPS spreadsheets, editing and formatting are the key links to improve the effectiveness of data presentation. With proper editing, we can make the data clearer and easier to read, and proper formatting can enhance the visual impact of the data and improve the readability of the data. For example, in a sales report, we can use conditional formatting to highlight rows of data that are selling more than expected, so we can see at a glance which products are selling well. At the same time, we can also use formulas and functions to calculate and analyze the data, such as using the SUM function to calculate the total sales, using the AVERAGE function to calculate the average sales, etc. These redaction and formatting operations not only improve the readability of the data, but also help us better understand and analyze the data.
In addition, editing and formatting can help us better visualize relationships and trends in the data. For example, in a sales trend analysis report, we can use a graph to show how sales have changed over time. By choosing the right chart type, adjusting the color, font and other formatting settings, we can make the chart more beautiful and intuitive, so that it is easier to find patterns and trends between data.
Table 1-2 Data visualization
| Chart type | color | font | Formatting settings | effect |
| Line chart | blue | Arial | Adjust line thickness and transparency | Visualize data trends |
| histogram | green | Times New Roman | Adjust the height and width of the column | Clearly display data comparisons |
| Pie charts | red | Courier New | Adjust the pie chart size and split ratio | Get a quick overview of the percentage of data |
| Scatter plot | Yellow | Verdana | Adjust the size and color of the dots | Discover patterns in data distribution |
Such reports not only give leadership a quick overview of sales, but also provide powerful data support for decision-making.
By editing and formatting, we are able to transform complex data into intuitive, easy-to-understand graphs and tables that help people better understand and utilize data. In WPS spreadsheets, mastering the skills of editing and formatting is of great significance to improve work efficiency and improve the effect of data presentation.
1.1.6 Interface Customization and Personalization
In WPS spreadsheets, interface customization and personalization are the key links to improve user experience and work efficiency. Through reasonable interface customization, users can adjust the layout and display of interface elements such as toolbar, menu bar, and status bar according to their own usage habits and needs, so that the entire operation interface is more in line with personal work habits and aesthetic preferences. For example, for frequently used features, users can add them to the Quick Access toolbar for quick invocation. At the same time, WPS spreadsheets also provide a wealth of themes and color schemes, which users can choose according to their preferences, making the whole interface more beautiful and comfortable.
Personalization allows users to configure WPS spreadsheets in more depth and detail according to their work needs.
Figure 1-10 Customize the font, font size, color, and border style
For example, in cell formatting, users can customize styles such as font, font size, color, and border to meet the needs of different data display and printout.
In addition, WPS spreadsheets also support conditional formatting, and users can automatically apply different formatting styles according to the data values or formula results of cells, so as to display the changes and trends of data more intuitively. These personalized settings not only improve the efficiency of data processing, but also make the data presentation more vivid and intuitive.
It is worth mentioning that the interface customization and personalization of WPS spreadsheets also have a certain degree of flexibility and scalability. Users can customize macros and VBA scripts to achieve more complex and personalized interface customization and data processing functions. This flexibility and scalability makes WPS spreadsheets a powerful, easy-to-scale spreadsheet software.
The interface customization and personalization of WPS spreadsheets are designed to make it easier for users to use the software, making it easier to process data and improve work efficiency.
1.2 Learn how to create and save workbooks
1.2.1 Create a new workbook
In a WPS spreadsheet, creating a new workbook is a basic and important operation. It provides users with a new, blank spreadsheet interface that gives users the freedom to enter, edit, and manage data. In practice, whether it is daily data recording and collation, or complex data analysis and modeling, creating a new workbook is the first step.
Figure 1-11 WPS spreadsheet creates a new workbook
Imagine you're a data analyst at an e-commerce company and you need to process a lot of sales data every day. At this point, you can create a new workbook to organize sales data for different time periods and product categories. You can create a separate workbook for each product category, and then build a corresponding data table in each workbook, including key metrics such as sales, sales, number of customers, and more. This way, you can clearly see how each product category is selling, allowing you to make more accurate decisions.
In addition, creating a new workbook can also help users better organize and manage their data. For example, you can use different workbooks to store data for different projects, or use different workbooks to record data for different time periods. This way, you can easily find the data you need without having to rummage through huge data tables.
By creating new workbooks, we can better organize, analyze, and leverage our data to gain more knowledge and information.
Table 1-3 Process for creating a new workbook
| steps | operation |
| 1 | Open WPS |
| 2 | Click on the "New" button |
| 3 | Select the Workbook option |
| 4 | Enter a name for the workbook |
| 5 | Click the "OK" button |
In conclusion, creating a new workbook in a WPS spreadsheet is a very practical feature. Not only does it help us better organize and manage our data, but it also improves our productivity and accuracy. Therefore, we should be proficient in this function and give full play to its role in practical applications.
1.2.2 Select the location and file name for the workbook
When saving a workbook, it's crucial to choose the appropriate save location and file name. A clear, meaningful file name can help us quickly identify what the workbook is about and what it is for. For example, if we're working on a workbook about "Q4 2023 Sales Data", we can name it "2023_Q4_Sales_Data.xlsx". Such a file name contains both time information and clarity about the content of the data, making it easy to quickly find it in many workbooks.
The choice of save location is equally important. We can choose to save it in a folder on our local hard drive, or we can choose to save it in a cloud storage service such as WPS Cloud Drive or OneDrive, among others. Choosing cloud storage allows us to sync and share workbooks between different devices to improve collaboration efficiency. At the same time, cloud storage also provides data backup and recovery functions to effectively prevent data loss.
Take an e-commerce company, for example, they may be dealing with a lot of sales data every day. To effectively manage and analyze this data, they might create a folder called "Sales_Data" where they save a workbook of sales data for each day. Each workbook is named after a date, such as "2023_10_23_Sales_Data.xlsx". This makes it easy for team members to find the data they need for further analysis and decision-making.
In addition, the choice of storage location and file name also reflects the professionalism and standardization of data management. As the famous management scientist Peter Drucker said, "Data is the foundation of management." "A clear, well-organized data management system not only improves productivity, but also helps us better understand and leverage data to make more informed decisions.
Therefore, when choosing the storage location and file name of the workbook, we should fully consider its usefulness and standardization to ensure that the data can be managed and utilized efficiently and accurately. In this way, we can make better use of the WPS spreadsheet as a powerful tool to bring more convenience and value to our work and life.
1.2.3 Set the initial parameters and properties of the workbook
When setting the initial parameters and properties of a workbook, you need to carefully consider the impact of these settings on the overall structure and data processing of the workbook. The setting of the initial parameters determines the basic framework and performance of the workbook, while the adjustment of properties can optimize the workbook experience. For example, when setting the calculation accuracy of a workbook, the user needs to weigh the calculation speed against the accuracy. Too high a calculation accuracy may increase the calculation time, while too low a precision may affect the accuracy of the data. Therefore, according to the specific data processing requirements, users need to reasonably set the calculation accuracy to ensure the efficiency and accuracy of data processing.
In addition, when setting the display properties of the workbook, users also need to consider how to make the workbook more readable and easy to use. For example, by adjusting attributes such as font size, color, and background, users can improve the readability of their workbooks and reduce the strain on their eyes from prolonged computer use. At the same time, reasonable layout and formatting can also make the workbook more beautiful and tidy, and improve the user experience.
Table 1-4 Process for setting the initial parameters and attributes of a workbook
| steps | operation |
| 1 | Open WPS Office |
| 2 | Click the "New" button and select "Workbook" |
| 3 | In the workbook, tap the File menu and select Options |
| 4 | In the Options window, select the Advanced tab |
| 5 | In the Advanced tab, set the initial parameters and properties of the workbook |
| 6 | Click the "OK" button to save the settings |
In practice, users need to set the initial parameters and properties of the workbook according to the specific data processing needs and work scenarios. For example, when processing a large amount of data, you may need to set a large number of worksheets and rows to ensure data integrity and processing efficiency. When creating a report or presentation, users need to focus on the aesthetics and legibility of the workbook to better convey the message.
In conclusion, setting the initial parameters and properties of the workbook is an important part of the WPS spreadsheet usage process. Through reasonable settings and adjustments, users can improve the efficiency and accuracy of data processing, optimize the experience of using workbooks, and better meet actual needs.
1.2.4 Create a workbook using a template
In the process of using WPS spreadsheets, the template creation workbook feature has greatly improved our work efficiency. Imagine you're budgeting for a large project and need to quickly create multiple workbooks to manage different budget items. With the template feature of WPS spreadsheets, you can easily achieve this. First, you can create a basic template with all the necessary columns and rows, such as Project Name, Budgeted Amount, Actual Spending, and so on. You can then save this template as a new workbook template to quickly create a new workbook if needed. This way, you can save a lot of time by avoiding having to set up and enter the same information repeatedly.
Figure 1-12 Using a template to create a workbook
In addition, the template's workbook creation feature makes it easier for teams to collaborate. Let's say you're working on a complex report with a team member, and everyone needs to enter data into their own workbook. By creating a workbook using a template, you can ensure that everyone's workbook has the same structure and format, making it easier to summarize and analyze your data. In addition, WPS spreadsheets also support online collaboration features, allowing team members to edit and share data in real-time in the same workbook, greatly improving the efficiency of team collaboration.
Not only that, but the template creation workbook feature of WPS spreadsheets also provides us with a wealth of data analysis and visualization options. For example, you can use the built-in chart templates to quickly create various types of charts, such as column charts, line charts, pie charts, and more, to present your data more visually.
Figure 1-13 Chart templates create various types of charts
In addition, WPS spreadsheets also support a variety of data analysis tools, such as pivot tables, conditional formatting, etc., to help you dig deeper into the value behind your data.
Figure 1-14 WPS spreadsheet: pivot table and conditional formatting
By using WPS spreadsheets to create workbooks, we can collect, organize, and analyze data more efficiently to support decision-making. Both individual users and enterprise teams can benefit from this.
Template creation workbook process for WPS spreadsheets
Table 1-5 Process of creating a workbook from a WPS spreadsheet template
| steps | operation |
| 1 | Open the WPS spreadsheet |
| 2 | Click on the "File" menu |
| 3 | Select the "New" option |
| 4 | Select the desired template from the list of templates |
| 5 | Click on the "Create" button |
| 6 | Edit the template content as needed |
| 7 | Click on the "File" menu |
| 8 | Select the "Save" option |
| 9 | Enter a file name and save path |
| 10 | Click on the "Save" button |
1.2.5 Quick save and automatic backup workbook
In WPS spreadsheets, quick saving and automatic backup of workbooks are key links to ensure data security and improve productivity. Imagine that when you are working on an important financial data report, suddenly the computer malfunctions or the power goes out unexpectedly, and if you don't save it in time, then your work work may be wasted. Therefore, the quick save function is particularly important. With simple shortcut key operations, you can save the current working status at any time, ensuring the integrity and accuracy of the data.
The automatic backup feature further enhances the security of your data. WPS spreadsheets can automatically save your workbook in a specified folder and generate multiple backup files. This way, even if the original file is corrupted or lost for some reason, you can recover the data from the backup file and avoid the loss due to data loss.
In practice, many enterprises and individuals have deeply realized the importance of fast saving and automatic backup. For example, the finance department of a well-known company often needs to work overtime when processing annual financial statements due to the large and complex amount of data. However, with the fast saving and automatic backup features of WPS spreadsheets, they ensure the security and integrity of the data, which greatly improves the work efficiency.
In addition, WPS spreadsheets also provide flexible backup setting options, and users can set the backup frequency and save path according to their needs. This personalized setting makes the quick save and automatic backup function more in line with the user's work habits and needs.
Table 1-6 Process for quick saving and automatic backup of workbooks
| steps | operation |
| 1 | Click the File menu |
| 2 | Select Save or Save As |
| 3 | Select a save location |
| 4 | Enter a name for the file |
| 5 | Click Save or OK |
| 6 | Set the automatic backup interval |
| 7 | Click OK |
In conclusion, quick save and automatic backup workbook are indispensable features in WPS spreadsheets. Not only do they ensure the security and integrity of your data, but they also improve your productivity and user experience.
1.3 Master the basic operation and editing skills of cells
1.3.1 Master the basic operation and editing skills of cells
In WPS spreadsheets, mastering the basic operation and editing skills of cells is the key to improving work efficiency. As the basic unit of the table, cells carry important functions such as data entry, editing, and display. Proficiency in cell manipulation makes us more comfortable working with data.
First of all, it is crucial to understand the basic structure and function of cells. Cells are formed by intersecting rows and columns, and each cell has a unique address, through which we can quickly locate a specific cell. In the cells, we can enter various types of data like text, numbers, dates, etc. At the same time, WPS spreadsheets also provide rich editing functions, such as fonts, colors, alignment, etc., to meet the needs of different data displays.
In practical applications, the quick editing and filling of cell data is an important skill. For example, when dealing with a large amount of data, we can take advantage of the autofill feature of WPS spreadsheets to quickly fill a set of data into multiple cells. In addition, WPS spreadsheets also support the conversion of multiple data formats, such as converting text to numbers, converting dates to specific formats, etc., which greatly improves the flexibility of data processing.
Figure 1-15 The auto-fill function of a WPS spreadsheet
In addition to basic editing functions, adjusting the format and style of cells is also an important means to improve the aesthetics and readability of the table. We can set different formats and styles for different cells according to the nature of the data and the display needs. For example, for important data, we can use bold, color-changing, etc. to highlight, and for data that needs to be compared, we can set different background colors or font colors to enhance the contrast.
In addition, in WPS spreadsheets, the filtering and grouping functions are also basic cell-based operations. The filtering function allows us to quickly filter out data that meets specific criteria, and the grouping function allows us to group data with the same attributes together for easy aggregation and analysis. These features are especially useful when working with large amounts of data, which can greatly improve the efficiency of data processing.
Table 1-7 Basic operations on filtering and grouping based on cells
| operation | description |
| sift | Select specific data in the table, such as filtering data based on date, name, and so on. |
| grouping | Group the data in the table according to certain criteria, such as by department, product, and so on. |
In a word, mastering the basic operation and editing skills of cells is essential to improve the efficiency of using WPS spreadsheets. We can process and analyze data more efficiently by having a deep understanding of the structure and function of cells, mastering the skills and methods of rapid editing and filling of data, flexible formatting and styling of cells, and making good use of filtering and grouping functions.
1.3.2 Understand the basic structure and function of cells
In a WPS spreadsheet, a cell is the basic unit that makes up a worksheet, which carries the functions of storing, editing, and displaying data. Understanding the basic structure and function of cells is essential for the efficient use of WPS spreadsheets.
Cells are formed by the intersection of rows and columns, and each cell has a unique address, identified by a combination of row numbers and column labels. This structure makes it easy for users to locate and manipulate data. Various types of data can be entered into the cell, such as text, numbers, dates, etc., and the display style of the data can be set by the formatting function, such as font, font size, color, etc.
Various types of data can be entered into WPS cells, such as text, numbers, dates, etc., and the display style of the data can be set by the formatting function, such as font, font size, color, etc.
Figure 1-16 Various types of data can be entered in a WPS cell
In addition to basic storage and display functions, cells also have powerful computing and analysis capabilities. Through the application of formulas and functions, users can perform various mathematical operations, data summarization, and statistical analysis in cells. For example, the SUM function can be used to easily calculate the sum of a column of data, while the AVERAGE function can quickly average the data in a certain area.
In addition, cells also support advanced features such as conditional formatting and data validation. Conditional formatting can automatically change the display style of a cell based on the data values in the cell, highlighting important data or data that meets certain criteria. Data validation allows you to set input limits and prompts for cells to ensure data accuracy and compliance.
In conclusion, understanding the basic structure and function of cells is essential to master the use of WPS spreadsheets. By flexibly using the various functions of cells, users can process, analyze and display data more efficiently, and improve work efficiency and decision-making accuracy.
1.3.3 Quick editing and filling of cell data
In WPS spreadsheets, the quick editing and filling of cell data is the key to improve work efficiency. Imagine you're working on a spreadsheet with thousands of rows of data, each containing personal information like name, age, income, and more. Manually entering this data is time-consuming and error-prone. This is where quick editing and filling comes in.
Table 1-8 Quick editing and filling methods for cell data
| way | description |
| Quick filling | After entering data in a cell, hover over the bottom right corner of the cell until the fill handle appears, then click and drag down to fill other cells. |
| Autofill | After entering data in a cell, press Ctrl+Enter key combination, the system will automatically fill in other cells. |
| Sequence filling | After entering the data in the cell, select the "Fill" button in the "Start" tab, and then select the "Sequence" option, you can fill the equal difference column. |
| Custom padding | After entering the data in the cell, select the Fill button in the Start tab, and then select the Custom option, you can customize the fill rule. |
With the auto-fill feature of WPS spreadsheets, you can easily achieve fast data entry.
Figure 1-17 Autofill
For example, when entering a series of consecutive numbers, simply enter the first two numbers, then drag the fill handle, and the WPS spreadsheet will automatically fill in the remaining numbers for you. Similarly, for text data, you can also use the autofill feature to quickly enter duplicate content.
WPS spreadsheets also provide a wealth of functions and formulas to help you achieve more advanced data editing and calculations. For example, the sum of a column of data can be easily calculated using the SUM function, and the average value can be calculated using the AVERAGE function. These functions and formulas not only improve the accuracy of data processing, but also greatly improve work efficiency.
Figure 1-18 PS spreadsheet with rich functions and formulas
In addition, WPS spreadsheets support a variety of data validation and conditional formatting to ensure the accuracy and compliance of input data. For example, you can set up data range validation to make sure that the age data you enter is within a reasonable range. You can also use conditional formatting to highlight data for specific conditions, such as marking rows with incomes above a certain threshold in red to quickly identify high-income groups.
Table 1-9 Data validation and conditional formatting process
| Data validation | Conditional formatting |
| 1. Select the data region that needs to be verified | 1. Select the data region for which you want to set conditions |
| 2. Click on the "Data" tab and select "Data Validation" | 2. Click on the "Start" tab and select "Conditional Formatting" |
| 3. In the Data Validation dialog box, select Allow as an integer | 3. In the Conditional Formatting dialog box, select Highlight Cell Rules |
| 4. In the Data Validation dialog box, select Data as Between and enter the minimum and maximum values | 4. In the Conditional Formatting dialog box, select Numeric between for Meets the following conditions, and enter the minimum and maximum values |
| 5. In the Data Validation dialog box, select Error Warning as Stop | 5. In the Conditional Formatting dialog box, select Format as Fill, and select a color |
| 6. Click "OK" to complete the data verification setup | 6. Click OK to complete the conditional formatting |
Figure 1-18 WPS spreadsheet, multiple data validation, and conditional formatting
In a word, in WPS spreadsheets, the quick editing and filling of cell data is the key to improve work efficiency. With tools such as autofill, functions and formulas, data validation, and conditional formatting, you can easily process large amounts of data, increase productivity, and ensure data accuracy and compliance.
In WPS spreadsheets, by mastering the skills of quick editing and filling of cell data, we can not only improve work efficiency, but also better manage and analyze data to provide strong support for decision-making.
1.3.4 Adjust cell formatting and style
In WPS spreadsheets, adjustments to cell formatting and style are essential for the presentation and readability of the data. By carefully formatting the cells, we can make the data clearer and more intuitive, which can improve work efficiency and accuracy. For example, in financial reports, by adjusting the font size, color, and border style, you can highlight key data and make the report easier to read and understand. In addition, conditional formatting allows us to quickly identify and analyze data by automatically applying different styles based on size, trends, or outliers. This flexibility and customizability makes WPS spreadsheets a powerful tool for processing and analyzing data.
In practice, the adjustment of cell formatting and style can also help us better understand and interpret the data. For example, in sales data analysis, we can use different colors or icons to indicate the increase or decrease in sales, so as to quickly identify sales trends. At the same time, by adjusting styles such as fonts, alignment, and borders, we can make the data table more beautiful and easy to read, and improve the visualization of the data. This approach to data visualization not only helps us better understand and analyze data, but also enhances the persuasiveness and credibility of the data.
By adjusting the cell formatting and style, we are not only able to make the data clearer and more intuitive, but also to improve the readability and comprehensibility of the data. This approach to data visualization not only helps us better analyze and interpret data, but also enhances the persuasiveness and credibility of the data. Therefore, in WPS spreadsheets, it is of great significance to be proficient in adjusting skills of cell formatting and style to improve work efficiency and accuracy.
1.3.5 Screening and Grouping
In WPS spreadsheets, filtering and grouping are important tools for data processing and analysis. With filtering, users can quickly locate data that meets specific criteria, allowing for deeper analysis. For example, in a sales data table, we might want to look at records where sales exceeded a certain threshold during a specific time period. With the filtering function, we can easily filter out this part of the data and further analyze and visualize it.
Figure 1-19 WPS spreadsheet filtering function
Grouping allows users to group data into fields to better understand the structure and distribution of data. For example, in an employee information table, we can group employees by department, and then calculate statistics such as average salary, number of employees, etc., for each department. This grouping analysis helps us to get a more complete picture of the company's organizational structure and workforce composition.
Figure 1-20 WPS spreadsheet grouping function
In addition to the basic filtering and grouping features, WPS spreadsheets also offer extended features such as advanced filtering and conditional filtering. These features allow users to filter and group data based on more complex criteria to meet more advanced data analysis needs. For example, we can use advanced filtering to filter out records that meet multiple criteria at the same time, or use conditional filtering to dynamically change the filter criteria and view the results.
In practical applications, the filtering and grouping functions are widely used in various scenarios. For example, in financial management, we can use the filtering and grouping functions to analyze the company's income and expenses, in marketing, we can use these functions to analyze the buying behavior and preferences of different customer groups, and in human resource management, we can use these functions to evaluate the performance and promotion potential of employees.
In conclusion, filtering and grouping are indispensable data processing tools in WPS spreadsheets. By leveraging these capabilities, we can better understand and analyze data to make more informed decisions.
1.3.6 Deletion and Copying
In WPS spreadsheets, the delete and copy functions are not only basic operations, they are important tools for data processing and analysis. Imagine when you're faced with a large and complex data set, how to efficiently clean and organize the data becomes a key issue. This is where the delete and copy features come in handy.
First, let's talk about the deletion feature. During the data cleansing process, we often need to delete some invalid, erroneous, or duplicate data.
Figure 1-21 WPS spreadsheet deletion function
WPS spreadsheets offer a variety of deletion options, such as deleting rows, columns, cells, or specific content. For example, you can use the Find and Replace feature to quickly delete all cells that contain specific text or numbers. This way, you can ensure the accuracy and consistency of your data.
Table 1-10 WPS spreadsheet deletion options
| Options | description |
| Delete the row | Delete an entire row of data |
| Delete columns | Delete an entire column of data |
| Delete the cell | Delete specific cell data |
| Delete specific content | Delete specific content data |
Next up is the copy feature. When working with data, we often need to copy and paste data to different locations or worksheets. The copy function of WPS spreadsheet not only supports simple cell copying, but also supports the copying of formulas, formats, and styles. This means that you can easily apply the style and calculation logic of one cell to other cells, which greatly improves your work efficiency.
Figure 1-22 WPS spreadsheet copying function
In addition, the copy function can also be combined with other features of WPS spreadsheets to create more powerful data processing capabilities. For example, you can use the copy and paste feature to create a pivot table or chart. By copying the raw data into a new worksheet or chart, you can easily analyze and visualize the data.
Table 1-11 Steps to combine the copy function with a WPS spreadsheet
| steps | operation |
| 1 | Select the text you want to copy |
| 2 | Right-click and select "Copy" |
| 3 | Open the WPS spreadsheet |
| 4 | Right-click in the table and select "Paste" |
| 5 | Done copying, the text has been pasted into the WPS spreadsheet |
It is worth mentioning that the delete and copy function of WPS spreadsheets also supports undo and redo operations. This means that if you accidentally delete important data or copy the wrong content, you can easily revert back to your previous state. This feature provides users with powerful data recovery capabilities and ensures the security of their data.
In short, the deletion and copy functions of WPS spreadsheets are not just simple data manipulation tools, they are also an important weapon for data processing and analysis. By leveraging these two features, you can efficiently clean and organize your data, increase productivity, and ensure data accuracy and security.
【Summary】
WPS spreadsheets are an important part of Kingsoft Office Suite, providing powerful and easy-to-operate spreadsheet processing tools. This chapter will introduce the basic functions of WPS spreadsheets, interface, worksheet and cell concepts, editing and formatting, interface customization and personalization, etc. By mastering these features and techniques, users can efficiently process and analyze data to meet the needs of the workplace. WPS spreadsheets can help users create, edit, and manage spreadsheets to better organize, analyze, and utilize data through features such as setting workbook parameters, using templates, quick savings, and automatic backups. Proficiency in basic cell operations and editing skills, such as autofill, data filtering, and grouping, is also key to improving productivity. In short, WPS spreadsheets provide users with rich functions and convenient operation methods, making data processing more efficient and accurate.
Appendix Review Question 1
(1) What are the basic functions of WPS spreadsheets?
First of all, WPS spreadsheets support a variety of data types, including numbers, text, dates, etc., and can be rich formatting of these data, such as setting fonts, colors, alignment, etc., to make the data presentation more clear and easy to read.
Secondly, WPS spreadsheets provide a variety of functions and formulas that users can use to calculate, statistically, and analyze data. For example, you can use functions such as sum, average, maximum, and minimum to perform quick calculations on data, or you can use advanced functions such as conditional statements and pivot tables for data analysis and processing.
In addition, WPS spreadsheets also support a variety of chart types, such as column charts, line charts, pie charts, etc., users can choose the appropriate chart type to display data according to their needs, making the data more intuitive and easy to understand.
Finally, WPS spreadsheets also provide a variety of data management and protection functions, such as data sorting, filtering, find and replace, etc., as well as data protection, password encryption and other security measures to ensure users' data security and privacy.
In short, the basic functions of WPS spreadsheet are rich and diverse, which can meet the various needs of users in data processing, analysis and display, and is an efficient, convenient and easy-to-use spreadsheet processing software.
(2) How to use WPS spreadsheet for data screening and sorting?
First, let's take a look at how to filter data. In WPS spreadsheets, the data filtering feature allows users to filter out the data they need based on specific criteria. The steps are as follows:
| steps | operation |
| 1 | Open the WPS spreadsheet and select the data region you want to filter. |
| 2 | Click the Data tab in the toolbar and select Filter in the drop-down menu. |
| 3 | A drop-down arrow appears on the header of each data column. Click the drop-down arrow on the column you want to filter and select a filter. |
| 4 | You can select a single condition or multiple criteria to filter based on your needs. Once the settings are complete, click the "OK" button. |
| 5 | The WPS spreadsheet will display only the data rows that match the criteria based on the filter criteria set. |
With data filtering, users can easily filter out data that matches specific criteria to more precisely locate the information they need.
Next, let's take a look at how to sort your data. In WPS spreadsheets, the data sorting feature allows users to sort data in ascending or descending order by the data values of a column. The steps are as follows:
| steps | operation |
| 1 | Open the WPS spreadsheet and select the data region that needs to be sorted. |
| 2 | Click the Data tab in the toolbar and select Sort in the drop-down menu. |
| 3 | In the Sort dialog box that appears, select the column you want to sort by, and select a sort method (ascending or descending). |
| 4 | Click the "OK" button, and the WPS spreadsheet will rearrange the data according to the set sorting criteria. |
Through the data sorting function, users can quickly arrange data in an orderly manner according to the data value of a certain column, so as to better analyze and display data.
In addition to the basic filtering and sorting functions introduced above, WPS spreadsheets also provide more advanced data processing functions, such as conditional summarization, pivot tables, etc. Users can combine these functions for more complex data processing and analysis according to their own needs.
In conclusion, the data filtering and sorting features of WPS spreadsheets provide users with a convenient way to process data. By leveraging these functions, users can process and analyze large amounts of data more efficiently and work more efficiently.
(3) What functions and formulas does WPS spreadsheet support?
As a powerful office software, WPS spreadsheet supports a wide range of functions and formula operations to help users process data efficiently. This includes, but is not limited to, the following types of functions:
| Function category | The name of the function | Description of the function |
| Basic functions | SUM | sue for peace |
| Basic functions | AVERAGE | average value |
| Basic functions | MAX | maximum |
| Basic functions | MIN | minimum |
| Text functions | CONCATENATE | Text connections |
| Text functions | LEFT | Take the character to the left of the text |
| Text functions | RIGHT | Take the character to the right of the text |
| Text functions | UPPER | Convert text to uppercase |
| Date and time functions | TODAY | Returns the current date |
| Date and time functions | NOW | Returns the current date and time |
| Date and time functions | DATEDIF | Calculate the difference between the two dates |
| Find and reference functions | VLOOKUP | Vertical search |
| Find and reference functions | HLOOKUP | Horizontal lookup |
| Find and reference functions | INDEX | Returns the value for the specified location |
| Financial functions | PMT | Calculate the amount of each instalment of the loan |
| Financial functions | FV | Calculate the future value of the investment |
| Financial functions | RATE | Calculate the interest rate on recurring payments |
In addition, WPS spreadsheets also support custom formula operations, and users can write complex formulas for data processing according to their own needs. In general, the functions and formula operations of WPS spreadsheets are very rich, which can meet the needs of users in many fields such as office, finance, and data analysis.
(4) How to use the auto-fill function of WPS spreadsheet to quickly edit data?
| steps | operation |
| 1 | Select the data range that needs to be populated |
| 2 | Click the "Fill" button in the toolbar |
| 3 | Select the type of fill (e.g. series, date, etc.) |
| 4 | Enter the start and end values for the fill |
| 5 | Click the "OK" button to complete the autofill |
(5) What functions and formulas does WPS spreadsheet support?
| The type of the function | The name of the function | Description of the function |
| Mathematical functions | SUM | sue for peace |
| Mathematical functions | AVERAGE | Average |
| Mathematical functions | MAX | Find the maximum |
| Mathematical functions | MIN | Find the minimum |
| Mathematical functions | ROUND | round |
| Mathematical functions | ABS | absolute value |
| Mathematical functions | SQRT | square root |
| Mathematical functions | LOG | logarithm |
| Mathematical functions | EXP | index |
| Mathematical functions | POWER | Powered by |
| Mathematical functions | MANNER | Find the remainder |
| Mathematical functions | CEILING | Improved Arrangement |
| Mathematical functions | FLOOR | Round down |
| Mathematical functions | TRUNC | truncation |
| Mathematical functions | WITHOUT | sine |
| Mathematical functions | COS | cosine |
| Mathematical functions | SO | Positive Cut |
| Mathematical functions | SALTY | Antisine |
| Mathematical functions | ACOS | Anti-cosine |
| Mathematical functions | ATAN | Anti-Orthocutter |
| Mathematical functions | ATAN2 | Anti-Orthocution 2 |
| Mathematical functions | DEGREES | Radian rotation |
| Mathematical functions | RADIANS | degrees to radians |
| Statistical functions | COUNT | count |
| Statistical functions | COUNTIF | Condition count |
| Statistical functions | COUNTIFS | Multi-condition counting |
| Statistical functions | SUMIF | Conditional summation |
| Statistical functions | SUMIFS | Multi-condition summation |
| Statistical functions | AVERAGEIF | Condition averaged |
| Statistical functions | AVERAGEIFS | Average multiple conditions |
| Statistical functions | STDEV | standard deviation |
| Statistical functions | STDEV. S | Standard deviation of the sample |
| Statistical functions | STDEV. P | Standard deviation of the population |
| Statistical functions | WERE | variance |
| Statistical functions | VAR. S | Sample variance |
| Statistical functions | VAR. P | Population variance |
| Statistical functions | MEDIAN | median |
| Statistical functions | MODE | mode |
| Statistical functions | QUARTILE | Quartile |
| Statistical functions | PERCENTILE | Percentile |
| Statistical functions | PERCENTRANK | Percentile grade |
| Statistical functions | RANK | ranking |
| Statistical functions | RANK. EQ | Equivalence ranking |
| Statistical functions | RANK. AVG | Average ranking |
| Statistical functions | LARGE | The K-largest value |
| Statistical functions | SMALL | K-th subvalue |
| Statistical functions | SWAP | arrange |
| Statistical functions | COMBIN | combination |
| Statistical functions | DEVSQ | Sum of squares of deviations |
| Statistical functions | GEOMEAN | geometric mean |
| Statistical functions | HARMEAN | Reconcile averages |
| Statistical functions | SUMPRODUCT | Sum of the products |
| Statistical functions | SUBTOTAL | Breakdowns |
| Statistical functions | FREQUENCY | Frequency distribution |
| Statistical functions | PERCENT | percentage |
| Statistical functions | GROWTH | growth rate |
| Statistical functions | MAXIFS | Multi-condition maximum |
| Statistical functions | MINIFS | Multi-condition minimum |
| Statistical functions | MODE. MUCH | Multi-value mode |
| Statistical functions | MODE. SNGL | Single-value mode |
| Statistical functions | T.DIST | Student t distribution |
| Statistical functions | T.DIST.2T | Student t-distribution (two-tailed) |
| Statistical functions | T.DIST. RT | Student t-distribution (one-tailed) |
| Statistical functions | T.INV | The inverse of the distribution of student t |
| Statistical functions | T.INV.2T | Inverse of the student's t-distribution (two-tailed) |
| Statistical functions | T.INV. RT | Inverse of the student t-distribution (one-tailed) |
| Statistical functions | F.DIST | F distribution |
| Statistical functions | F.DIST. RT | F distribution (one-tailed) |
| Statistical functions | F.INV | The inverse of the F distribution |
| Statistical functions | F.INV. RT | Inverse of F distribution (one-tailed) |
| Statistical functions | CHIDIST | Chi-square distribution |
| Statistical functions | CHIDIST. RT | Chi-square distribution (single-tailed) |
| Statistical functions | CHIDIST.2T | Chi-square distribution (two-tailed) |
| Statistical functions | CHIDIST. INV | Inverse of chi-square distribution |
| Statistical functions | CHIDIST. INV. RT | Inverse of chi-square distribution (one-tailed) |
| Statistical functions | CHIDIST. INV.2T | Chi-square distribution of inverse (two-tailed) |
| Statistical functions | WEIBULL | Weber distribution |
| Statistical functions | WEIBULL. DIST | Weber distribution |
| Statistical functions | EXPON. DIST | Exponential distribution |
| Statistical functions | NORM. DIST | normal distribution |
| Statistical functions | NORM. DIST. RT | Normally distributed (one-tailed) |
| Statistical functions | NORM. INV | The inverse of the normal distribution |
| Statistical functions | NORM. INV. RT | Normally distributed inverse (one-tailed) |
| Statistical functions | NORM. S.DIST | Standard normal distribution |
| Statistical functions | NORM. S.DIST. RT | Standard Normal Distribution (Single-tailed) |
| Statistical functions | STANDARD. S.INV | The inverse of the standard normal distribution |
| Statistical functions | NORM. S.INV. RT | Inverse of the standard normal distribution (one-tailed) |
| Statistical functions | LOGNORM. DIST | Lognormally distributed |
| Statistical functions | LOGNORM. INV | The inverse of the lognormal distribution |
| Statistical functions | FISH | Tomarimatsu distribution |
| Statistical functions | BOYON. DIST | Tomarimatsu distribution |
| Statistical functions | STDEV. P | Standard deviation of the population |
| Statistical functions | STDEV |
(6) How to adjust the cell format and style of WPS spreadsheet?
| operation | steps |
| Adjust cell formatting | Select the cell, right-click, select "Format Cell", and set the font, font size, alignment, etc. in the pop-up dialog box. |
| Adjust the cell style | Select the cell, right-click, select "Set Cell Style", and select a preset style or a custom style in the pop-up dialog box. |
(7) How to use WPS spreadsheets for data verification?
How to Use WPS Spreadsheets for Data Validation?
| steps | operation |
| 1 | Select the data range that needs to be verified |
| 2 | Click on the "Data" tab and select "Data Validation" |
| 3 | In the Data Validation dialog box, select the Settings tab |
| 4 | Select Allow as an Integer, Data as Between Into, Min as 1, and Max as 100 |
| 5 | Click OK to complete the data validation settings |
(8) How to set conditional formatting for WPS spreadsheets?
WPS Spreadsheet Conditional Formatting Table
| steps | operation |
| 1 | Select the cells or range of cells that need to be conditionally formatted |
| 2 | Click on the "Conditional Formatting" button in the start menu bar |
| 3 | In the drop-down menu, select New Rule |
| 4 | In the pop-up dialog box, select the condition type, such as Cell Value, Formula, etc |
| 5 | Set conditions, such as greater than, equal to, contains, and so on |
| 6 | Set the formatting when the conditions are met, such as font color, background color, border, and so on |
| 7 | Click OK to complete the conditional formatting |