Quick Tips & Tricks For Excel 2007

Simon Lloyd · #1 05-01-2008, 03:35 AM

Submit your Tips & Tricks in this thread to help new users get up to speed!

Simon Lloyd · #2 05-01-2008, 03:38 AM

To Hide the Ribbon Commands:
Double click on one of the ribbon tabs, e.g Home, Insert, or Page Layout.

OR

Press the CTRL + F1 keys on your keyboard.
Only the tabs will be left showing above your spreadsheet.

To Show the Ribbon Commands Again:

Click on one of the ribbon tabs, e.g Home, Insert, or Page Layout.

OR

Press the CTRL + F1 keys on your keyboard again.

darkyam · #3 05-01-2008, 09:44 AM

Users new to 2007 should note that the file format has completely changed and that 2007 Excel spreadsheets (.xlsx) cannot be run on 2003. Also, if you want to include macros, you have to save as either .xls or .xlsm for a normal spreadsheet. The .xlsx format is far more compact than .xls; one of my spreadsheets shrunk from 20MB to 2.5MB when I upgraded it to .xlsx.
Spreadsheets now have 16,384 columns and 1,048,576 rows, as opposed to 256 columns and 65,536 rows in 2003 and before.
You can now have 30 (I think) nested IF functions, rather than only 7 in previous versions.
The Analysis Toolpak add-in is included in Excel 2007, so many date and other formulas are available out of the box.
Conditional formatting has been greatly improved. Users can create more than three conditional formats (not sure on the exact limit, but I've had over a dozen with no issues) and can choose whether the conditional formatting stops when a condition is found to be true or goes to the next rule. The options available in conditional formatting have been greatly expanded, allowing users to format based on top or bottom values (either absolute rankings or %), values above or below average, or unique or duplicate values, as well as applying a color scale that applies to each cell based on its ranking within the range.
The filter functions also have seen vast improvement. Instead of selecting only one value at a time to filter by, users can check or uncheck the values they want to see. If dates exist in the column, they roll up into months and years, making it easy to show or hide all dates in a certain time period.
There are also dozens of additional chart formats and options, allowing much better 3-D charting.
These are only the changes I've noticed most, by no means is it even close to complete.

ratcat · #4 05-07-2008, 02:30 AM

To find the recent document(s) that you had open.

Go to the Office button (found in the at the top on the left hand side of the excel window) -> Click on the button and then click on the workbook you desire located on the right.

To adjust on how many document you wish displayed go the Excel Options (located on the bottom right hand side) after clicking on the Office button. Click on the "Advanced". Scroll down to a heading named "Display" then adjust the field next to "Show the number of Recent Documents:"

Number of Documents showing: Min is 0, Max is 50

Pin the Document

If you have a recurring document(s) that work on over days and/or weeks, you can pin it to the Office button by clicking once on the pin on the right hand side of the name of the document (the pin will turn green). That document will stay there until the document is unpinned (the pin will turn back to gray).

darkyam · #5 05-08-2008, 11:28 AM

To get to a list of Options, click on the Office logo at the upper left-hand corner, then Excel Options. The "Popular" tab includes the option to show the Developer tab in the ribbon, which is necessary to record macros. There is also an Add-Ins tab to manage add-ins and several others that have considerably more options than in Excel 2003.

darkyam · #6 07-15-2008, 11:10 AM

There is one caveat about conditional formatting that you should be aware of: when you copy and paste conditionally formatted cells' formats, Excel seems to want to create an entirely new rule for that selection, rather than expand the selection to which the rule applies. Thus, it is better to highlight the entire selection that you want to be conditionally formatted and then use the $ appropriately in whatever formulas you have, as Excel will update them based on the location of the top left cell in the selection. Alternatively, you can create the rule on just one selection and then go to Manage Rules... and change the range to which it applies.

malang · #7 03-19-2009, 11:42 AM

Excel 2003 to Excel 2007 interactive Menu Finder

Here's a cool interactive guide that helps find common Menu items from Excel 2003 in Excel 2007:

http://9link.org/2007/excel2003and2007.htm

http://9link.org/2007/word2003and2007.htm

Logingue · #8 05-28-2009, 05:38 AM

Excel 2007 tips & tricks step-by-step tutorials at http://mister-excel.blogspot.com/

http://i533.photobucket.com/albums/e...screenshot.png

robinx1578 · #9 09-21-2009, 10:19 PM

This Quick Reference can help somebody familiarizing Excel 2007. If you need excel 2003 Quick reference let me know. Im glad to provide you a little help i can give.

@admin
Hope you wont mind if i upload this file. Based from the site its free to distribute. Hoping im not violating any rules in this forum. More Power to Excel Help Forum.

brack · #10 01-11-2010, 10:20 AM

I was creating an small invoicing system the other day and finally figured out a way to have excel prepopulate my invoices when I place a key into a certain row.

Here is the file I used...Populate-Cells.xlsx

Here is what the file looks like without comments...prepopulate-without-comment.jpg

Here is what the file looks like with comments...prepopulate-with-comments.jpg

Once an "x" is placed into the row of information you want to call up, the formulas do the work to get the info into one set of cells. From that set of cells, you can do the "copy" "paste special" and "paste link" into your template. Best of luck.

If you wish you can see the long boring tutorial at... http://blog.brackwho.com/2010/01/tut...lating-or.html

waldotp2 · #11 02-04-2010, 09:41 AM

Pls how can i combine two same worksheet to get only the unique values. ie i mean i need the values that are not existing within the two worksheets. Thanks

DonkeyOte · #12 02-04-2010, 10:23 AM

waldotp2,

Your post does not comply with Rule 2 of our Forum RULES.

Don't post a question in the thread of another member.

Also note this is not a "question" thread.

Welcome to the Excel Forum

Forum Statistics