15 Excel tips to become an Office 2007 expert

Thread Content

　　 As a table tool in Office software, Excel is extremely convenient for collecting and organizing data. Today, with the explosive growth of information, data collection, organization, and processing have become more important for any industry. Therefore, Excel is widely used and has become an indispensable software for many office workers. Therefore, proficiency in using Excel is also an essential work skill for professionals. However, Excel is indeed powerful. Using it may be no problem for most people, but it is not easy to use it well. Let’s take a look at some common techniques when using Excel. Download address: Office2007 Download Tip 1 A sneak peek of new limitations To enable users to browse large amounts of data in worksheets, Microsoft Office Excel 2007 supports up to 1,000,000 rows and 16,000 columns per worksheet. In other words, the capacity of a single Excel worksheet is 1,048,576 rows by 16,384 columns. Compared with the 2003 version of Excel, it provides 1,500% more available rows and 6,300% more available columns, and the capacity is greatly improved. In addition, Excel 2007 supports up to 16,000,000 colors. 　　 And now, you can use an unlimited number of format types in the same workbook, instead of just 4,000 ； The number of cell references per cell has increased from 8,000 to any number, and the only limit should be the test of computer memory. In order to improve the performance of Excel, Microsoft doubled the memory management from 1GB of memory in the 2003 version of Excel to 2GB. Because Excel 2007 supports dual processors and multi-threaded chipsets, users will experience faster computing speeds on large worksheets containing a large number of formulas at the same time. 　　 Tip 2: Flexibly add headers and footers to Excel documents. Users who often use Excel may know that adding headers and footers to Excel documents was not an easy task in the past. It required layers of dialog boxes to complete the task. But now Excel can add headers and footers to Excel documents in the fully previewable "Page Layout" view, and the operation process is simple and intuitive. Here's how to do it: 　　 1. In Excel’s Ribbon, click the Insert tab. 　　 2. Click the "Header and Footer" button in the "Text" group. 　　 3. The context-sensitive tab of the "Header and Footer Tools" will appear in the "Ribbon", and the "Design" tab provides all the commands we need, so that the header and footer of the Excel document can be edited very intuitively. The beauty of Excel tables can actually be very simple. Tip 3: Beautify tables as quickly as possible. In the latest Excel worksheet, users can focus all their attention on entering important data without having to spend any time on the format setting and appearance of the table, thereby reducing the workload. Let Excel help you complete table beautification work as quickly as possible. The steps are as follows: 　　 1. Locate any cell containing data. 　　 2. On the "Home" tab, in the "Styles" group, select a "Format Table" option. 　　 3. The "Apply Table Format" dialog box will pop up to specify the source of table data. 　　 4. Click the OK button, and the title text color, title cell fill color, and alternate row fill color will be automatically added to the table, and the table area will be converted into a list to obtain many functions of the list, such as automatic filtering, etc. 　　 Tip 4: Automatically apply appropriate formats and automatically create calculated columns. When you need to add new columns to an existing table, Excel can automatically apply the existing formats without resetting. In addition, in Office Excel tables, you can quickly create a calculated column. A calculated column uses a formula that applies to each row and automatically expands to include additional rows, causing the formula to immediately expand to those rows as well. Enter the formula once without using the Fill or Copy commands. The specific steps are as follows: 　　 1. When you want to add a new column, just enter the new field name on the right side of the Excel table and press the [Enter] key. A column will be automatically added and the appropriate format will be automatically applied. 　　 2. Locate the "E3" cell and enter the formula to calculate the total. After pressing the [Enter] key, the formula will be automatically filled to the end of the list, all total amounts will be calculated, and a calculated column will be automatically created. 　　 If the table is very long, you don’t have to drag it down to find the end like before, which makes it much more convenient to use. 　　 Tip 5: Quickly delete duplicate data. Even when you have Excel to help you process a large amount of data, you will inevitably encounter errors. It is common for your eyes to be dazzled by the phenomenon of repeated data entry. Although we check carefully over and over again, it is sometimes really difficult to find such mistakes. At this time, it is even more necessary to use Excel, so that such problems can be easily solved. 　　 1. Select the cell range that needs to be checked and deleted, and click the "Data" tab in the "Ribbon". 　　 2. Click the "Remove Duplicates" button in the "Data Tools" group. 　　　　 3. At this time, the "Delete Duplicates" dialog box will open and select which columns need to be checked for duplicate values. 　　 4. After the settings are completed, click the "OK" button. Excel will check the selected columns for duplicate values ​​and give a prompt message for the processing results. 　　 Tip 6: Intuitively grasp the overall distribution of data through gradient colors. Display a two-color gradient or a three-color gradient in a cell area. The color shading represents the value in the cell, and the gradient color can intelligently change with the size of the data value. It is very powerful. The new "color scale" conditional formatting brought by the new version of Excel allows readers to view it more intuitively and comfortably. 　　 1. Select the data to be analyzed, such as the "A3:C8" cell range. 　　 2. In the "Home" tab of the Excel "Ribbon", click the [Conditional Formatting] button in the "Styles" group. 　　 3. Execute the "Color Level" -> "Green-Yellow-Red Level" command in the "Conditional Formatting" drop-down menu. 　　 At this time, the specific data in the table is displayed in different colors according to the size of the data. It is very practical for data statistical summary and allows people to understand the overall changes at a glance. Make full use of color to make data more expressive. Tip 7: Use color bars of different lengths to make data more expressive. The above is a comparison of the entire table. What if you only need to be able to see the size of a certain column of data at a glance? At this time, you can apply "data bar" conditional formatting to the data. The length of the data bar represents the size of the value in the cell. By observing the colored data bars, you can save the time of comparing values ​​one by one and easily know the maximum or minimum value in a column of data. The specific operation method is as follows: 　　 1. Select a cell range in the table. 　　 2. In the Home tab of the Excel Ribbon, click the Conditional Formatting button in the Styles group. 　　 3. Then, execute the "Data Bar" -> "Blue Data Bar" command in the pop-up drop-down menu. 　　 At this time, after applying the "data strip" conditional format, you can clearly see the maximum and minimum data values ​​in the entire column, without having to compare the data one by one to see it in a blur. 　　 Tip 8: Use tables to present the development trend of data. By using the colorful and diverse icon sets provided by Excel, you can quickly display the development trend of the data graphically and have better predictability for grasping the overall situation. Operation steps: 　　 1. Select the data that represents the trend, in the "Home" tab, click the "Conditional Formatting" button in the "Style" group, and execute the "Icon Set" -> "Four-way Arrow (Color)" command in the pop-up drop-down menu to add corresponding icons to the data. 　　 2. If the icons added at this time cannot meet your needs, you can also click "Conditional Formatting" in the "Style" group, execute the "Manage Rules" command in the pop-up drop-down menu, and open the "Conditional Formatting Rule Manager" dialog box. 　　 3. In the dialog box, select the "Icon Set" rule and click "Edit Rule". In the Edit Formatting Rules dialog box that opens, set specific rules (such as value type and size) to display different icons. 　　 Tip 9: Quickly create professional charts. Excel provides users with a very smart chart design tool, which allows you to quickly complete professional data statistics and analysis, and easily create professional and beautiful charts. How to operate: 　　 1. Select a cell range, click the "Insert" tab, and select "Pie Chart" in the "Chart" group. 　　 2. The "Chart Tool" will appear. In the "Chart Style" group in the "Design" tab, you can see many professional color schemes, and you can choose any style. Then select the first layout from the "Chart Layout" group to automatically add percentage labels. 　　 3. To set the label text on the pie chart, you can right-click on any label text, and a floating menu will pop up (also available in Word and PowerPoint). It contains some of the most commonly used commands, and you can set the font color and font size here. 　　 In the past, it took a lot of time to set up such professional and beautiful charts, but now it can be done in less than a minute. 　　 Tip 10: Write formulas at will. Office's new, work-product-oriented user interface design places the most commonly used commands in the "Ribbon". Users can quickly discover and use the required functions in the "Function Library" group of the "Formulas" tab, thereby improving the efficiency of writing formulas. However, some long formulas are inconvenient to view in tables. In order to make it easier to view and edit long formulas in cells, you can adjust the size of the formula box in the edit bar through the following 3 methods. Specific operations: 　　 1. To switch between expanding the formula box into three or more lines, or collapsing the formula box into one line, click the expand button at the end of the edit bar, or press the [Ctrl]+[Shift]+[U] key combination. 　　 2. To adjust the height of the formula box accurately, hover the mouse pointer at the bottom of the formula box until the pointer changes to a vertical double arrow, hold down the left mouse button and drag the vertical double arrow up and down to the position you want, and then release the mouse. 　　 3. To automatically resize the formula box to fit the number of lines of text in the active cell up to the maximum height, hover the mouse pointer over the formula box until the pointer changes to a vertical double arrow, and then double-click the left mouse button. Tips for Creating Beautiful and Efficient Worksheets in Excel 11. Sort data by color. Almost everyone likes colorful colors. Reasonable and effective use of colors in documents can significantly improve the attractiveness and readability of documents. Being able to sort and filter data by color is the icing on the cake for quickly organizing and finding the data you need. With Excel 2007, you can sort and filter data by formatting, including cell color and font color, whether manually or by conditionally formatting cells. Let’s first take a look at how to custom sort data by color. 　　 1. In the "Style" group of the "Home" tab, use the "Highlight Cell Rules" function in "Conditional Formatting" to apply different cell colors and font colors to any cell range. 　　 2. Click a cell in the data area, click the "Sort and Filter" button in the "Edit" group, and execute the "Custom Sort" command in the pop-up drop-down menu. 　　 3. In the "Sort" dialog box that opens, specify the primary/secondary keywords, sorting basis and order, etc., and add 4 sorting conditions. After clicking OK, the data is sorted according to the specified color order. 　　 Tip 12 Filter data by color In the previous tip, we have learned how to sort data by color, so how do we filter data by color? Don't worry, the answer will be revealed soon. The operation is as follows: 　　 1. Use the "Highlight Cell Rules" feature in "Conditional Formatting" in the "Style" group on the "Home" tab to apply different cell colors and font colors to any cell range. 　　 2. Click a cell in the data area, click the "Sort and Filter" button in the "Edit" group, execute the filter command in the pop-up drop-down menu, and Excel will automatically add an auto-filter lower triangle button to the column title. 　　 3. Click the lower triangle button on the right side of the "Expiration Date" column title, execute the "Filter by Color" command in its drop-down menu, and then select a color from the "Filter by Cell Color" or "Filter by Font Color" list on the right to complete the filtering work. 　　 Tip 13: Easily set cell styles If you want to apply several formats in one input step and ensure that the formats of each cell are consistent, you can use cell styles. A cell style is a defined set of formatting characteristics, such as font and size, number formatting, cell borders, and cell shading. Through the built-in cell styles provided by Excel, any user can complete the formatting work very conveniently. Simple operation is as follows: 　　 1. Select the cells you want to format. 　　 2. On the Home tab, in the Styles group, click the Cell Styles button. 　　 3. Click the cell style you want to apply. 　　 Also, as a bit of common sense, cell styles are based on the document theme that applies to the entire workbook. When you switch to another document theme, cell styles are automatically updated to match the new document theme. 　　 Tip 14: Make sure you can always see every table title. In order to make it more convenient when reading long data tables, Excel provides a very practical function. As long as you convert the data area into a table, you can always clearly see every title when browsing table data. 　　 1. Select the data range to be converted into a table and click the "Insert" tab. 　　 2. In the "Table" group, click the "Table" button to open the "Create Table" dialog box, select the "Table contains headers" check box, and click OK. 　　 3. After creating the table, you can always see the table title by scrolling the table up and down, that is, the original title row appears at the position of the column heading (of course, the cursor must be in the table, and the column title can only be seen by dragging the scroll bar). 　　 Let me share a little trick. You can directly "apply table format" to an ordinary data area and convert the data area into an Excel table. 　　 Tip 15 Summary of data in tables After creating a table in Excel, you can manage and analyze the data in the table independently of the data outside the table. You can quickly summarize data in an Excel table by displaying a summary row at the end of the table and then using the functions available in the drop-down list of each summary row cell. The steps are as follows: 　　 1. Click anywhere in the Excel table, "Table Tools" becomes available, and the "Design" tab is displayed. 　　 2. On the Design tab, in the Table Style Options group, select the Total Rows check box. 　　 3. At this time, a summary row is displayed at the bottom of the table, and the word "Summary" is displayed in the leftmost cell. 　　 4. In the summary row, click the cell for which you want to add a summary calculation, and then select the desired function in the drop-down list, such as sum, average, etc. 　　 After learning the 15 tips introduced above, I believe your skills in using Excel will quickly improve, and you will be more comfortable processing various tables, ensuring the accuracy and beauty of the data with the least effort. ■