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! 

Click here for the SoccerLeague.accdb database which forms the basis of the Introduction to Access 2007 course.

For additional Access 2007 training, check out my video training links below.  They are not directly related to this course; the examples come from a different training series.  However, every concept applies to Access 2007.  Viewers interested in seeing how things are done rather than reading how to do them can benefit by following along.  The price is certainly right… after all, it’s FREE!!!

The videos are listed in the order in which they should be viewed, subsequent videos building upon previous ones.

Note:  headphones or speakers required

Online video (44 min) – Create file, table and fields; enter and delete data. Change primary key, modify field properties. Import Excel data into Access; add to imported field & modify. Establish tables relationships and create queries.

 

Online video (52 min) – Create calculated queries, use expression builder. Use functions & data arithmetic. Total row & grouping.  Sample Quiz

 

Online video (41 min)- Reports, grouping, sorting, calculation. Query-based report, summary, layout changes & modifications.

Use this data to explore Appending and Importing data into Access.

Skate shop wisdom « Smurf on Spreadsheets

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 advantage and expand your own knowledge base today.  You’ll be glad you did.

Top 10 Online Resources to Learn and Master Microsoft Excel: Training and Tutorials

Business road warriors frequently place calls to numbers they do not have in their cell phone contacts.  Verizon 411 (aka ‘directory service’) calls are very handy, but also very expensive.  Waste money no more!  Try 1-800-GOOG-411 to get the same results entirely for free.

It’s very simple.  Create an entry for Google (or whatever you wish to know it by) and enter the number 800-GOOG-411 or 800-466-4411.  An automated voice will answer the call and ask for city and state (same as 411) and the business type (pizza) or business name (Peacock Cleaners).  Then you will have it dialed and connected automatically for you!! How cool is that.

Follow this link [click here] to see the actual Google page, or press play below to get a better idea of this service.

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 topic.  The length is perfect.  You don’t need to invest tons of time, and you won’t be all over the map.  One quick, simple concept added to your arsenal daily!

[Click here] to view the entire list.  I have pasted a sampling of topics below.  Enjoy and learn!!!

•  Calculate Sales Over Quota – An introduction to using the IF function to change…
• Text Calculations in Excel – Use Excel to calculate text. In this podcast, lear…
• Break apart a column – Use the LEFT function to return just the left-most…
• MID Function – If you need to extract characters from the middle …
• Get characters up to the Dash – The Left, Mid, Right functions are great, but what…
• Get characters after the Dash – Yesterday’s podcast showed how to use LEFT and FIN…
• Get characters after the 2nd Dash – Another twist today – how to get characters that o…
• Use TextToColumns to Parse – Use the TextToColumns Wizard to eliminate the need…
• Joining Text and Paste Special Values – Have you ever deleted a column, only to find out t…
• Random numbers in Excel – Sometimes you need to create random numbers for a …
• Sort to a Random Sequence in Excel – Another great feature of the Rand Function is sort…
• Create a Dice Simulation in Excel – Use the random number generator to create dice in …
• Creating Random Letters in Excel – Not only does the Rand Function create/sort random…
• Convert Numbers to Text – Are your numeric entries sorting in the wrong plac…
• Calculate a Loan Payment – Buying a cool new car or a house? Use Excel to cal…
• Calculate Many Scenarios for Loan Payments – Want to see calculations of a loan payment if diff…
• Get Help on Any Function While Entering a Formula – Instant help with function arguments: Do you remem…
• Finding Functions – Confused when your manager asks you to do some str…
• Three Methods of Entering Formulas – There are THREE popular ways for entering formulas…
• AutoSum – Use the AutoSum button to quickly total columns of…
• AutoSum Woes – Why the AutoSum icon doesn’t always work. Learn th…
• AutoAverage? – Yes – you can use the AutoSum icon to find average…
• Who Taught Excel How to Count? – Why does Excel say I have two cells when any seven…
• Number My List – How to add numbers to a list so that the numbers w…
• Rank Function – Do you need to determine where you rank in a list?…
• Sorting With A Formula – Need a Top Sellers Report? Use the VLOOKUP Functio…
• Kissing Your Sister – Ties can really screw up the RANK function. Learn …
• Commenting a Formula – Have you just racked your brain to figure out an o…
• Calculate a Moving Average – When you are trying to analyze product demand for …
• Straight Line Estimate – One of the simplest ways of forecasting is to assu…
• Multiple Regression – Why does Sally’s ice cream stand either have a boo…
• Debug a Formula – Do you have a complicated formula? Use the F9 key …
• Quick Calculator – One of the simplest tricks in Excel. See how to do…
• Formula Frustrations! – Frustrated because you’ve entered a formula and in…
• Calculate Percentage of Total – How to enter a formula to calculate percentage of …
• Running Percentage of Total – I hate it when a calculation requires two differen…
• Powers in Excel – Exponents gave me headaches in math class, but the…
• The Root of the Problem – How to calculate square roots, cube roots, fourth …
• Growth Rate – Your company is growing, but how fast? The formula…
• Like a Big Pizza PI – Whether you are figuring out the best pizza deal o…
• Starbucks or the Lottery? – I spend a couple bucks a day on Starbucks, UNLESS …
• Steven Spielberg – Question: What do Steven Spielberg, an 8th grade m…
• Max, Abs and Forecast Error – Bill Jelen is the only guy on the planet who wants…
• Round Up to the Nearest 5 – Today, MrExcel handles yet another maddening reque…
• Floating Along – Why does that simple price show up as $2.099999999…
• When 2 + 2 = 5 – Are you updating numbers in your worksheet but the…
• Order of Operations – Excel has a certain order that it will perform a c…
• Claiming Dependents – It is tax time, but THESE dependents are not the k…
• Finding Precedents – When you have a large formula, all of the cells th…
• More Precedents – Use the Formula Auditing tools to find all of the …
• Slow Motion Instant Replay – Rather than drawing a million blue arrows on your …
• Time Formats – Learn the obscure formats to make your time calcul…
• Time Entered as Text – Someone entered a million time values as text. How…
• Time as Text Part 2 – In today’s episode, someone keyed in hundreds of c…
• Relay Races – 10 runners each ran for about 3 minutes and Excel …
• Daily Dates to Month – Make a series of daily dates appear as a month. Ep…
• Daily Dates to Months for Real – OK….the trick that I used in podcast 210 happene…
• Last day of February? – How can you convert a date to the last day of the …
• Set up a Simple Time Sheet – What? I work hard all week and Excel can’t add up …
• Lucent or Lucille Ball – Learn how to compare two list to find an exact mat…
• Using VLOOKUP instead of MATCH – Use VLOOKUP to find the exact match when comparing…

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]

