1. Delete blank rows. sometimes we don;t need blank rows . You may have a spreadsheet with blank rows that are spread throughout the worksheet. Instead of deleting each blank row individually, you can delete all blank rows at once. For this to work, your header rows must be on the first row of the spreadsheet. Select the entire columns that contain your data by clicking on the letters at the very top of the columns. On the Data tab, Sort & Filter group, select Filter. Click the drop-down arrow on the right side of the first column of your data, uncheck (Select All), and check (Blanks). If any numbers are still visible, go to the second column of your data and repeat the step above. Continue to repeat the steps for each column until no data appear. Select the filtered rows and go to the Home tab, Cells group, and select Delete. On the Data tab, Sort & Filter group, select Clear.
2. Hide 0 values. Including many 0 values in your data can be distracting. To easily hide 0 values, go to the File tab, Options, Advanced, and uncheck Show a 0 in cells that have 0 value.
3. Name a cell or range of cells & AutoSum shortcut. A cell or a range of cells can be given a name in Excel. You can reference the name in your formulas and functions rather than trying to remember or find a specific cell or range of cells. To do this, highlight the cell or range of cells that should be named, then type the name in the Name Box (the box to the left of the formula bar). Click Enter.To quickly sum a list of values, select the cell at the bottom of a vertical list of values or to the right of a horizontal list of values and press Alt+=, then Enter.
5. Quickly foot and crossfoot & Instantly select an entire table or data range. AutoSum can be used to insert sum formulas that total all columns and rows at the same time. Highlight a table of data, plus one additional row below and one additional column to the right of the data. Click the AutoSum button or press Alt+=.To instantly select an entire table or data range, click anywhere within the table or data range and press Ctrl+A.
6. Sort data based on colour. Sorting data in Excel is not limited to sorting based on cell values. Data can also be sorted based on cell colour and font colour. To do this, select the data to be sorted. On the Home ribbon tab, select Sort & Filter from the Editing group, then select Custom Sort. Ensure that My data has headers is checked if headers were included in your selection. In the Sort by drop-down list, choose the column on which you want to sort. In the Sort On drop-down list, choose Cell Color or Font Color. In the Order drop-down list, choose the colour you want shown first. Next, click Add Level located at the top left of the Sort window. Complete the same steps as above for the second colour that should be shown, and so on until you have instructed Excel on the order in which to sort all cell colours or font colours.
7. Quickly resize a column to fit contents. You may have data in a cell that is much shorter than, or too long to fit in, the default width of a column. Instead of trying to manually adjust the width of the column to get the right size, double-click the boundary between two column headers (eg, the line between the A and the B for the first and second columns), and the column size for the column to the left will be perfectly sized to accommodate the cell with the longest text.
8. Extract characters from the left of a text string & Extract characters from the right of a text string. You may need to extract a portion of data to the left of a text string. For example, you may need to extract the area code of a phone number. Use the function LEFT(Text, Num_chars). For Text, reference the cell that contains the text string. For Num_chars, enter the number of characters to the far left of the text string to extract. Click OK.You may need to extract a portion of data to the right of a text string. For example, you may need to extract the last four digits of a National Insurance number. Use the function RIGHT(Text, Num_chars). For Text, reference the cell that contains the text string. For Num_chars, enter the number of characters from the far right of the text string to extract. Click OK.
9. Extract characters in the middle of a text string. You may need to extract a portion of data in the middle of a text string. For example, you may need to extract digits in the middle of a product number. Choose the function MID(Text, [Start_num], [Num_chars]). For Text, reference the cell that contains the text string. For Start_num, enter the position of the first character to extract (eg, if you wanted to start extracting at the fourth character in a text string, the Start_num would be 4). For Num_chars, enter the number of characters from the Start_num of the text string to extract. Click OK.
10. Merge multiple cells into one text string and Hide/unhide worksheets , columns/rows . You may need to combine data from various cells into one text string in one cell. You can also include spaces, symbols, etc, in the new text string you are creating. Choose the function CONCATENATE (Text1, Text 2, etc). For Text1, enter the cell reference, text, or any other characters for the beginning of the text string. For Text2, enter the cell reference, text, or other characters for the next part of the text string, and so on. For example, if you had a first name in cell A1 and a last name in cell B1, and you wanted to combine the first and last name into one cell with a space separating the two, you would reference cell A1 for Text1, enter a space (in quotes) for Text2, and reference cell B1 for Text3: CONCATENATE (A1,” “,B1). To hide a worksheet, right-click on the tab of the worksheet (located at the bottom of the Excel workbook) and select Hide. To unhide a worksheet, right-click on any tab of the worksheet, select Unhide, then choose which sheet to unhide. Hide/unhide columns/rows. To hide columns or rows in a worksheet, select the columns or rows to be hidden, right-click within those rows or columns, and select Hide. To unhide columns or rows in a worksheet, select the columns or rows surrounding the hidden columns or rows, right-click within those columns or rows, and select Unhide.