100 level Archives - Page 2 of 3

20Jun 2013 by Charlie Nichols No Comments

Print Column Headings on Every Page in Excel

100 level, Excel

Print column headings on every page in Excel so that every page printed has the labels at the top of the columns. This will save a bunch of flipping back and forth to keep track of what you are reading on subsequent pages. The few steps below are specifically for Excel 2010, but they’ll general apply to your version of Excel too. The key is to get to the Page Setup dialog box where you will specify which row contains your column headings. How to print column headings on every page in Excel 2010 In Excel 2010, go to the Page Layout tab and click Print Titles. This is in the Page Setup group on the ribbon. Go to the Sheet tab on the Page Setup dialog box. Here, you…

24Apr 2013 by Charlie Nichols No Comments

How to use Excel’s conditional formatting feature to detect potential typos

100 level, Excel

Do you need to quickly identify values that are unique from a list within Excel? One reason for uniqueness of a value may be it's misspelled or there has been an entry or typographic error on the data. Excel makes it really easy with its conditional formatting feature. It takes just a few clicks. First, just highlight the values that you want to detect unique values, possibly misspellings or typos in. Next, click Conditional Formatting up in the ribbon. Then click Highlight Cells Rules, then click Duplicate Values. Change the value "Duplicate" to "Unique" in the dialog box that pops up next. Then click OK. Voila -- the unique values in your list are highlighted for you. There you go.

22Apr 2013 by Charlie Nichols No Comments

How to quickly and easily detect duplicate values in an Excel list

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Detect Duplicates in Excel Column Do you need to quickly detect duplicates in Excel columns? Excel makes it really easy with its conditional formatting feature. It takes just a few clicks using built-in capabilities of Excel. Here is a quick overview to see first hand how to do it. First, just highlight the values that you want to detect duplicates in. You will need to be sure to highlight the range because otherwise you'll be trying to detect duplicates within a single cell which will not work. Also note that the matching will be done for the entire cell rather than a subset or substring within the cell. Next, click Conditional Formatting up in the ribbon. Then click Highlight Cells Rules, then click Duplicate Values. You can just…

12Apr 2013 by Charlie Nichols No Comments

How to locate the last cell of your worksheet

100 level, Excel

You might find that you’re worksheet prints a bunch of extra pages for no reason, or you wind up with an Out of Memory error message, or you may have an abnormally large file or other weirdness. This might just be because there is some information or formatting in a distant cell that you no longer need or was accidentally set. To find that last cell to take a look press the following keys together. CTRL SHIFT END If you find unneeded info or formatting in that cell, or you wind up way beyond that end of the data you’re using you can clear out the excess rows or columns to make Excel consider your worksheet smaller. To find out how, see how to clear unwanted rows or columns. There…

12Apr 2013 by Charlie Nichols 1 Comment

How to reset or clear the last cell in Excel to make your worksheet smaller

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] If you find yourself needing to clear out a bunch of unwanted rows and/or columns in Excel you can do that pretty quickly with the following 2-phase approach. Phase 1, clear unwanted columns Highlight the first of the columns that you want to clear out. This will be the first one to the right of the edge of the data you care about. Highlight the column by clicking on the column header (the letter name of the column). Press the following keys together. This will highlight the entire remaining right side of your worksheet. CTRL SHIFT RIGHT-ARROW Now click Clear All. You'll find this option on the Home tab on the ribbon, in the Editing group, in the Clear item drop down list. Phase 2, clear unwanted rows…

12Apr 2013 by Charlie Nichols No Comments

How to get more out of Excel’s Auto Fill feature

100 level, Excel

Quick basics Excel has a feature called Auto Fill that lets you start a series and then Excel can automatically fill in the rest of the cells by either repeating the series, or incrementing or decrementing some value. If you haven’t used it before, it can save a bunch of time with your data entry. To use the feature just highlight a bunch of cells, grab the fill handle, and drag your selection to more cells. The fill handle is that heavy dot that shows up in the lower right-hand corner of your selection. Getting more Excel has a number of Auto Fill options available, depending on the type of data you have in your selection. The options are listed below. Copy CellsFill SeriesFill Formatting OnlyFill Without FormattingFill DaysFill WeekdaysFill…

05Apr 2013 by Charlie Nichols 1 Comment

Open xlsx files in Excel 2003

100 level, Excel

Do you still have Excel 2003, but somebody sent you a file from Excel 2007 or later? Need to open xlsx files in Excel 2003? Good news, you're not stuck - you're in luck. You can open xlsx files in Excel 2003 To open xlsx files in Excel 2003, Microsoft has an upgrade package called the Office Compatibility Pack that lets you open files in later versions of the Office apps. That's cool, because it saves you from having to upgrade the copy of Excel you have just to work with the new file formats. One thing you don't get is the new functions that might be used in the newer files. These would be worksheet functions that were added as part of the newer version of Excel. Here's a…

05Apr 2013 by Charlie Nichols No Comments

How to turn on the Developer tab in Excel

100 level, Excel

By now I'm guessing you've read some instructions that have asked you to click the Developer tab in Excel. But, you don't see it right? You need to turn on the Developer tab in Excel. Here are the few simple steps to enable the tab. There are steps for both Excel 2007 and 2010. Microsoft moved the option around a bit between versions. Steps to turn on the developer tab in Excel 2007 Click the Office Start button, then click Excel Options. Click on the Popular section in the left navigation, and then click the checkbox for Show Developer tab in the Ribbon. Click OK. The above will show the developer tab in Excel 2007 for you. Steps to turn on the developer tab in Excel 2010 Click File, then…

04Apr 2013 by Charlie Nichols 1 Comment

How to enable the Analysis ToolPak Add-in in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Lots of cool analysis capabilities are available when you enable the Analysis ToolPak in Excel. Here are the few simple steps to enable the features. Just which set of steps you use will depend on which version of Excel you have. Excel 2007 steps to enable the Analysis ToolPak Click the Office Start button, then click Excel Options. Click on the Add-ins section in the left navigation. Make sure Excel Add-ins is showing in the Manage drop-down box, then click Go. The Add-in list will show up. Click the checkbox next to Analysis ToolPak. Click OK. The above will enable the Analysis TookPak in Excel 2007 for you. Excel 2010 steps to enable the Analysis ToolPak The steps for Excel 2010 are basically the same, but the windows have only a slightly different…

04Apr 2013 by Charlie Nichols No Comments

How to change the case of your text in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Three functions to change the case of text in Excel You can easily change the case of text in Excel. Unfortunately, Excel doesn't support an in-place way of converting text from one case to another as you can do in Word, Excel's Office sibling. Out of the box, Excel provides a few worksheet functions that give you a basic level of conversion. But since they're functions, you can't change the text in place. You need to put the new value in another cell or use in another formula. Here are three text case conversion functions you can use. UPPER LOWER PROPER UPPER and LOWER do what you'd think. They convert to all upper or lower case text. PROPER will add an initial capital letter at the beginning of…