I mentioned in a previous post that RoboForm is a ‘must have’ product-especially if you check into and out of countless password protected web sites daily.

So when I stumbled upon the FREE look-alike ‘Sxipper’ recently I had to give it a try.  The interface is not quite as flexible as RoboForm, but Sxipper definitely gets the same job done–allowing you to log in to countless web sites without entering usernames and passwords every time. 

It’s FREE and unobtrusive, can import your information from RoboForm, and goes an interesting step further… there is a user community ‘learning’ feature that ‘teaches’ the application to recognize forms with different input boxes. 

Where RoboForm may only recognize ‘Name’, ‘LName’ or ‘LastName’, ‘FName’ or ‘FirstName’, Sxipper has the ability to associate and then ‘report back’ that some obscure web form wants ‘Surname’, and it should then provide ‘LName’ from your user identity.  A pretty neat feature–every user who participates helps the data engine get ’smarter’, thereby increasing the usefulness of the program.

Check the links below for more information of this innovative product.

Version: 2.2.1

Requires:
Firefox 3, 2.0, 1.5

Flock 1.1+

Want to run the bleeding-edge version?
Get the development build.

Sxipper is a free Firefox add-on that saves you time by keeping track of an unlimited number of usernames and passwords as well as the personal data you share every day over the web.

Our mission is to make your interactions with the Web simple, keeping your data
private and secure.

• Privacy – Sxipper protects your personal data by keeping it secure on your computer and retrieving it for you when you choose to share it online.
• Simple Set up – No account set up required! Just install, let Sxipper gather data for you, and you’re ready to go.

• Passwords – Sxipper remembers your usernames and passwords letting you login with a single click.
• Form Fill – Learning from other users, Sxipper knows how to complete forms with 100% accuracy.
• OpenID – Sxipper recognizes OpenID forms and helps you manage your OpenIDs.

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 you study it and apply a few good examples.  Here is a great explanation worth 3 minutes of your time to put this incredible tool into your box! 

[Click here]

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 and format. Numbers are not formatted.
Good. Text is clean. Formats give information.
Minimise colour codes.

For readability, do colours help or hurt? Would you write a business letter like the spreadsheet below?

In the first sheet, the formats do not give us any information.

To look professional, strive for a clean look rather than distracting big titles and heavy colours. To get attention on a few specific cells, use a logical layout and concise labels.

A graph that is cool looking, but gives little information is what Edward Tufte calls a duck, so named for a restaurant in California that is shaped like that. The whole thing is one big decoration. This sheet looks like a duck – it is mostly decoration.

Below is the same sheet, reformatted. Inputs are boxed in yellow, though lately I’ve switched to a calmer grey.

Below, at left, there is so much formatting, it is meaningless.

In the centre, I turned it off the format – it’s a relief to get rid of the noise.

At right, I formatted the constants in grey. Why are there constants on the Formulas sheet? Shouldn’t they be on the Inputs sheet?

