ExcelHub - Learn Excel Easily

Excel Tip #023 Prevent Automatic Hyperlinks Having Hyperlinks in your worksheet that allow you to quickly and easily open other related workbooks, can save you a lot of time. You don't have to browse to the folder where the workbook is located. Many of us use hyperlinks to jump from one sheet of the workbook to another sheet (or) to another workbook. But it can be annoying sometimes when you simply want to enter the data (e.g. website (or) email address) in your worksheet and it automatically gets created as hyperlinks. By clicking the Undo command (on the Quick Access Toolbar) or pressing CTRL+Z immediately after you create the hyperlink, you can quickly disable that hyperlink but that is not going to be a permanent solution. If you just don't want hyperlinks created in your workbooks every time you type a website (or) email address, you can disable this feature in Excel's options. From the File tab, click Options, Proofing , then click the AutoCorrect Opt...

Excel Tip #022 Search Backwards in Excel Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it. Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select , then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one. But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values? You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time. Select a cell below the range you want to search ( CTRL+END is a shortcut you can use to quick...

Excel Tip #021 Quickly Identify Column Differences If you need to compare two or more columns of data, there are various methods you can use in Excel to check for differences. Sorting, Conditional Formatting and using formulas such as MATCH or VLOOKUP are just a few of them. For years, whenever I needed to compare two columns of data, I used a simple formula such as =B2=C2 that would give me TRUE if the cells are the same and FALSE if they aren't. But there's and even easier and faster way identify differences in columns of data without using formulas. Select the columns to compare Press press CTRL + \ (or press F5, Special.., Row Differences , OK). Cells in the 'non-control' column(s), whose values don't match the values in the control column, are selected Add a background Fill color to make the selected cells more visible For 2-column and multiple row comparisons, Right-click one of the highlighted cells and choose Filter, Filter by Selected Cel...

Excel Tip #020 Keyboard shortcuts for formatting A lot of us use keyboard shortcuts but when it comes to formatting a spreadsheet, these shortcuts go for a toss as formatting relies on mouse. But there are few simple & effective shortcuts which would help us a lot. Below are some... CTRL+1 – Format anything This shortcut is powerful. Select anything (cells, chart objects, drawing shapes, pictures etc.) and press CTRL+1 to instantly launch format dialog box. ALT EST (or ALT HVE) – Format painter If you want to copy the formatting from one thing to another (like formatting of a bunch of cells to another range, a chart to another chart), you can use Format painter. Simply copy the original object (CTRL+C), select the target object and press ALT+EST (one key after another). You may also use ALT+HVE (again one key after another) in all modern versions of Excel. ALT HH – Fill color Now comes the tricky one. If you want to fill some color in a chart object, drawing shape,...

Excel Tip #019 Quickly locate active workbook's folder We will be working on (or) will be having multiple workbooks open at a time. Sometimes I want to know the folder location for the file that I am working on. And sometimes I need to go to that folder to access other related files. A cool trick that I find handy involves adding the Document Location command to my Quick Access Toolbar (QAT) . Add the Document Location command to your QAT Right click anywhere on the Ribbon and select " Customize Quick Access Toolbar " On the " Choose commands from " dropdown, select All Commands In the list of commands (on the left side of the dialog), scroll down and select the Document Location command Click the Add>> button and click OK Document Location command in QAT Note : You can see the document location (or) the folder path only if the workbook is saved The " Document Location " command is not only useful to show the full pat...

Excel Tip #018 Print your Worksheet pages in the order you want Did you know that you can print your worksheet pages in any order you want? Yes, instead of printing a worksheet as pages 1-2-3-4, you can print pages 4-1-3-2, or any other order you want, without having to reorganize your worksheet. You may have a simple solution, why can't we just change the page order and then print. That's a good answer but what if the worksheet is a client report and it is only in read only format. All you have to do is to hold down the CTRL key and select each area you want to print for each page, in the order you want them to print. In this example, let's say you need to print pages 4-1-3-2 in that order. Before you begin, you may want to save your workbook if you don't plan on keeping this new print order. That way, if you make any changes to the current print area, you can close and re-open your workbook without saving those changes. You may find it easier to do th...

Excel Tip #017 Limit # of characters in a cell We would have come across situations where we want to restrict the end user from typing lengthy sentence in a cell which would then get itself autofit (or) sometimes we don't see the entire comment if the adjacent cell has data in it. To overcome this situation, we can limit the end user to have only certain number of characters that can be typed in a cell. In our example, the sales rep address should not be greater than 20. So we are limiting the range of cells to be greater than (or) equal to 20. For ease, I have created a column name length which displays the length of the address for each sales rep. Example - Limit # of characters in a cell You can see that the address of the sales rep Walker (in cell B10) is greater than 20. So we are going to give a constraint. Here's how you can limit the number of (text) characters that can be entered in a range of cells: Select the range of cells you want to limit to ...