12 Super Practical Tips in Excel

Source: Internet
Author: User
Tags manual range sort

We often use Excel to make tables, processing data, how to improve efficiency? Here we will organize 12 of Excel common tips.

First, in Excel to calculate the results of multiple columns of data, you can in a column next to show its every step of the calculation, used to track the balance of bank account changes is absolutely straightforward.

As shown in figure I, assuming that the C2 contains the original account balance, A3 is revenue, B3 is an expense, the C3 unit input Formula =sum (c2,a3-b3) can be calculated as a result of this step. When you enter other transaction data later, just hold down the C3 fill handle and drag down. XLS file Download

If you have multiple similar worksheets in the same workbook, you do not need to enter the same text information for each of these worksheets. Hold down the SHIFT key, click the name of the worksheet to select all the worksheets, and then fill out the same content on the first worksheet, and the rest of the worksheet will automatically appear.

If you want to open a specific workbook every time you start Excel, save the workbook to the XLStart folder to open it automatically. XLStart are usually under program FilesMicrosoft Officeoffice or Office10. But accessing the XLStart folder is not convenient, especially because many people are accustomed to keeping all the common documents in My Documents for easy backup and access. Fortunately, Excel allows users to specify another folder to save automatically opened files. Select Menu "Tools/Options" to fill in the General tab, "Open all files in this key at startup", as shown in Figure Ii. In addition, recently used files can be found at the end of the File menu, and Excel defaults to record 4 recently used files, up to 9 records (also set in the Options dialog box), but the history option for the Open File dialog box does not have this limitation.

Four, to select a large chunk of content is not an easy thing, may be omitted, or because the contents of a cell beyond the boundaries of the selection of more blank cells. You can select a small block of content, and press Ctrl+shift+8,excel to automatically extend the selection to all the non-empty cells around it.

Excel can track the most recently used commands, that is, the menu only shows the common commands, the rest of the command "folded" up. Unless you click on each menu that you want to display directly, it is not possible to display which menus and which menus are available for manual control. Looking for a menu from a long list of menus is not difficult for a skilled user, but opening a folded menu can be tricky. If you want all menus to be displayed, right-click any toolbar and choose Customize, as in Figure three, select "Always Show entire menu" under the Options tab.

Six, for the regular data, such as date, time, week, etc., do not need all manual input. Enter the first 2, 3 (as long as you can determine the law of the data), then use the AutoFill function to enter the rest: Select the manually entered unit, move the cursor to the small square on the corner, the mouse pointer into a black cross-shaped fill handle, drag the fill handle through the fill area, as shown in Figure four.

Excel has a powerful list management capability that can be sorted alphabetically or numerically and without upsetting the order of other related columns. Select the list contains all the columns, select the menu "Data/Sort", the row series can have up to three, allowing the specified ascending or descending order. Sometimes, the sorting criteria are neither numeric nor alphabetic, but "A, B, C, D, ..." sequence, you need to click the "Sort" dialog box "Options" button to specify a custom sorting criteria. There are no predefined sorting criteria for Excel, for example, according to the order of "General manager, deputy general Manager, Computing Center, ...", click the Excel Menu "Tools/Options", as shown in Figure Five, select the "Custom Series" tab, enter the custom sequence, and then click the "Add" button. After you customize the sorting criteria, sort them in the usual way.

Eight, the simple calculation does not need to enter the formula. Select multiple units, the status bar at any time to show the sum result, right click on the results, the menu shows a variety of commonly used computing functions, such as average, count, maximum and minimum values, such as Figure seven.

In a large Excel worksheet, to quickly find a specific unit or range, simply enter a cell or range name in the Name box at the top of Excel. The Name box shows the name of the currently selected unit, and clicking the next arrow to get a drop-down list shows the history of looking up the cell through the Name box. If you want to name a cell or range, select the "insert/name/definition" menu, enter the name of the cell, enter the cell position, and then click OK, as shown in Figure eight. If you want to quickly jump to a specific cell in another worksheet, simply precede the cell name with the sheet name and exclamation point, such as "sheet3! D38 ". In addition, press F5 to open the Locate dialog box for complex positioning and selection operations.

If you find that data that is organized by rows is converted into columns, or vice versa, data is easier to use and Excel can help you achieve row-column substitution. Select the data you want to convert, copy it, and then move the cursor to the beginning of the new data, select "Edit/Paste" as shown in Figure nine, and select the "Transpose" option.

There may be a waste of paper when you print in Excel. For example, if some cells are already formatted, Excel prints blank cells when they are printed, even if they have not been used. To avoid wasting paper, you can select the range of cells you want to print, select File/print, and select selected area in the Print Content dialog box, as shown in Figure 10. If the content you want to print belongs to more than one area, just hold down the CTRL key and select it. If your worksheet is large, the range you want to print cannot be displayed on the screen at the same time, select menu View/Page preview, and then press CTRL to select the area you want to print. If you want to print more than one (but not all) worksheets in a workbook, simply select each worksheet before selecting "File/print" menu: For adjacent worksheets, click the name of the first worksheet, and then hold down SHIFT while clicking the name of the last worksheet; for nonadjacent worksheets, First click on the first worksheet name, then hold down the CTRL key and then click the rest of the worksheet name.

12, if the workbook has too many worksheets, can not see their names on the screen at the same time, using scrolling method to switch worksheets is more cumbersome. Right-click the worksheet scroll arrow in the lower-left corner, as shown in Figure 11, which allows you to quickly switch to any worksheet by using the pop-up menu.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.