Excel 2007 and 2010: Getting Legacy VBA to Run in Templates
Most of your old VBA code will still run in Excel 2007 and 2010 but you will need to save it into a new Excel template with the new file format that supports macros first. For instance, save the template as an XLTM (Excel Macro Enabled Template) file. The default template is XLT and that won’t allow macros to run.
Excel 2010: Sparklines
Excel 2010 includes a new feature called Sparklines which are tiny charts that fit into a single cell and plot data in cells from the worksheet. There are a host of formatting and styles that can be applied to them and they are really quite interesting. To insert sparklines, on the Insert tab, in the Sparklines group, click Line, Column or Win/Loss. Note that you cannot be in Compatibility mode (.xls). You have to be in a new file format in order to be able to work with the feature.
Excel 2010: Equations
The Equation tools for math that were available in Word 2007 are now available in Excel. A ribbon displays along with a host of tools for working with Binomial Theorem, Fourier Series, and more. From the Insert tab, in the Symbols group, click Equation.
Excel 2007: Remove Duplicates
In previous versions of Excel, you had to create formulas to remove duplicates. This changes in Excel 2007 where you can accomplish this same task in seconds and with very little technical expertise. First select the range that you wish to work with. Select the Data tab and in the Data Tools group, click Remove Duplicates. The Remove Duplicates box appears where you confirm which column values you wish to use for checking for duplicate values. Click OK to confirm.
Excel: Change Case
You can quickly change the format of information in cells by using a function such as Proper or Upper and then pasting the resulting values into the original cell over, thus replacing the contents. For instance, let’s say you receive a worksheet with data in column A that is formatted as all uppercase and instead, you require it to be in proper case. In an empty cell, just type =Proper( then point to the cell or type the exact address after the parenthesis and press Enter. The information from that cell will appear in proper case format. You can use AutoFill to drag down and continue the formula for the rest of the list. If you wish to change the list to Uppercase, substitute the word proper with Uppercase. The last step in the process is to copy the list with the correct format and choose to Paste the values only in place of the original list.
Excel: Format Anything as Text Upon Entry
Excel automatically evaluates cell information when you press Enter. For instance, if you type a web address, pressing enter automatically turns this information into a hyperlink. Dates equivalents are also formatted upon entry automatically. If you want the information to be treated strictly as text and not be converted, just type an apostrophe as the first character in the cell.
Excel: Quick Math
Excel has a built-in functionality to serve as a quick calculator - for more than just quick sums. Select cells that contain numbers only. For non-contiguous cells, press the Ctrl key and click to select. Look at the Status bar at the bottom of the window and you will see calculations. Alternate-click to change the type of calculation performed, alternate-click on the status bar and change the calculation to average, count, numerical count, minimum, maximum or sum. This was available in previous versions but not nearly as intuitive.
Excel: Extend Selection and Add To
If you are working with someone who has limited mouse proficiency, or if you have a large worksheet where you need to select non-contiguous cells, you can use Excel's little known Extend and Add feature. First, click in any cell that should be part of the selection. Press F8, then select the last cell in the range to be part of the selection. Press Shift+F8 and click other cells to add individual cells to the selection. Press Esc to leave Extend and Add To mode. This was also available in previous versions of Excel.
Excel: What does the # (number sign) mean when displayed in cells?
Most often, the reason for the # symbol displaying in a cell instead of the resulting value is that the column width is not large enough to display the contents. Resize the column and it should fix the problem. There is one other reason why the # symbol would show instead of numbers. If you have a formula that subtracts one time value from another and the result is a negative number, the number symbols will appear.
Excel: Entering Fractions
If you type a fraction into a cell that has not been preformatted for fractions, you will get a date value instead of what you typed. For instance, type 1/2 in cell A1, press Enter, and Excel translates this into January 2. To return a fraction value, type 0 1/2 and press Enter. This is the same for previous versions of Excel.
Excel: Line Spacing
Word rules over Excel when it comes to being able to control line spacing options. But let’s say you want to quickly print a worksheet and just need some extra spacing between the lines of data. You can easily accomplish this and make the spacing temporary or permanent. Select All (the box between column A and row 1) and then drag the row down to the desired height. As you drag, you can even see the measurement. If you are looking for one and a half times the line spacing, or double-spacing, you can adjust as needed. Check out your handiwork in Print Preview or in Backstage View (2007/2010) before printing and adjust if necessary. To return to the original position, select all again and double-click the line below row 1. This resizes to fit the contents.
Excel 2007: A message appears when attempting to add rows or columns
Excel 2007 gives you a lot more real estate to work with. Typically you can insert rows and columns at will with one exception. If you are in the last available column (XFD) or row (1,048,576) you get a message box warning.
Excel 2007: More than 255 worksheets
You can have up to 255 worksheets in a new workbook. However, once the workbook is created, you can add more worksheets up to what the amount of your computer's memory can handle.
Excel 2007: That sheet is "History"
Can you insert a worksheet and name it history? You cannot, because this is a reserved name for a track changes or history feature in Excel. If you attempt to copy or add a worksheet with an existing sheet name, Excel will put a number next to the worksheet name.
Excel 2007: Turn of the gridline display"
On the View tab, in the Show/Hide group, uncheck the option for Gridlines. This affects the active worksheet only.
Excel 2007: Merge Styles
Excel 2007 includes more style centric formatting. If you have styles in one workbook and you want to add them to another, you can use the Merge Styles command.
First, open both workbooks and then move to the Destination workbook where the styles should be copied to.
On the Home tab, in the Styles group, click Cell Styles, then Merge Styles.
The Merge Styles dialog box displays where you can select the workbook from which to copy styles and click OK.
Excel 2007: What happened to the Function Wizard?
It's actually still there, it's now called the Insert Function dialog box now.
Excel 2007: Generate Random Numbers Between Two Stated Values
There are websites that will generate random numbers for you; however, you can use an Excel function for the same purpose. Click in any cell and type =RANDBETWEEN(1,50) and press Enter to generate a random number between these two values. Use AutoFill to add random numbers to other cells on the worksheet.
Excel: Quickly Clear All Spreadsheet Formatting
Press Ctrl+A to select all cells in the worksheet. From the Edit menu, choose Clear, Formats.
Excel Insert a Return into an Excel Cell
To insert a hard return in a cell press ALT+Enter.
Excel: Shortcut to Insert Time/Date in Excel or Access
Use the following shortcut keys to insert the current time and date in a Microsoft Access table or Excel worksheet.
• Current date: Press Ctrl+; (semicolon)
• Current time: Press Ctrl+Shift+; (semicolon)
• Current data and time: Press Ctrl+; (semicolon), press the Spacebar and then Ctrl+Shift+; (semicolon)
Note: When you insert the date and time using this tip, the information remains static. To update this information automatically, you must use the TODAY and NOW functions.
Excel: I want to print my worksheet but hide all of the values that are under $500. Is this possible?
Yes. You can do this by applying conditional formatting. Select the entire worksheet (press Ctrl+A). From the Format menu, choose Conditional Formatting. Under Condition 1, set the following: Cell Value Is - Less Than - 500. Click the Format button. Click the drop-down arrow under Color and select White. Click OK twice to close the dialog boxes. All numerical values under 500 are formatted as white and will not show when printing unless your background is set to a darker color. In the event that you have color applied the worksheet, set the font color for the conditional the same as the background color.
Excel: Color code sheet tabs?
Yes. Follow the steps below:
- Alternate-click the tab you wish to color code and choose Tab Color from the shortcut menu.
- Select the color you wish to apply and click OK.
Excel: Concatenate Cells (Combine Cell Data)
To join two or more cells, follow these steps:
- In cell A1, type your first name.
- Type your last name in cell A2.
- Click in Cell A3 and type =A1&" "&A2. Press Enter.