Format constants differently than formulas.

Think about how the format will look to your audience. On a laser printer, grey prints beautifully, but yellow becomes white. Blue prints as black.

I used to like all constants in yellow, but I’m switching over to putting formulas in grey. Most of my spreadsheets are constant, so if the constants are formatted, then most of the spreadsheet is formatted, which looks too heavy.
Yellow constants. But yellow does not print. Grey formulas, which print. Less format, cleaner, calmer.

What’Best! makes decision variables blue, but blue looks black when printed. So I also make decision variables italic, when the spreadsheet must be printed in black and white.
Pretty, but it hides the business logic. It’s a duck! The sheet is formatted to look cool, not to help you understand the spreadsheet. The green title block actually uses a graphic frame.
Calmer format. Much more fits on a screen. Once the formats are killed, we see this so-called template is trivial.
Try to have uniform column widths.

Readers try to find meaning in column widths. There should be none.

If we see a special column width, it jumps out. So we look for information in it. This suggests you should not use thin columns simply as dividers. It will confuse your reader. Instead, try to make columns all about the same width. This is also visually more pleasing.
Bad Good

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, to create a “free zone” and an “out of limits” zone in you worksheet, you can use the following method.
Suppose you have a standings table like in my post on ranking a list with RANK and SUMPRODUCT functions:

Now suppose you want to limit the user to the range B5:J19, protecting the auxiliary columns and preventing him to move to any cell outside this range. To do this follow these steps:

1 – Open the Control Toolbox bar

2 – Press the Properties button. The Sheet1 Properties window will open

3 – In the ScrollArea line enter the range B5:J19

Finally, close the Properties window. From this moment there is no way to select any cell outside the range, until you open the properties window again and change or delete the range.

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 kept in various workbooks, where tabs are corresponding to months, from January to December:

Tabs in Excel 2007

Rather quickly, you get to the point where you no longer can see all the tabs in one view. Of course, you can use the built-in tabs navigation buttons, and go the next, previous, first, or last sheet, but wouldn’t it be great if there was a way to see all the sheets and be able to click on the one you want to work on? Well, there is one, and it has been there since Excel 4 (I think). Simply right-click the tabs navigation buttons and a floating list of all the worksheets in the workbook will appear, as pictured below. It is especially useful when sheet names are long. I use this time and time again, and find it the best way to navigate complex spreadsheets. Now seems just the perfect time to also remind you of two great keyboard shortcuts: CTRL+PageUp activates the previous sheet in your workbook, while CTRL+PageDown activates the next one.

Floating list of worksheets in Excel 2007

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 all the training and classes no one showed me this little tip:

When you Click the Office Button, the documents you were working on most recently show up under Recent Documents. What I didn’t know, until a short time ago, was that if you click the push pin to the right of the name of the document, it “pins” that document to your recent documents so that it always shows up. What a find!! This is great for forms and documents that you need often. I work in accounting, and we use a lot of check requisition forms and form letters that are connected to databases.

Right you are Misty! That is a great find. So, in other words, YOU have control over what’s in that Recent Documents list.

Note Recent Documents, the display of the last few files you opened in that program appears from the Office Button Office button image in Office 2007 versions of Access, Excel, InfoPath, PowerPoint, Visio, and Word. Push pin imageThe adorable push pin that Misty is talking about is only available in Access, Excel, PowerPoint, and Word. When a document has been pinned to the Recent Documents list, the pin button looks like a push pin viewed from the top. Push pin image

“A human being has a natural desire to have more of a good thing than he needs.” — Mark Twain

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 with four identical quarterly worksheets.  All formats and functions are entered at once across the five sheets! 

Next, I show you how to ‘roll up’ the amounts from sheets Q1, Q2, Q3, and Q4 into the Annual tab–all with one function that is copied into the remaining cells.  How cool is that! 

To view the video in a separate window [click here].

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 the Zip code: =RIGHT(A2,5)
Screenshot // Splitting a Full Address into Three Separate Cells

Splitting a Full Address into Three Separate Cells

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 Insert menu, or right click on the cell or shape and select Hyperlink from the pop up menu. You can enter a cell reference in the current workbook, browse to another workbook, a different file, or a web page, even enter an email address and subject line. You can also edit the hyperlink text for a hyperlink in a cell.
Hyperlink to a Worksheet Range

This is almost a trivial exercise. Enter the cell reference, or select a defined name. If you browse to another workbook, Excel shows an Address field in the Insert Hyperlink dialog to the path and file name of the linked workbook. To link to a particular location in the other workbook, click on the Bookmark button. Now browse to the desired location as you would within the same workbook. Excel appends the sheet name and cell reference to the workbook path and name in the Address field:

C:\My Documents\Book.xls#Sheet1!A1

Hyperlink to an Embedded Chart

