Highlight the active cell in Microsoft Excel(Excel XP, 2003, 2007, 2010)

              It is often difficult in large tables to find the active selected cell. You want to configure Excel in a way that this cell always and automatically stands out from the rest of the sheet.

              When working on large tables, it becomes difficult to find the active cell. To solve this problem you can download a free add-in named RowLiner, which can be downloaded from http://www.cpearson.com/excel/rowliner.htm. Double click on the file to install it. Once installed, it can be accessed form the “Add-ins” tab from the menu bar. Once activated, it will highlight your active cell. To change its settings, click “RowLiner” in the “Add-ins” tab and select the “RowLiner Setup”. Here you have different options for rows and columns, as to where these are shown and how they should appear. To the right, you can control the structure of the active cell. Then confirm the configuration with “OK”. However, the “Undo” function is not available in active RowLiner. In case of extensive entries, you should deactivate RowLiner temporarily. For that, click “RowLiner” in the “Add-Ins” tab and deactivate the “Draw Lines” option.

Read More...

Visualize cell values with conditional formatting(Excel 2007, 2010)

              When making reports in Excel, you may wish to highlight certain cell values by using visual aids in the worksheet.

              Select the cells whose values you want to support visually and click “Conditional Formatting” in the “Home” tab. Here, you can select from several predefined formats. These include ‘Highlight Cell Rules’, ‘Top/Bottom Rules’, ‘Data Bars’, ‘Color Scales’ and ‘Icon Sets’. Under ‘Highlight Cell Rules’, you have the option to highlight data that is greater, lesser, equal to, or between the specified values. Apart from this, you can also highlight according to text, date and duplicate values. The ‘Top/Bottom Rules’ allow you to highlight the top ten, bottom ten, above average, bottom average and even top/bottom ten based on percentage.
               For representing the data in a more appealing way, you can even make use of the ‘Data Bars’, Color Scales’ and the ‘Icon Sets’. With the help of the ‘Data Bars’ you can make use of colored data bar within the cell. Here the length of the data bar corresponds with the cell value. So higher the value, longer will be the bar. You can also make use of the ‘Color Scales’ that displays two or three color gradients in the range of the cells, where the shade represents the value. Alternatively you can make use of the various icons from the ‘Icon Sets’ to represent cell data visually.

Read More...

Prevent undesired error messages in Excel 2007(Excel 2007 and 2010)

              You are searching for information in Excel from other table fields with Vlookup and want to adapt it. You often get error messages if the search term is not present. Here is an example for the functioning and advantages of “Vlookup”. For instance, you have a table that records names and telephone numbers. And if you include some of the names elsewhere in the sheet then you can enter the relevant number using the “Vlookup” function. “Vlookup” will search for the name accurately in the sheet. The formula used is as follows:
=VLOOKUP(H2, A1:B16, 2, FALSE) 
             Here H2 is the name, A1:B16 is the table field, 2 is the column that contains the numbers, whereas false will search for the exact corresponding name. In case of success the formula outputs the relevant number in I6 or else it will show “#N/A”. This is not an error, as it means that the name is not present in the list. If you wish to change this output to “Not Present!” for your convenience, then you can easily do so. Using “IfError”, you can change the output message to let’s say “not present!”, with the help of the following formula.
=IFERROR(VLOOKUP(H6, A1:B16, 2, FALSE), "Not Present!")

Read More...

Record the last modified date internally(Excel 2003, 2007, 2010)

             You want to keep a track of when your Excel sheet was last modified. You want Excel to insert and update the date automatically.
             As a solution for this, you need a VBA code in the working folder so that a macro automatically ensures an update. Open the relevant working folder for this and select the command"Tools → Macro →Macros”. From Excel 2007 onwards, activate the “View” tab in the taskbar and click the “Macros” icon. Now enter a macro name and click “Create” or if a macro already exists, click “Edit”. In the VBA editor, navigate to the “This working folder” entry in the upper left of the project explorer under “VBAProject" of the current file. Double click and open the relevant code window that is now ready for your entries.In the right combination field, select the “SheetChange” system procedure whereupon the relevant number of macros is added below. Add all the further rows as follows:

Private Sub Workbook _ SheetChange(ByValSh As Object, ByVal Target As Range) Application.EnableEvents = False Sh.Range("A1").Value = Date Sh.Range("A1").NumberFormat = "mm/dd/yyyy" Applications.EnableEvents = True End Sub
               The sample code is triggered on the current sheet by the result of the change. The value of the current date is then entered on the sheet in the “A1” cell. It is mandatory to switch the event processing off within the macros in this example; else, the change in the A1 cell automatically triggers the next SheetChange event. This would always lead to an endless recursions. Instead of a separate entry for every table, you can also access the document property and update it centrally in the same table. Then also specify the relevant work sheet, for instance:
Worksheet("Sheet1").Range("A1"). Value= ThisWorkbook. BuiltDocumentProperties(12)
               This command will change the A1 cell in Table1 and assigns it with the document property specified with the last change. This is the information that can be called up in the file properties under Windows. After entering the script, close the VBA editor and save the Excel file in your document folder. When calling up the sheet, ensure that the contained macros are activated; else, the security settings prevent the functioning of the macro.

Read More...

Display ‘&’ in the footer(Excel 2007)

             The ampersand sign (&) is a special symbol which is not printed by default.

             The ampersand sign (&) is a special symbol which is not printed by default. If you have ever tried to add an ampersand symbol in the footer, you might have also noticed that the ampersand sign disappears on print. If you need to print this symbol, you can do so with very little hassles. Open the workbook which requires to be printed, then on the status bar, click the ‘Page Layout’ option. Now place your cursor on any one of the three sections where you need to enter the "&" symbol. Now press [Shift] + [7] combination twice from your alphanumeric keypad. Notice the & sign appears twice (&&). Click outside the footer area, you will now notice the ampersand symbol in your footer.

Read More...

Value-based shading(Excel 2007)

              Differentiate between data without sorting it with the help of Conditional Formatting.

              If you need to differentiate between data with out sorting it, you can do it with ease in Excel 2007. Using the Conditional Formatting rules in Excel 2007, you can easily segregate even numbers from the odd numbers in your data. First select the cells with the data values, then go to the ‘Home tab’. Here under the ‘Styles’ group, select ‘Conditional Formatting’. In the dropdown list click ‘Manage Rules’ and then select ‘New Rule’. You will be asked to ‘Select a Rule Type’, here choose the ‘Use a Formula to Determine Which Cells to Format’ option. In the ‘Format Values Where This Formula Is True’ textbox, enter the formula as "=MOD(A1,2)-1" and select ‘Format’. Then click on the ‘Fill’ tab in the ‘Format’ dialog box and select the color yellow, press ‘Ok’ to continue. Then go back to the ‘Conditional Formatting Rules Manager’ and select ‘New Rule’. In the ‘Format Values Where This Formula Is True’ textbox, enter the formula as "=MOD(A1,2)-0" and select ‘Format’. Then click on the ‘Fill’ tab in the ‘Format’ dialog box and select the color orange, press ‘Ok’ to continue. Click ‘OK’ to apply the conditional formatting to the selected cells.

Read More...

Remove duplicates quickly and easily(Excel 2007, 2010)

             You want to remove duplicate entries from a large table and are looking for a quick and easy solution without any filters or complicated formula.

             Use the mouse and select the cell area from which you want to remove duplicate entries. Then click the ‘Data’ tab and then the ‘Remove duplicate’ button in the multi-function tool bar.
             In the following dialog, you can define the columns of the area to be included in the comparison of individual rows. All cells of the two rows should thus not display the same content due to which the rows become duplicates of each other. In the ‘Columns’ field, remove the checkmark in front of the columns which you want to ignore during the comparison and then click ‘OK’.
NOTE: If you do not include all columns in the comparison (when there are obvious differences between the individual rows), Excel always retains the first (the topmost) row out of the rows that have been identified as duplicate. This is important when the previously excluded cells are required later.

Read More...

Count a term in a particular cell area(Excel XP, 2003, 2007, 2010)

                You want to determine how many times a term is repeated in a specifi c area of a table, e.g. how many times the name of a city appears in a list.

                You would normally use an array formula to summarize individual functions and conditions for the area. The exact defi nition of the formula depends on whether you want to differentiate between the upper and the lower cases and whether you want exact compliance with the search result.
                The fastest way to get the desired result is using the array formula =COUNTIF(A5:E15,‘Text’). This will fi nd and count all results containing the search term, irrespective of the spelling. For example, if you are searching for the city Mumbai, then type the formula as =COUNTIF(A5:E15,‘Mumbai’).

Read More...