8 convenient tools in Excel you may not know yet

If all you're doing with Excel is just in simple spreadsheets, you can use a ledger and a brush. This ledger will not calculate everything for you, but there are really many valuable tools that will help you perform many different tasks.

1. Total sub . When you encounter a row of data files arranged by date, it will be very difficult to view overall. The extra total feature in Excel can help you fix this. Click Subtotal on the Data ribbon (menu) in Excel 2007 or select Subtotals from Data in Excel 2003. By default it will provide a sum-type subtotal (total type) for each change that takes place in the column [at the end. left, but you can choose to take a column and the action you like. You can even create multiple subtotals, possibly for months, quarters or years. A ribbon on the left will allow you to keep detailed data confidential and only show the other levels of subtotals.

2. Automatic conditional formatting . Conditional formatting in Excel 2003 is an obstacle. With Excel 2007, it will help you figure out patterns in data and use it very simply. Just mark a group of cells and click Conditional Formatting on the Home ribbon. When you hover over the options, you will see its preview immediately. It is possible to assign each color a cell to judge its rank in the entire range of values, add a transparent data bar that reflects the value of the cell . This way of doing allows a lot simpler than with complexity in Conditional Formatting dialog box of Excel 2003.

8 convenient tools in Excel you may not know yet Picture 1

3. Analysis table (PivotTable) . Excel's PivotTable feature allows you to get fast and flexible data analysis. Would you like to see the number of times each value appears in a column that is too long? Highlight the column, click PivotTable on the Insert tab (in Excel 2003, select PivotTable from the Data menu), and click Finish . Drag the column header field into the Row Labels box and into the Values box (in Excel 2003, the Drop Row Fields Here area and the Drop Data Items Here area ). You will immediately get a classification list of all the values ​​in the column, along with the number of times each value appears. That is the secret of PivotTable.

8 convenient tools in Excel you may not know yet Picture 2

4. Control documents (Document Inspector) . The documents of Excel 2007 include more information than data rows and columns. They can contain marked changes, comments, individual attributes, etc. To review hidden data in an Excel 2007 document, click the Office button at the top left, point to Prepare and click Inspect Document . It is very similar to the Document Inspector in Word, but instead of looking for hidden text it searches hidden rows and columns, hidden worksheets and hidden formatted objects. Excel 2003 does not have this feature. Most of what it can do (not much) is to remove personal information from file properties when you save them.

8 convenient tools in Excel you may not know yet Picture 3

5. View page breaks (Page Break Preview) . Excel will print your spreadsheet using the many pages needed to display all data both vertically and horizontally. If the last column is irrelevant, printing a worksheet may need twice as many pages. It's even more wasteful if you leave those printed pages and print again. To avoid this annoyance, click Page Break Preview on the View ribbon (in Excel 2003 select Page Break Preview from the View menu). Now when you resize columns, change text or make other layout changes, you will immediately see the effect on page breaks. It is also a quick way to find out the number of pages you will have to print.

8 convenient tools in Excel you may not know yet Picture 4

6. AutoSum . After you have entered a number of terms in a certain column, there are nine out of ten times you will end the column with a total. Excel can easily calculate the sum of a row or a column. Click one cell after the row or column and click the AutoSum button from the Home ribbon in Excel 2007 or the toolbar in Excel 2003 (or simply press Alt- = ). Excel will calculate the sum of rows or columns automatically. If you don't want to calculate this sum, click the button's menu to select average items, minimum (minimum), maximum (maximum) or other functions.

7. Tables and lists (Excel lists / tables) . Excel 2003 calls them lists but in Excel 2007 is called tables. No matter what names they are called, this feature allows you to define a range of cells, sorting, filtering, and summing columns easily, and also possible insert and add rows without adjusting your formulas. Excel 2007 also offers many pre-defined table styles to make formatting easy. In Excel 2003, check the boxes and select Data | Lists | Create List from the menu. In Excel 2007, click Table on the Insert ribbon.

8 convenient tools in Excel you may not know yet Picture 5

8. Get data from the Web . You meet a statistics table of data on a certain Web site; and want to drag them into Excel! From the Web click in the Data ribbon (in Excel 2003, select Data | Import External Data | New Web Query from the menu). Copy / paste the web address from your web browser into the New Web Query dialog box. An arrow icon will appear next to each table that Excel can import; Click one or more of them, then click the Import button. Immediate data will be in your nature. It has an online sale status - click the Refresh Data button in the floating toolbar (2003) or Refresh All button in Data ribbon (2007) to update your spreadsheet with the latest data.

8 convenient tools in Excel you may not know yet Picture 6