Welcome to the Stark State College Excel 2007 Intermediate course by Brian Mickley. To better assist your learning, please download the workbook below. We will use this for the duration of the course, and it is available 24/7 via the web. The sheet DOES contain macros for instruction purposes, so the warning is appropriate. However, [...]
Archive of posts filed under the Excel category.
Training videos – Excel 2007
The outline below corresponds to a 5 week introductory Excel 2007 course Walsh University.
The training videos track with the textbook used in the course which I do not name to ensure no copyright violations occur.
Nevertheless, a tremendous amount of FREE training is here for the taking–for whole the world to enjoy!
Excel 2007 [...]
Excellent list of online Excel resources
You can’t learn too much about Excel! Most so-called ‘experts’ at the office are barely knowledgeable compared to all there is to know. The reason they are ‘local experts’ is because no one else around them knows all that much either!
What a shame, however. There is a tremendous amount of FREE help available. Take [...]
Mr Excel Podcasts
If you have never visited the MrExcel.com web site then you don’t know what you’re missing! Bill Jelen is among the top Excel gurus, and at his web site you will find answers to many of your thorniest Excel challenges.
Also available are 600+ 2 minute video training podcasts in which Bill explains a narrow Excel [...]
Consolidated list of Excel combo-key shortcuts
If you are a shortcut key junkie like me (or if you seek to become more compuFicient!!) then visit this link to scan for Excel key combinations to speed up your work even more: [click here]
Dynamic named ranges – Excel power trick
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 [...]
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 [...]
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. [...]
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 [...]
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 [...]
How to parse non-delimited text data using Excel macro
Continuing the Excel macro theme… many employees receive company data from mainframe systems–perhaps even a screen copy or web paste.
Often the data is not delimited or structured in any way, so it does not want to go into Excel columns. They must first spend significant time ’shaping the data’ into columns and rows prior to [...]