You can’t hyperlink to a chart directly, but you can link to a cell on the worksheet in which the chart is embedded. Use the cell under the top left corner of the chart, or the entire range under the chart.

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.

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 of referring to the coordinates. You can set up as many named cells as you wish.

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.

	YES	NO
Number Registered	√
Paid in full		√
Attending seminars	√

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.  It’s as simple as selecting the F4 key.

This is one of those understated CompuFiciency tricks.

Repeat the Last Action

Actions are generally defined as things you do with menus and title bars, like merging, formatting, running macros, etc, not typing in a cell. Sometimes you may like to repeat the last action easily, without going back through the menus again, there’s a shortcut!

Shortcut: Repeat the Last Action

Keys to press:
F4

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 a necessary evil; the quicker you get it out of the way the better.  That’s where this book and seminar comes in.

With the programmability of computers and the decreasing hardware costs there is far more can be done using our home-based PCs to manage a small business–more than most people realize.  Understandably so.  But don’t let that lack of awareness prevent you from reaping the rewards.

Let us show you how just how affordable and within reach using existing technology can maximize your effectiveness and minimize the time spent ‘running’ your business.  Even if you implement only one, two, or three suggestions, you will notice gains in efficiency.

And one step forward inevitably leads to another.

I make my eBook available to you for FREE because I need to share the thousands of hours and dollars I’ve spent becoming a small business computer expert.  Keeping these lessons learned to myself would be crazy when I know so-o-o many business owners struggle with the exact same challenges and limitations of time and money.

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 worth your effort. 

I can understand both reasons, perhaps the latter more than the former.  I only ask you to make yourself aware of likely lost opportunities.  You see, even simple macros can accomplish some incredible amounts of work–remarkably fast, incredibly accurately, and effortlessly repetitively.

Digest that last sentence, and then ask yourself this:  how much money (in wages paid) does your company spend having someone who daily, weekly, or constantly spends significant time retooling data before they are able to get some useful analysis out of it?  Probably more than you know or care to admit (I’ve seen it far too often). 

Because you may not have been introduced to the power of macros and the nuances of VBA, you are missing out on huge cost-savings opportunities.  Even if you legitimately conclude it’s not worth your time to learn VBA don’t let that deter you. Seize the power of macros by out-sourcing.  You would be surprised how quickly and inexpensively a VBA consultant like MrExcel [consulting link] could solve some of these costly recurring business needs.   The ROI and future cost avoidance could be well worth stepping outside of your routine and asking:  ‘Can we automate this?” 

Remember, a good consultant doesn’t cost your company money, they save you money!

Bill Jelen, aka MrExcel has another great 2 minute example to help shine a light on your own potential lost opportunities.  Let your mind wander the desks and cubes of your own workforce and see what similar repetitive tasks could benefit from automation. 

Learn Excel from MrExcel Video Podcast: Episode 871 – Finding Dates

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 trainees on to macros because the awesome power of macros only come to light their isolated usage.   In other words, what you need a macro to do is probably so unique it is unlikely to enthuse someone else.  I find it hard to create good generic examples when instructing.  What will interest you simply won’t interest others, and the only thing likely to wake you up to the power of macros is something that solves a problem of yours.

In the attached link, Bill Jelen has a great example of how to overcome horrendous spreadsheet layout with a simple recorded macro.  By the way, Bill has a whole series of 2 minute videos that are must-see for anyone serious about Excel in the business arena.  He is among the top authors and certainly www.MrExcel.com is the top web site for Excel users.  Learn Excel from MrExcel Video Podcast: Episode 873 – First Macro

For step-by-step, detailed actions to create macros in Word and Excel [click here].

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 mount holes already manufactured into the design.  Your task is simply find a mounting system suitable to your office/desk environment.  There are poles, swivels, telescopes, direct to wall, etc.  Google ‘flat screen monitor brackets’ and you will find an impressive variety spanning a range of prices.  Pick the one that matches your budget and workspace layout.

I purchased two mount brackets on EBay for $9.99 each–very rudimentary but all I needed.  What was for years a tight, keyboard nearly in my lap workspace is now a spacious work area. 

Two screens at perfect eye level, above the desk, and all for around $20.  Who wouldn’t make that investment! 

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 or assistant, etc.

http://phonetag.com/newsandevents.html is where you’ll fine this up-and-coming business dream!  And the price is right, too.

Formerly called SimulScribe, PhoneTag is a voice to text service. To be precise, PhoneTag converts your voicemail into text messages and then sends them directly to your mobile phone, Blackberry, and/or your email account.

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 where it’s due!

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 beginning analysis.  Learning these few Excel features and more about the power of macros will eliminate that drudgery forever.

Click the following link and go midway down the page to ‘An Excel Macro for Parsing Text Data’.  This will work for 10 rows or 10,000 rows, and can save you hundreds of hours!  [click here].