{"id":147,"date":"2013-10-30T09:08:42","date_gmt":"2013-10-30T03:38:42","guid":{"rendered":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/2013\/10\/30\/very-useful-microsoft-excel-tips-and-short-cuts\/"},"modified":"2021-12-05T12:00:54","modified_gmt":"2021-12-05T12:00:54","slug":"very-useful-microsoft-excel-tips-and-short-cuts","status":"publish","type":"post","link":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/2013\/10\/30\/very-useful-microsoft-excel-tips-and-short-cuts\/","title":{"rendered":"Very Useful Microsoft Excel Tips and Short Cuts"},"content":{"rendered":"<div><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-412 aligncenter\" src=\"https:\/\/rajeshbaranwal.com\/blog\/wp-content\/uploads\/2013\/10\/VERY-USEFUL-MICROSOFT-EXCEL-TIPS-AND-SHORT-CUTS-thebaranwal.png\" alt=\"VERY USEFUL MICROSOFT EXCEL TIPS AND SHORT CUTS\" width=\"800\" height=\"336\" \/>Friends,<\/div>\n<div style=\"text-align: justify;\">Now a days\u00a0<b>Excel\u00a0<\/b>is becoming a very useful<b>\u00a0part for Computers\u00a0<\/b>Users. In Excel\u00a0there are so many<b>\u00a0functions\u00a0<\/b>with the help of those one can do his job very easily and\u00a0comfortably\u00a0which is even impossible if someone try to do it\u00a0<b>manually<\/b>.\u00a0While\u00a0working\u00a0in\u00a0Excel. we need some\u00a0<b>shortcuts<\/b>\u00a0to do work fast. Here are some\u00a0Excel\u00a0Tips which could be very\u00a0<b>useful for<\/b>\u00a0doing job in Excel. Like as someone type some matter in Column mode but the Boss says to convert the Columns in Rows, then you don&#8217;t have to panic to type the matter again in Rows mode. Just use the following\u00a0trick\u00a0and convert it in just seconds.<\/div>\n<div style=\"text-align: justify;\"><\/div>\n<div>\n<ul>\n<li style=\"text-align: justify;\"><b>Convert Rows to Columns:\u00a0<\/b>You can convert rows to columns (and columns to rows) by highlighting the cells you want to switch around,clicking on Edit, Copy, selecting a new cell and then going to Edit, Paste Special\u2026 Finally, place a tick in the\u00a0<b>Transpose<\/b>\u00a0box on the dialog box and click on OK.<\/li>\n<li style=\"text-align: justify;\"><b>Calculate\u00a0time between dates:\u00a0<\/b>Enter in a cell<b>\u00a0<\/b><b>the formula<\/b><b>\u00a0<\/b>=A2-A1, where A1 is the earlier date, and A2 the later one. Don\u2019t forget to convert the target cell to number format \u2013 do this by highlighting the cell, clicking on Format, Cells\u2026, picking on the Number tab and selecting Number from the Category: list.<\/li>\n<li style=\"text-align: justify;\"><b>Enter URLs as text, not hyperlinks:\u00a0<\/b>To prevent\u00a0Excel\u00a0from converting written Internet addresses into hyperlinks, add an apostrophe to the beginning of the address, for example \u2018<a href=\"http:\/\/www.rajeshbaranwal.com\" target=\"_blank\" rel=\"noopener\">http:\/\/www.rajeshbaranwal.com<\/a>.&#8217;<\/li>\n<li style=\"text-align: justify;\"><b>Calculate\u00a0Running\u00a0Totals:\u00a0<\/b>Enter the numbers to be added in column A, say A1 to A5, then enter =SUM($A$1:A1) into column B. Highlight the cells beside the ones with numbers in (in our example, B1 to B5) and go to Edit, Fill, Down. This places the running total of the figures in A1 to A5 in the adjacent column.<\/li>\n<li style=\"text-align: justify;\"><b>Remove\u00a0Hyper links:\u00a0<\/b>From your work If\u00a0Excel\u00a0has already converted your written URL into a hyperlink, you can cancel it by right-clicking on the offending address and selecting Hyperlink, Remove Hyperlink from the menu that pops up.<\/li>\n<li style=\"text-align: justify;\"><b>Fit wide tables to the page width:\u00a0<\/b>To make your tables fit neatly on the page, click on File, Page Setup\u2026, select the Page tab, click on the Fit to:radio button and pick 1 page wide. Click on the tall box and press [Delete], leaving the box empty.<\/li>\n<li style=\"text-align: justify;\"><b>Hide your data from prying eyes:\u00a0<\/b>If you want to hide from view any sensitive data, highlight the relevant cell and click on Format, Cells\u2026 Click on the Numbers tab, select Custom from the Category: list, double-click on the Type: input box and enter ;;;.\u00a0Undo the operation to make your data visible again.<\/li>\n<li style=\"text-align: justify;\"><b>Use template\u00a0worksheets:\u00a0<\/b>Templates can save you considerable time when you\u2019re setting up a new worksheet. Click on File, New\u2026, select the Spreadsheet Solutions tab and choose a template from the list.<\/li>\n<li style=\"text-align: justify;\"><b>Access help for Lotus users:\u00a0<\/b>If you have converted some\u00a0worksheets\u00a0from Lotus 1-2-3 and find\u00a0Excel\u00a0confusing, you can access help specific to your situation by clicking on Help, Lotus 1-2-3 Help\u2026<\/li>\n<li style=\"text-align: justify;\"><b>Use\u00a0the formula\u00a0browser:\u00a0<\/b>Select a cell and click on the Paste Function button on the main toolbar. Pick the function you require from the list box and click on OK. Now highlight the cells on which you want the target to perform the function and click on OK.<\/li>\n<li style=\"text-align: justify;\"><b>Customise your AutoFills:\u00a0<\/b>If you use the same list over and over in different\u00a0worksheets, you might want to add it to your AutoFill list \u2013 this will save you heaps of time in future. Highlight your list, click on Tools, Options\u2026 and select the Custom Lists tab. Click on Import, then OK.<\/li>\n<li style=\"text-align: justify;\"><b>Use the Auto Calculator:\u00a0<\/b>If you need to\u00a0calculate\u00a0a sum based on a row or a column of figures and you can\u2019t be bothered typing in a\u00a0function, just select your figures and glance down at the status bar \u2013 you\u2019ll find the sum of the selected cells there.<\/li>\n<li style=\"text-align: justify;\"><b>A quick way to enter the time:\u00a0<\/b>To enter the current date or time click on a cell and type <strong>=today()<\/strong> or =now().\u00a0Excel\u00a0updates the result every time you open the sheet, so it\u2019s always current.<\/li>\n<li style=\"text-align: justify;\"><b>Enter a fixed time into\u00a0Excel:\u00a0<\/b>If you want Excel to enter the current date or time and fix it at that point \u2013 for example, to show the last date the sheet was modified \u2013 click on a cell and press <strong>[Ctrl]<\/strong> +<strong> [;]<\/strong> for the date and<strong> [Ctrl]<\/strong> + <strong>[:]<\/strong> for the time.<\/li>\n<li style=\"text-align: justify;\"><b>Find the currently active cell:\u00a0<\/b>If you have been scrolling around your spreadsheet and you lose your place, you can jump back to the currently\u00a0active cell by pressing the [Ctrl] + [Backspace] keys.<\/li>\n<li style=\"text-align: justify;\"><b>See the big picture:\u00a0<\/b>If you\u2019re working on a large sheet you might want to switch to Full Screen mode: simply click on View, Full\u00a0Screen. Click on it again to return to a normal window.<\/li>\n<li style=\"text-align: justify;\"><b>Fit text automatically:\u00a0<\/b>Make your work look neater by selecting the relevant column, and clicking on Format, Column, AutoFit Selection.<\/li>\n<li style=\"text-align: justify;\"><b>Fast copy, no clipboard:\u00a0<\/b>Pressing [Ctrl] + [\u2018] is a speedy way to duplicate\u00a0the formula\u00a0or figure in the cell above the one you\u2019re in.<\/li>\n<li style=\"text-align: justify;\"><b>Fast and easy multiple entry:\u00a0<\/b>If you need to enter a formula in several cells at once, simply select all the target cells, type your formula as normal and press <strong>[Ctrl] <\/strong>+<strong> [Enter]<\/strong>.<\/li>\n<li style=\"text-align: justify;\"><b>Pick cells for Auto Calculator:\u00a0<\/b>You can select unconnected cells by holding down<strong> [Ctrl]<\/strong> while you click on the individual\u00a0cells.<\/li>\n<li style=\"text-align: justify;\"><b>Hide comments to reduce clutter:\u00a0<\/b>You can hide notes and other comments by using the Insert Comment function.Highlight a cell, click on Insert, Comment and type your text. Click away from the input box when you\u2019ve finished. The comment will appear\u00a0whenever you pass your mouse over the relevant cell.<\/li>\n<li style=\"text-align: justify;\"><b>Re-colouring the grid lines:\u00a0<\/b>You can change the colour of the grid by clicking on Tools, Options\u2026, selecting the View tab, clicking on the Color: list box and choosing a new colour from the palette. Choosing white effectively removes the grid completely.<\/li>\n<li style=\"text-align: justify;\"><b>Angle your entries:\u00a0<\/b>To make\u00a0Excel\u00a0display the text in cells at an angle of 45 degrees, right-click on the toolbar, select Chart, and pick either of the ab icons on the new toolbar.<\/li>\n<li style=\"text-align: justify;\"><b>To any degree you like:\u00a0<\/b>If you want custom angles right-click on the cell and select Format Cells\u2026 From the pop-up menu, click on the Alignment tab, and then drag the Text pointer in the Orientation window.<\/li>\n<li style=\"text-align: justify;\"><b>Zoom in to your selection:\u00a0<\/b>You can have\u00a0Excel\u00a0display just the area you\u2019re working in by highlighting the relevant cells, clicking on the arrow on the Zoom box on the toolbar and selecting Selection from the list.<\/li>\n<li style=\"text-align: justify;\"><b>Another default entry direction:\u00a0<\/b>When editing cells the cursor moves down when you press [Return]. To change the direction click on Tools, Options\u2026 and select Edit. Click on the Move selection after Enter list box and choose another direction from the list.<\/li>\n<li style=\"text-align: justify;\"><b>Launch\u00a0Excel, open your file:\u00a0<\/b>Launch\u00a0Excel, open the file you use most often, and click on File, Save As\u2026 In the Save As dialog, navigate to the Xlstart folder in your Office folder and click Save. Now the file will open automatically when you launch\u00a0Excel.<\/li>\n<li style=\"text-align: justify;\"><b>Set decimal points:\u00a0<\/b>Click on Tools, Options\u2026 and select the Edit tab. Place a tick in the Fixed decimal tick box and use the arrows in the Places: input box to set the number of decimal places.<\/li>\n<li style=\"text-align: justify;\"><b>Calculate\u00a0the median value:\u00a0<\/b>To\u00a0calculate\u00a0the median value of a number of figures, say A1 to A10, enter =median(A1:A10) into the target cell.<\/li>\n<li style=\"text-align: justify;\"><b>#NAME! and #NUM! errors:\u00a0<\/b>Excel\u00a0returns a #NAME! or #NUM! error whenever a formula refers to nonexistent names or numbers. To sort it out, re-enter\u00a0the formula\u00a0correctly.<\/li>\n<li style=\"text-align: justify;\"><b>Quick sort your columns:\u00a0<\/b>The quickest way to sort a column into a hierarchy is to select it and click on \u00a0the Sort Ascending or Sort Descending buttons (the ones with the AZ arrows).<\/li>\n<li style=\"text-align: justify;\"><b>Quickly change cell dimensions:\u00a0<\/b>The quickest way to change a column\u2019s width or a row\u2019s height is to place your mouse pointer close to the join between two columns or rows, then click and hold down the mouse button, and drag the column or row to the desired size .<\/li>\n<li style=\"text-align: justify;\"><b>Erasing entries while typing:\u00a0<\/b>If you mistype your formula, press <strong>[Esc]<\/strong> to erase the cell\u2019s content.<\/li>\n<li style=\"text-align: justify;\"><b>Enter number sequences:\u00a0<\/b>Enter the first number in a cell, press the [Ctrl] key, and then click, hold and drag the cell\u2019s handle until you have\u00a0reached the desired number of cells.Release the mouse button first, and then the <strong>[Ctrl]<\/strong> key.<\/li>\n<li style=\"text-align: justify;\"><b>See formulas at a glance:\u00a0<\/b>You can view all of your formulas at once by clicking on Tools, Options\u2026, selecting the View tab, and placing a tick in the Formulas tick box.<\/li>\n<li style=\"text-align: justify;\"><b>Highlight cells of a kind:\u00a0<\/b>You can select all the cells of a certain type by using the Special functions. Click on Edit, Go To\u2026, click on the Special\u2026 button, choose the cell type from the dialog box and click on OK.<\/li>\n<li style=\"text-align: justify;\"><b>Switch off the zeroes:\u00a0<\/b>You can prevent zeroes from cluttering your sheets by clicking on Tools, Options\u2026, selecting the View tab, and unticking the Zero values tick box.<\/li>\n<li style=\"text-align: justify;\"><b>Protect your work:\u00a0<\/b>Open the sheet you wish to protect and click on File, Save As\u2026 Click on the General Options\u2026 item in the Tools menu (in the top right corner of the Save As dialog) and enter a password in the Password to open: input box. Finally, click on the Save button. You might want to make a note of the password before continuing.<\/li>\n<li style=\"text-align: justify;\"><b>Customize\u00a0your dates:\u00a0<\/b>You can customize the format of a date in a cell using the Custom cell formatter. To do this, enter a date in a cell, click on Format, Cells\u2026, click on Custom in the Category: pane, click on the Type: input box and press [M] repeatedly until the desired format is displayed in the Sample area above.<\/li>\n<li style=\"text-align: justify;\"><b>Quick column copy:\u00a0<\/b>You can fill out the cells in a column by double-clicking on a cell\u2019s handle.Excel will place copies of the clicked cell in each cell below, stopping when it reaches a cell with blanks either side.(Confused? Try this: enter a figure in cell A1, then enter figures in cells B1 to B5, then double-click on A1\u2019s handle.)<\/li>\n<li style=\"text-align: justify;\"><b>Merge cell contents:\u00a0<\/b>To merge the contents of cells A1 and B1, click on cell C1 and enter =A1&amp;B1.The result is not a sum but a text string, so merging 10 and 7 will return 107, rather than 17.<\/li>\n<li style=\"text-align: justify;\"><b>Use dynamic formatting:\u00a0<\/b>You can set Excel to alert you to critical figures in cells by setting it up so the colour or size of text in a given cell changes whenever a certain condition is reached. Select a cell (say, the cell containing the total of your bank balance) and click on Format, Conditional Formatting\u2026 Choose the conditions in the pop-up dialog (say \u2018Cell value is less than 0\u2019) and click on the Format\u2026 button. Now choose a new colour in the Color: list box and click on OK. Click on OK again to finish. From now on Excel will display your chosen figure in the new colour whenever the figure falls below zero.<\/li>\n<li style=\"text-align: justify;\"><b>Border connected cells:\u00a0<\/b>You can place a border around a group of related cells \u2013 all the totals, for example \u2013 by selecting them and clicking on Format, Cells\u2026, then selecting the Border tab and clicking on the Outline button.<\/li>\n<li style=\"text-align: justify;\"><b>Shrink entries to fit their cells:\u00a0<\/b>You can force Excel to display the full text of a cell by using the Shrink to fit option. Click on Format, Cells\u2026, click on the Alignment tab and place a tick in the Shrink to fit tick box. Obviously, the more text you have, the smaller it\u2019ll be, so this isn\u2019t practical for small cells with lots of content.<\/li>\n<li style=\"text-align: justify;\"><b>Create hyperlinks to support files:\u00a0<\/b>To place links in cells that enable you to load other documents with one click, enter a name for the link in a cell and press [Ctrl] + [K]. Click on the File\u2026 button. Navigate to the file you wish to link to. Double-click on it and click OK. From now on, whenever you click in that cell, Excel will jump to the file.<\/li>\n<\/ul>\n<p>I think these short cut functions of Excel will help you. \u00a0Kindly note that these commands are may not be workable in all excel version but most of versions have this features.Further there is only on method\/ procedure to solve one query is given though there are more than one methods in excel to do same thing.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Excel is becoming a very useful part for Computers Users. In Excel there are so many functions with the help of those one can do his job very easily and comfortably which is even impossible<\/p>\n","protected":false},"author":1,"featured_media":1372,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[42,30,43,24,44,25,35],"class_list":["post-147","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-gain-knowledge","tag-excel","tag-learning","tag-microsoft","tag-rajesh-baranwal","tag-shortcut","tag-the-baranwal","tag-trick"],"_links":{"self":[{"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":2,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/147\/revisions"}],"predecessor-version":[{"id":1374,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/147\/revisions\/1374"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/media\/1372"}],"wp:attachment":[{"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rajeshbaranwal.com\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}