Few things add more power to Excel spreadsheets than dynamic named ranges. Why? Because they allow you to create highly complex formulas that change as often as necessary using a word (‘Expenses’) instead of hieroglyphics (Sheetxx!A$139:$RJ$125).
And one key to making them dynamic is the =OFFSET( )function. A bit confusing at first, but very manageable if [...]
Archive of entries posted on November 2008
Dynamic named ranges – Excel power trick
Excel formatting rules to guide your designs
The New Guidelines for Writing Spreadsheets
Rule 3: Format for description, not decoration.
A format is descriptive if it gives the reader immediately recognizable information. A format is decorative if it displays no information.
Decoration in a spreadsheet is like crayon in a business letter. Format to explain to the reader, not to impress the reader.Bad. Meaningless colour [...]
Limiting a worksheet scroll area in Excel
The JLD Excel Blog – Tips and Help for MS Excel: May 2006
When you want to protect formulas or data in a worksheet you can hide columns and/or rows, hide formulas and more and then apply the Tools—>Protection menu.But if you want to limit the users to a certain area in the worksheet, that means, [...]
list of all the worksheets in the workbook will appear
Office Hours: Hidden gems in Excel 2007 – Help and How-to – Microsoft Office Online
So many worksheets, so little space
Have you ever had an Excel workbook with multiple sheets, those sheets having names other than the proverbial Sheet1, Sheet2, and Sheet3? My wife’s business has many great examples of this, where business data is being [...]
Recent Documents
10 Office features you need to know about – Help and How-to – Microsoft Office Online
* Misty Kush, working from Norcross, Georgia noticed a tiny little icon in Word that ended up saving her time and irritation:
Hey there Crabby!! I’m fairly new to Word…and in [...]
Create and calculate spreadsheets in 3D
For LARGER IMAGE, click the black square to the right of the video length.
This 11.5 minute video shows some awesome techniques within easy grasp of Excel rookies that could be put to use by nearly every business or family I can think of.
First, I show how you can simultaneously create an summary sheet along [...]
Separating the addresses shown in column A into three columns, one each for City, State, and Zip Code
Microsoft Excel Tips from Excel Tip .com / Free Excel Spreadsheet Help / MS Excel Tutorial
Separating the addresses shown in column A into three columns, one each for City, State, and Zip Code.
Solution:
Use the LEFT, MID, RIGHT, and FIND functions as shown in the following formulas:
To extract the City: =LEFT(A2,FIND(“,”,A2)-1)
To extract the State: =MID(A2,FIND(“,”,A2)+2,2)
To extract [...]
Hyperlinks in Excel
Hyperlinks in Excel
A hyperlink is a convenient way to allow the user of a workbook to instantly access another place in the workbook, another workbook, or a file associated with another application. A hyperlink can be inserted in a cell or a shape in Excel. Select the cell or shape and select Hyperlink from the [...]
CompuFiciency FREE eBook
By all means, click the link below to receive your personal FREE PDF file with TOC, internal and external hyperlinks, and a wealth of FREE CompuFiciency information.
CompuFiciency Training.pdf
CompuFiciency Training.pdf
Make a Numerical Constant
Make a Numerical Constant – PC Magazine
Got one number you use over and over in a spreadsheet? Make it a constant. On the Formulas tab, click Define Name, and give the select cell a name (like “taxrate”). Put a number in that cell. Now you can use the name in a formula (like =(b4*taxrate) instead [...]
How to use √ checkmarks in Excel or Word
We have all seen the software comparison tables depicting side-by-side version indicating what’s included and what’s not.
A simple √ checkmark is often the best way to convey information, and it is quite simple to do. Simply change to Webdings font and then type the lowercase letter ‘a’ where you want to place the √ checkmark. [...]
Repeat the Last Action
Here’s a handy trick to know.
We often find ourselves making an editing decision that we then need to reapply throughout an entire document or spreadsheet. For instance, you may want to change the indent of your paragraphs.
As long as you don’t make any other actions, you can repeat that action throughout your document. [...]
Small business CompuFiciency FREE eBook
Expertise takes a long time to achieve and no one can be expert at everything.
You are an expert in your business and people are willing to pay for your services and knowledge.
Anyone in business knows there’s a big difference between ‘doing’ the business and ‘running’ the business. More often than not running the business is [...]
Solve a business problem–fast, accurately, and repetitively
If you are debating whether or it’s worth your time learn macros or delve a little deeper into the Visual Basic for Applications (VBA) programming language, perhaps it’s because you simply don’t understand how it could solve business problems for you, or perhaps you question whether the payback for your learning time is legitimately not [...]
MrExcel demonstrates recording your first macro
Macros save a ton of work–in the right place! You may rightly ask ‘But, what is a macro?’ A macro is simply a set of actions that you instruct your application to take. In this case we’re using Excel, but the exact same principles apply to Word (and others too!).
I find it hard to turn [...]
Reclaim your desk – mount the monitor
If you really want to maximize efficiency in the office environment, there are a few no-brainer steps you need to take. Besides a cordless keyboard and mouse, and double-monitors (the obvious choice being flat-screen monitors), the next improvement is to mount your monitors above the desk surface.
Most all newer flat-screen monitors come with industry-standard bracket [...]
Voicemail as emailed text – what a great solution
This is the next best thing to a memory! Each voicemail is transcribed and sent to your email account and your cell phone.
Not only does it take seconds to scan to the most important parts, it gives you a written archive for those things you would like to retain, act upon, forward to a colleague [...]
2009 calendar on multiple worksheets – by Microsoft
Visit the Microsoft site [click here] to download an Excel workbook nicely preformatted as 2009 calendar, one page for each month.
It’s business office uses are many–even if the most simple argument is just saving money.
There are also other templates available, and I got this idea from the AutomateExcel.com web site. I like to give credit [...]
