September 2, 2011

Excel Spreadsheets Analysis Tips & Tricks

Excel spreadsheets tips & tricks

Excel Tips & Tricks to remove blank rows & cells

Excel Tips & Tricks

  • Select your data
  • Press F5. This opens “Go to” dialog in Excel. 
  • Now hit on that “select” button.
  • From “select special” screen, select “Blanks” (shown aside). Now, all the blank cells will be selected.
  • Just press CTRL and Minus sign (-)
  • Select “shift cells up” or “entire row” as needed.
  • All excel blank cells & rows will be deleted

Excel Tips and Tricks to remove blank rows and cells

Source: http://chandoo.org/wp/2010/01/26/delete-blank-rows-excel/

 

  • Shortcut Keys: On most of Excels menu items you will see the shortcut key associated with it. To see a complete list push F1 and type "Shortcut Keys".
  • Quick Help: To get quick help on any menu item push Shift+F1 and click the menu item 
  • Insert Today's Date: To insert Today's date push Ctrl+; (semicolon) 
  • Insert Current Time: To insert the current time push Ctrl+Shift+: (Colon) 
  • Show the Paste Function (Function Wizard): Push Ctrl+F3 
  • Show the GoTo dialog: Push F5 
  • Show the Paste Names dialog: Push F3. This will only work if you have named ranges. 
  • Name a Range: To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name. 
  • Go To a Named Range: To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5. 
  • Edit a Named Range: To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3. 
  • Headings as Range Names: Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3. 
  • Named Formula: To make a Name refer to a constant formula e.g. "TaxRate", go to Insert>Name>Define and type TaxRate in the "Names in Workbook" box and 36% in the "Refers To". Now enter =(10*TaxRate) anywhere on the Worksheet. 
  • Named Range List: To obtain a list of all Named Ranges and where they refer, select any blank cell (make sure you have no data underneath or 1 column over) and go to Insert>Name>Paste then Paste List. 
  • Nested Formulas: To help write nested formulas (more than 1 formula in a single cell) use the "Paste Function" i.e. Insert>Function or Shift+F3. Select the function that you need, enter the reference, number or text then select the drop arrow to the left of the formula bar to add more Formulas. Doing it this way ensures all your parentheses are in the correct places. 
  • Debugging Formulas: To troubleshoot complex formulas select the cell containing it and then click the = (Equal sign) to the left of the formula bar, this will activate the "Paste Function". To step through your formula simply click in the part of the formula you want to debug. 
  • Personal Help: To add your own text to any of the Office Assistants help files, push F1, enter your question then open the file. Go to Options>Annotate and type in your own text then click OK. You will now notice a paperclip symbol next to the heading, this will let you know that you have added your own Help in a way you will understand. 
  • Different Help: Sometimes the Office Assistant is not very helpful to your needs, so try the "Context and Index" help by either clicking Help>Context and Index or selecting "Help Topics" from any "Help" file. 
  • Customizing Toolbars: Right click on any Toolbar and select "Customize" or push Ctrl+Shift+F10 twice then "Customize". Now click the "Commands Tab" and drag menu items both on and off the Toolbars. If things get a bit messy simply click the "Toolbars" tab and click "Reset". This will return all menu items to their default. 
  • Quick Charts: To create quick charts, click anywhere within your data and push F11. 
  • Worksheet Template: Set up your Worksheet how you want it e.g. formatting, formulas etc then delete all other sheets in the Workbook. Now go to File>Save or Alt+F2 and select "Template (*.xlt)" from the "Save as Type". Type a name and click "Save" Now right click on the sheet tab and select Insert you should see your Template sheet. 
  • Secret Menu: Click in any cell, then move your mouse pointer over any border of the cell until the mouse pointer changes to an arrow, right click and drag to it's destination and then release. 
  • Secret Menu 2: Place a date in any cell, then move your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouse pointer changes to a small black cross. Now right click and drag to any cell and release. 
  • Quick Cell Move: Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and drag to it's destination and then release. 
  • Quick Cell Copy: Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and hold down the Ctrl key and drag to it's destination and then release. 
  • Change Formulas to Values: Click in the cell(s) with the formula(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, right click and drag to the next cell, now still holding down the right mouse button drag back to where you Start ed and release. Now select Copy here as values only. 
  • Quick List: To quickly copy down the contents of a cell that has a list in the column to the left or right of it, simply click in the cell you want to copy and then Double click the Fill handle (little black square on the bottom right of the cell). 
  • Fill Blank Cells Within a List: Let's say you have a list of entries in column A and within the list you have many blank cells. Here is a quick way to fill those blanks with the value of the cell above. Highlight column A, then push Ctrl+G and click Special then check the Blanks option and click OK. Now push Equals (=) then the Up arrow and finally holding down the Ctrl key push Enter. 
  • Auto Fill: To fill a series across columns or down rows type January or Jan in any cell and place your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouser pointer changes to a small black cross. Left click and drag down or across. This can also be done with Numbers, Weekdays, Quarters or any text that ends in a number e.g. Day1. 
  • Custom Auto Fill: Type your list across columns or down rows. Now go to Tools>Option and select the "Custom Lists" tab. Click the collapse dialog box to the right of the "Import list from cells" box, highlight your range, click the expand dialog and then click "Import". Or type your entries in the "list Entries" box. 
  • Adding Text to Formulas: To show a formula result and text or number(s) in the same cell type a & (Ampersand) after the formula then your text/number(s). 
  • Adding Hidden Text to Formulas: Imagine you have a formula like: =$2018+$1056-4*$120. When you initially wrote it you knew what each number represented, but you come back later and can't remember. Add a hidden note to your formula by using the N() formula i.e. =$2018+$1056-4*$120+N("My Wage+Bonus-4 weekly loan repayments"). The N() function will convert text to zero. 
  • Custom Format: You can format a cell to show any number or text without changing it's real value using "Custom Format". To see this type the number 20 in any cell then go to Format>Cells or push Ctrl+1. Select the "Number" tab and then select "Custom." Using any one of the pre-defined formats type "Twenty" (without quotations) or any text and then click "OK". To test it use the cell in any formula. 
  • No More Chart Gaps: If you have a chart that is plotting empty text ("") or 0 (zero) from a formula then instead of using "" or 0 if the formula is False try using "#N/A" (without the quotations) or the formula =NA(). Or you can hide the Row(s) or Column(s). Either way Excel won't plot #N/A or hidden Rows or Columns. 
  • My List: If you have a long list of Text with no blank cells between and you want to see a preview of what is in your list. Click in any cell within your list then right click and select "Pick from list", If you select one of the entries, Excel will insert it in the cell for you. 
  • Remove Blank Rows: Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete or Ctrl+Shift+= (equal) and then select "Entire row" from the "Delete" dialog and click "Ok". 
  • Sort Out Blank Rows: The quickest way to remove all blank rows is to select you range then go to Data>Sort. 
  • See Formula cells: If you have a sheet full of formulas and you want to identify these cells at a glance go to Edit>Go to>Special and select "Formulas" then click "OK". Now go to Format>Cells or Ctrl+1 and select the "Patterns" tab and choose a color. 
  • En Masse Changes: To make changes to more than one worksheet at the same time select one of the sheets, hold down your Ctrl key and click on each sheet name tab. Now any data entered one sheet will also be entered on the other(s). When you have finished right click on any of the sheet name tabs and select "Ungroup sheets". 
  • En Masse Changes 2: Another way to have changes on one worksheet reflected on other sheets is to make all the changes you want on one sheet then hold down your Ctrl key and select the other sheet tabs. Go to Edit>Fill>Across Worksheets and Excel will give you 3 choices of what to copy to the other sheets i.e. "All", "Contents" or "Formats". 
  • Worksheet Copy: Select the sheet name tab then hold down your Ctrl key and simply drag it to the position you want it. 
  • Paste Reference: An easy way to reference another cell is to select the cell you wish to reference then right click and select Copy or Ctrl+C then select the cell you want the reference in, right click again and select "Paste Special" then click "Paste Link" 
  • Absolute/Relative Toggle: If you have a formula you want to make absolute or relative then double click in the cell or F2 then place the insertion point anywhere in the cell address and push F4 1, 2 or 3 times. 
  • Repeat: To repeat an operation push F4 
  • Undo: To undo an operation push Ctrl+Z 
  • Linked Picture: A good alternative to a textbox or any shape is a linked picture that reflects any changes made to its reference. To make one, copy your cell(s), select the destination cell and holding down your Shift key go to Edit
  • Run a Macro by Clicking a Cell: This is possible with use of VBA but let's face it most people don't know VBA so here is an easy way. Select the cell you want to run the macro and hold down your Shift key and go to Edit>Copy Picture then select "As shown on screen" from the "Copy Picture" dialog then hold down your Shift key again and go Edit and click "Paste Picture". Now right click on the cell picture and "Assign Macro". 
  • Non Formula Result: Sometimes you just want the result from the Sum, Average, Min, Max etc from a group of cells without typing a formula in a cell. Excel allows you to do this very easily, first highlight the cells you want to evaluate then right click on the "Status Bar" and select the function you want and your result will be displayed in the "Status Bar". 
  • Reduce File Size: When you have a workbook that is very large in size you can reduce this dramatically by saving the file as "Microsoft Excel Workbook (*.xls)" as apposed to "Microsoft Excel 5/95 Workbook (*.xls)". In other words avoid saving as multiple versions whenever possible. Also click here for much more details and other methods. 
  • Cell Navigation: To move through a group of cells that you are working with without going outside the range highlight the group of cells and then use the "Enter" key to move through them. 
  • Quick Formula Syntax: When writing formulas for Excel sometimes you just need a quick reminder of the formula syntax. In this is the case then type an equal sign followed by the function name and push Ctrl+Shift+A. For Example typing =Vlookup and then pushing Ctrl+Shift+A will give you: =vlookup(lookup_value,table_array,col_index_num,range_lookup). The non-bolded arguments are optional. 
  • How to copy formulas without the reference changing: This can be achieved by either pressing F2 and then highlighting the formula, Copy, Enter then paste to destination. Or doing the same in the Formular bar. However, this is not much good for large amounts of data, so try this: Select the range of cells with Formulae, use the Ctrl key for non-contiguous ranges. Now go to Edit>Replace and Replace = with #. Copy and paste to your location and then simply use Edit>Replace # with = 
  • How to copy and transpose formulas without the reference changing: In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows. Now with the formulas selected go to Edit>Replace and Replace = with #. Now copy, select cell B1, go to Edit>Paste special and choose Transpose. Delete Column "A" and with Row 1 selected go to Edit>Replace and Replace # with = 
  • Turn a List Upside-Down: 1. Copy the list to another location using Copy, Edit>Paste Special>Value., 2. Now select all data in the list, go to Tools>Options>Custom Lists., 3. Ensure the list address is in the "Import list from cells:" and click "Import"., 4. Now go back to the column next to your list and in the top cell place the LAST entry from your list., 5. Now in the cell below, place the, second last entry., 6. Select both cells and double click on the Fill Handle (small black square bottom right).The list should now be reversed. You could now also sort you original list using Data>Sort>Options, nominate your list then sort! 
  • Formula Errors: Whenever typing one of Excels functions (especially nested ones) into a cell always use lower case. This way when you push Enter Excel will capitalize only the names of the functions you have entered correctly. 
  • Entering Named Ranges Into Formulas: When you write a formula, sometimes you want to use a Named Range as one of the arguments for the formula, but you cannot remember the name. In these times simply push F3 when you reach the argument that you want the Named Range in and Excel will display the Paste Name dialog. Click the name you want then OK. 
  • Optional Function Arguments: Sometimes you may not be sure what arguments in a function are optional and which are not. If your using the Paste Function (Function Wizard) then the non-bolded arguments are optional. 
  • Sort by more than 3 Columns: Excels sort feature only allows to nominate up to 3 columns to sort by, here is how to get around this. The key to this is sorting by the last key first and working back to the first key. Say you data is in Column A:E and you want to sort by A, B, C , D then E: 1. Select all of Columns A:E, 2. Go to Data>Sort> sort by C then by D then by E, 3. Click Sort, 4. Now again with Columns A:E selected, 5. Go to Data>Sort> sort by A then by B, 6. Click Sort

Navigation

  • [Ctrl]-[Home] takes you to A1
  • [Ctrl]-[End] takes you to the last data cell in your worksheet
  • Move to next sheet -- [Ctrl]-[PgDn]
  • Move to previous sheet -- [Ctrl]-[PgUp]
  • The VCR buttons to the left of the sheet tabs let you navigate through sheets.
  • You can right-click on the VCR buttons and from the resulting menu select the sheet you want to view.
  • To move to the next window (open workbook) -- [Ctrl]-[Tab]
  • Tools | Options | Edit tab has a dropdown for specifying the direction the cell pointer moves when tapping [Enter] after entering data in a cell.
  • The Status Bar, at the very bottom of the screen usually says Ready in the lower lefthand corner. It provides useful information. One useful feature is that when a block of cells is selected the SUM of the cells will appear in the Status Bar. Right-click the SUM in the Status Bar and you can choose another function to apply to the selected cells. You can't do anything with this result, however, except view it in the Status Bar.
  • Window | Split or Window | Freeze Panes will divide the window above and to the left of the current cell pointer position. This will allow column and/or row headers to remain displayed in one section of the window while you scroll and move through data in another section of the window.
  • You can also split the window by dragging the little gray bar above the up arrow in the vertical scroll bar and/or the little gray bar to the right of the right arrow in the horizontal scroll bar.

Selecting Cells

 

  • Holding [Ctrl] while using mouse selection lets you select a non-contiguous block of cells
  • Click the mouse once in the upper lefthand corner cell of a block of data you want to select, then hold the [Shift] key down when you click the mouse on the cell that defines the lower righthand corner of your block
  • Use the keyboard -- [Shift] combined with any keyboard movement selects
  • [Shift]-arrows
  • [Shift]-[Ctrl]-arrows
  • Select the current block of data -- [Ctrl]-*
  • Once you've selected a block of cells, pointing at the border of the selection will turn the mouse pointer to an arrow, this is the move cursor.
  • To move cells, click and drag, clicking when you have the move cursor/arrow.
  • If you hold [Ctrl] before click-drag moving cells, you can copy the cells.

Entering Patterned Data

  • Type the first two values in a sequence, select the two cells, click and drag using the Fill Handle to extend the pattern
  • If you right-click and drag with the Fill Handle, when you release the mouse you'll be presented with a list of options for the type of pattern/sequence you want to use.
  • Select a block of cells (contiguous or noncontiguous), type the value to enter, [Ctrl]-Enter will put that value in all the selected cells
  • Use a formula and then once all the data is entered, select the results, use Edit | Copy, click in the upper lefthand corner of the destination, and use Edit | Paste Special as Values
  • If you'd like to randomize your data, one option is to create a column of random numbers using the RAND() function and then sort your data with the random number column as your sort field.

Excel Functions

  • The IF function is frequently useful -- IF(CONDITION, action or value if CONDITION is true, action or value if CONDITION is false)
  • If you have standard text to enter, maybe one of 5-10 possible entries, maybe using the INDEX function would help.
    • List of possible values in E1:E9
    • If you want the first value, you enter 1 in B1, 3rd value in the list, enter 3, ...
    • Copy this formula down Column C -- =INDEX($E$1:$E$9, B1)
    • Once you have the values you want in Column C, Add a column, and then use Copy | Paste Special | Values to copy the formula results to your new column
  • MATCH provides the complimentary operation for INDEX - given a value you want to find, look it up in an array of possible values, and return the index number for that  value's location in the array. MATCH (location of possible values, lookup value, match type). If match_type is omitted, it is assumed to be 1. If match_type is 1, array of values must be sorted in ascending order and    MATCH finds the largest value that is less than or equal to lookup_value. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order. If match_type is -1, array of values must be sorted in descending order and MATCH finds the smallest value that is greater than or equal to lookup_value.
  • The INDEX function technique above can be combined with List Box or Combo Box tools from the Forms (or Control Toolbox) toolbar. Once you've added your List Box or Combo Box the key is to right-click the Box and choose Format Control... In the Control tab you specify (using the example above), Input Range: $E$1:$E$9 Choosing a value from the list will populate the cell specified in Cell Link, say: B1 You'd still have to convert this number to a value in another column using the INDEX function discussed above. The List Box or Combo Box will only populate one cell with a value.
  • If you need to calculate or dynamically enter a cell reference for a formula, INDIRECT is a useful function.  SUM(INDIRECT("A"&D5):A15)  would concatenate A with the row number specified in D5 and sum in column A from that row through A15.
  • Close relatives of the INDEX function are VLOOKUP, HLOOKUP, and LOOKUP
  • For Example:
  • The lookup table of the INDEX example would be extended to two columns. Column D would number the values in Column E, that is, Column D contains the numbers 1 through 9. In Column B you enter the number of the value you want to pick from the lookup table.Column C contains the formula: =VLOOKUP(B1,$D$1:$E$9,2) This translates to ==> Lookup the value in B1 in the first column of the table in $D$1:$E$9, find the value in a row in the table, return the value in the 2nd column of the table.

Formatting

  • Format | Conditional Formatting might be useful for error checking (invalid values could show up formatted in red)
  • If you have multiple conditions specified, the conditions are evaluated from the top of the list. Once the cell satisfies a condition it applies that formatting and doesn't continue down through the rest of the possible conditions.
  • To find cells that are formatted with Conditional Formatting use Edit | Go To... | Special and choose the Conditional formats radio button. To find cells with identical conditional formats to the selected cell, click Same below Data validation. To find cells with any conditional formats, click All below Data validation.
  • Tools | Options | Calculation | Precision as Displayed  checkbox could prevent you from falling into a potentially embarrassing "rounding error" situation 
  • Want to transpose your columns to rows or rows to columns? The transpose feature in Office 97/2000 is in the Edit | Paste Special dialog box.
  • Select the cells to transpose, Edit | Copy
  • Right-click on the upper-lefthand corner of a range of empty cells that can accommodate the transposed result.
  • Choose Paste Special and check the transpose checkbox
  • Selecting cells and tapping the Delete key only deletes the cell contents, equivalent to Edit | Clear | Contents
  • Edit | Delete "chops out" the cell and asks you how you'd like to fill in for the missing cells
  • Edit | Clear | Formats will let you delete the formatting of the cell, e.g., borders, fonts, numeric formatting, ...
  • There may be certain portions of the worksheet that you'd like to protect from any possible changes. By default all the cells in the worksheet are locked but the locks are ignored. Tools | Protection | Protect Sheet activates recognition of the locks. Before using Protect Sheet you would unlock all the cells you want to be able to edit when the rest of sheet is protected -- Select Cells, Format | Cells | Protection tab and uncheck the default lock. Then use Tools | Protection | Protect Sheet.
  • If most of your cells are going to be unprotected with just a few protected --
  • Use [Ctrl]-A to select all the cells in the sheet
  • Format | Cells | Protection tab and uncheck the default lock
  • Select the cells you DO want to protect
  • Format | Cells | Protection tab and CHECK the lock back on
  • Tools | Protection | Protect Sheet

Miscellaneous

  • Electronic Post-It notes might be useful to make comments on your worksheet, Insert | Comment. For more information see: http://www.cof.orst.edu/net/software/office/tips/#Comments
  • Options for printing comments with the sheet are in: File | Page Setup | Sheet | Comments option dropdown
  • Data | Text to Columns is a useful tool for separating into multiple columns text that is currently contained in one column. For example, maybe you have social security numbers in column A and you just need the last four digits of the SSN. Data | Text to Columns would let you separate the SSN into two columns.
  • Sometimes you may want to combine into one column text that is currently contained in two columns. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand. This formula in C1 would return the contents of A1 followed by the contents of B1: =A1&B1
  • If you want to include a literal character, say a space, use the form: =A1&" "&B1
  • The Wizard used in Data | Text to Columns is the same tool you have available for opening text files you want to import into Excel. File | Open with some text file will bring you to Step 1 of the Data Import Wizard.
  • Use File | Page Setup | Page tab | Fit to checkbox if your printout is extending just beyond one page
  • To move or copy an entire worksheet to a new workbook:
  • Open the original workbook and choose Edit, Move Or Copy Sheet
  • When the Move Or Copy dialog box opens, click the arrow at the right side of the To Book list box. Choose New Book from the list.
  • If you want to copy the sheet, select the Create A Copy check box and click OK.
  • Excel moves or copies the selected sheet to a new workbook.
  • Sometimes codes used in data entry need to be sorted in some order that is not alphabetical or numeric. You can define a custom sort order in Tools | Options | Custom Lists. Rather than Adding the list, the easiest way to enter the values is to Import list from cells. To use your list, Data | Sort | Options button has a dropdown where you can choose your list for the sort order.

 

0 comments:

Post a Comment





Follow my blog with Bloglovin
Powered by Blogger.