Photography Tips for Amateurs from an Amateur

am-a-teur : ˈamətər; -ˌtər; -ˌ ch oŏr; - ch ər noun a person who engages in a pursuit, esp. a sport, on an unpaid basis. ORIGIN late 18th cent.: from French, from Italian amatore, from Latin amator ‘lover,’ from amare ‘to love.’ Source: New Oxford American Dictionary

I am an amateur photographer.  It is not my profession.  I am not formally trained in it.  I have never owned a single lens reflex camera.  And I am not expert enough to describe f-stops.

But I do enjoy taking photographs; the derivation of the word amateur from the Latin word for lover is fitting.  I find today's point-and-shoot cameras are quite powerful and take great photos.  And I prefer to strive for the perfect shot at the time I take the photo so there is no or limited need to alter the photo after it is taken.

This posting shares some of my tips on taking photographs.  I use photos I have taken as examples.

My tips generally follow the keep it simple philosophy.  While I am not opposed to the alteration of photos after they are taken with tools like Photoshop, and I do find some of these enhancements can be artistic, I personally have not taken that approach except for an occasional crop or auto enhance.  The photos presented below have not been altered.

TIP 1: TAKE PHOTOS, NOT SNAPSHOTS.

That is, give some thought to your photo and do not snap a shot hastily.

I bought my first camera in 1994 just prior to a trip I took to the Amazon in Peru.  It was from this first trip that I learned the difference between the two.

I have saved the following two photos to remind me of this tip.  Below is the photo I took of a fisherman casting a net on the Amazon River.

After coming home and sending out this photo to my tripmates, I received this photo below from a fellow traveler with a note stating she had taken the same photo as I did.

True, we were looking at the same water, the same fisherman, the same scene.  But the two images are quite different.  Her photo is what I would call a "snapshot".  It has several people in the shot in addition to the fisherman of interest.  The net is half-cast.  I am even in the photograph taking the same shot!  (Yes, that is me on the far right with the backpack.)

My photo is focused on the fisherman.  I positioned myself to be closer to him, and I zoomed in more.  I waited until the net was fully cast before taking the shot.  I wanted the expanse of the river so I put him in the bottom right of the frame.

For me, the top one is a photo.  The bottom one is a snapshot.

TIP 2: HAVE THE PATIENCE TO WAIT FOR THE RIGHT SHOT.

The previous example showed that good photographers must have patience and anticipate when it is the right time to take the photo.

There is no question luck can also play a factor in taking good photos.  I have hiked in places where I was able to take a photo of a beautiful valley.  Hikers who came just a few minutes after me saw nothing but fog.

But patience can often help you take better photos even in unlucky situations.  This photo was taken during a cloudy hike on the Inca Trail in Peru.  I saw a slight break in the clouds and waited for it to open just enough and pass by a mountain peak before I took the photo.

TIP 3: GET UP EARLY IN THE MORNING TO TAKE PHOTOS.

Actually, take photos whenever it is the right time.  But do not forget the morning as one of the times to take photos.  Here are just two of the reasons.

First, you can avoid crowds and take shots without people that may distract a shot.  Here is a photo I took of the World War II Memorial in Washington, D.C., USA, early in the morning.  I wanted to take a photo of the memorial without people blocking the symmetry of the shot.

Second, mornings can have some special qualities, just like dusk can give you beautiful sunsets.  Here is a photo of the Washington Memorial in the early morning dew.

TIP 4: TAKE PHOTOS OF THE SAME AREA AT DIFFERENT TIMES OF THE DAY BECAUSE DIFFERENT LIGHTING MEANS DIFFERENT PHOTOS.

If you have the luxury of visiting a place multiple times, take photos each time.  Do not assume you have already taken the photo.  The lighting can make a difference.

Here is a photo of the Washington Memorial taken later that same day.

TIP 5: INCLUDE A FAMILIAR OBJECT IN PHOTOS IN WHICH YOU WANT TO EMPHASIZE SIZE OR SCALE.

Here is an example of what not to do if you want to convey size.

This is the seed pod of a baobab tree.  While it is a nice photo and has a good composition, it does not convey the size of the seed pod.  Is it larger than a plum?  Smaller than a loaf of bread?  You cannot tell.

Here is a photo of a baobab tree that grows from that seed.  Look at the man to the left of the tree.  It shows that it is a very large tree.  In hindsight, I should have had the guide park his jeep next to the tree to really emphasize the size of the baobab as the man is difficult to see.

The picture of this outcropping from Halong Bay in Vietnam requires the fishing vessel to the right of it to give a sense of how tall this island is.  I was fortunate the boat was there.

The woman in the bottom right gives an indication of how large the red doors are.

Here is another example.  This termite mound is almost twice the height of the man standing next to it on the right.

And here is one more example.  The man on the boulder helps emphasize our small size next to this waterfall.

TIP 6: USE THE MACRO SETTING ON YOUR POINT-AND-SHOOT CAMERA.

The macro setting is usually marked with a flower icon. This allows you to take close-up photos of objects like flowers.  Photos taken with this setting give a nice blurry and soft quality to the items in the foreground and background.

These were all taken with the macro setting on a point-and-shoot camera.

Some tips on using the macro setting include making sure you have put it in macro mode, making sure the camera lens is not zoomed so that it can focus correctly, and pressing the shutter button half-way to focus on the portion of the object you do want in focus.

TIP 7: CAPTURE EMOTIONS.

I find this is best done when people are not posing and are being their real selves.  You do not know when vivid emotions are going to be expressed, so you have to be ready.  And have a little luck.

Weddings are wonderful opportunities to capture joy.  Here are a couple of examples I have taken.  I was at the right place at the right time.

TIP 8: IF IT MUST BE POSED, BE CREATIVE SO THE PHOTO DOES NOT FEEL STAGED OR FORCED.

One technique is to not tell the subject when you are taking the photo.

Let them have fun and position themselves as they would like be. These three siblings naturally posed this way, and I took the shot immediately as they settled in before counting to three.

Incorporate other objects in the photo to create a relaxed or whimsical setting.

And for babies, do not wait for a perfect shot.  Take it immediately when they look your way.

TIP 9: TAKE ADVANTAGE OF DRAMATIC LIGHTING.

Many point-and-shoot cameras do not do well in low light and the results can be grainy. Oftentimes you need a tripod or they come out blurry.  Experiment with your camera and see what works.  Try different settings, including the night setting.

But don't avoid taking low light photos.  Sometimes they come out in your favor.  Here is a photo with candles.

In the photo below I was trying to capture the silhouette of a baobab tree as the sun went down along with the moon up above. Unfortunately, I could not move the moon lower toward the horizon or get closer to the tree. But the lighting still works to dramatic effect.

TIP 10: TAKE CLOSE-UPS, CAPTURING ONLY PART OF THE SUBJECT.

In the Forbidden City in Beijing, there is a bronze male lion with a ball under its paw to signify imperial power.  I took a photo of the ball and paw rather than the full lion.  Part of the reason was there were too many people posing with the lion. The other part was I thought it made for a more interesting shot.

TIP 11: BE ADVENTUROUS TO CAPTURE SHOTS FROM UNIQUE PERSPECTIVES.

This is a picturesque photo of Machu Picchu, a very typical photo most visitors take and one that should not be missed.

But if you climb the mountain in the back--Huayna Picchu--you can get a unique photo of Machu Picchu from an different vantage point.

TIP 12: HAVE YOUR CAMERA AT THE READY FOR THE UNEXPECTED.

I turned the corner on this hike to encounter this sheep.   We both were startled.  The camera was already in my hand.  I snapped immediately before I came to my senses and reversed my course.

I looked down over a railing into a pool at a temple in Vietnam.  I was surprised to see a turtle down below.

TIP 13: LOOK UP.

Actually, look all around you to find interesting views.  Here is a photo of giant sequoias taken by getting in a patch of these trees and aiming skyward.

Tip 14: REQUEST A WINDOW SEAT AND LOOK OUT OF THE AIRPLANE WINDOW.

When you take photos from airplane windows, turn your setting to landscape mode (the mountain icon) so the camera does not try to focus on the window.  Also put your camera lens right next to the window so it does not pick up any reflections from the inside of the plane.

I was very fortunate to take this photo as we were flying over the Andes.  I had an aisle seat and a woman let me switch with her so I could look out for a few minutes.  Perfect timing.

Here is a sun setting into the clouds.

And one final photo of volcanoes when flying into Managua, Nicaragua.

Happy snapping!

Single-Purpose Devices Replaced by a Smartphone

Smartphones, like the iPhones made by Apple and the phones utilizing Google’s Android operating system, have been big news the last couple of years.  The prices are also big, particularly when compared to “traditional” cell phones -- those single-purpose devices that primarily serve as a phone and texting device.  The cost of a subsidized iPhone 4 (with 32 GB) or a Motorola Droid X is $199 at the time of this writing (July 2010). Subsidized means a two-year contract with a wireless provider is required to obtain the subsidized price.

But the price of a smartphone should be put into perspective.  These phones are really highly mobile computers, not quite as powerful as laptops, but in some ways even more functional than laptops because of their unique hardware capabilities (see this posting on the hardware capabilities of Apple’s iOS devices for an in-depth description.)  The unique hardware capabilities, combined with their small form factor (fits in a pocket) and sophisticated software, allow smartphones to replicate the functionality of several single-purpose devices as well as provide new classes of functionality.

Here is a thesis:  A smartphone can be cost-justified, and perhaps be a money-saver, when compared to the cost of buying several single-purpose devices.  The assumption, of course, is the user would be purchasing some or all of these separate devices in the absence of having a smartphone and has not spent the money on acquiring them yet.

This posting presents this thesis by:

• Describing the different categories of costs associated with smartphones
• Presenting a list of single-purpose devices a smartphone can replace
• Comparing the total costs of these single-purpose devices to smartphones to test the thesis
• Describing additional cost-saving features and benefits of smartphones that may also help justify the purchase.

The thesis is applicable to several smartphones on the market, including the iPhone 4, HTC Evo 4G, and Motorola Droid X, among others.  I have used the iPhone 4 costs, features, and apps in this posting as the smartphone for comparison against single-purpose devices.

The information in this posting will be updated as more single-purpose devices that can be replaced by a smartphone become known and are logged.  Please send me an e-mail if you have suggestions or use the Feedback link in the menu.

COSTS ASSOCIATED WITH SMARTPHONES

There are five types of costs associated with a smartphone.  These should be kept in mind in any smartphone purchase.

• The cost of the smartphone hardware.  This is a one-time cost.

In this thesis, the subsidized price of the smartphone is used as the hardware cost.  The "traditional" cell phones are assumed to have zero hardware costs—they are typically subsidized and given free with a contract.

• The cost of a data plan.  This is usually a monthly cost to send and receive data (provide connectivity) through cellular service.

In this thesis, I include this cost because some of the functionality to replace single-purpose devices require connectivity, and the smartphone should able to replace a single-purpose device almost anywhere that device typically works.

• The cost of a voice plan.  This is usually a monthly cost to send and receive voice calls through cellular service.

In this thesis, the assumption is a "traditional" cell phone is definitely one of the single-purpose devices being replaced.  Therefore, the voice plan is not included in the relative cost comparison, that is, the cost for a voice plan would still exist and be the same for either a "traditional" cell phone plan or a smartphone plan.

• The cost of the smartphone applications (app).  These costs vary, from free to several dollars, depending on the developer and/or the features provided.

In this thesis, free apps are used if they provide a roughly equivalent level of functionality compared to a single-purpose device.

• Subscription or other costs associated with the smartphone app or service.  Some apps may require a subscription to continue using the service or having the most up-to-date information.  For example, one iPhone car navigation app charges an annual fee if you want voice turn-by-turn directions.  As another example, in the United States, ATT charges a monthly fee to allow the smartphone to act as a cellular modem for a laptop (called tethering.)

In this thesis, any subscription or other costs are computed over a two-year period.

LIST OF SINGLE-PURPOSE DEVICES REPLACEABLE BY A SMARTPHONE

The following single-purpose devices (SPDs) have been categorized as being partially (but adequately) replaceable, fully replaceable, or fully+ replaceable by a smartphone.  This categorization is subjective based on my opinion.

Partially Replaceable	Flash drive Point and shoot pocket camera GPS device for driving Radio Portable DVD player Portable gaming device Landline phone Netbook eBook Reader
Fully Replaceable	Cell phone Small video recorder iPod Keychain light Wrist watch / stopwatch Compass Mobile GPS device for hiking/sports Level Pocket calculator Pocket voice recorder White noise machine Four-note ocarina Dog whistle Police band radio Notebook for notes
Fully+ Replaceable	Maps Travel alarm clock Pocket calendar Pocket address book Pocket dictionary Pocket thesaurus Recipe book Travel electronic chess game Dice Holy Bible

A spreadsheet has been created which lists these single-purpose devices and the reasoning for its categorization.  Click on the spreadsheet graphic to view the spreadsheet.

(Click to access the spreadsheet -- Located on the Replacement List tab)

The spreadsheet contains additional columns of information about each single-purpose device:

Column Name	Description
Single-purpose device (SPD)	The type of single-purpose device that can be replaced by a smartphone.
SPD example	A specific example of that single-purpose device.  A low-priced example has been chosen, typically with standard functionality for that SPD.
SPD cost	The cost of the single-purpose device.
Cost source and date	Where the cost was noted and date it was noted.  Amazon.com was used to find the costs for most of these SPDs because of their low prices and good reputation.
iOS app example	The name of a specific iOS app that provides the functionality of the SPD.  If a free app is available, it has been selected.
App cost	The cost of the app in Apple's App Store as of the same date noted for the SPD cost.
App other cost	Any additional costs associated with the app or service to allow replacement of the SPD.  This cost is the amount required to be spent over a 24 month period.
Replacement level	A subjective rating on the level of replacement of the SPD by the smartphone with the app installed.  Levels: Partially:  partially replaces the SPD, but the smartphone does an adequate job to serve as a replacement in most situations Fully:  fully replaces the SPD Fully+: fully replaces the SPD and even provides better functionality
True for iPod Touch 3rd?	An "X" signifies that an iPod Touch 3rd generation could be used to replace the SPD in off-line mode (no connectivity) or with Wi-Fi connectivity.  While not as powerful as a smartphone with cellular capability, the intent is to allow this spreadsheet to also be used in cost-justification of an iPod Touch.
Notes	Comments associated with the line item in the spreadsheet.

Care was taken not to overload this list and exaggerate the costs and comparison.

• Only single-purpose devices that could be used by a large population are included.  Niche devices, like oscilloscopes that can be replaced by a smartphone, are not included.
• Only one example for each group of similar types of single-purpose devices is included in the list.  For example, a smartphone can replace the cost of several board games, including checkers, chess, chinese checkers, go, etc.; however, only a portable electronic chessboard has been listed.
• Low-end or medium-end single-purpose items have been selected.  High-end models have not been chosen.  For example, there are several high-end ebook readers available; however, a smaller, lower-cost Sony model was selected.
• Discounted prices, not retail prices, have been used.  Specifically, Amazon.com was used to determine prices of most items, all at a discount.

The spreadsheet also contains a column at the far right titled Place "X" for Your Situation.  This will compute the total cost related to the single-purpose devices you choose to put in your own calculation.

RELATIVE COST OF OWNERSHIP COMPARISON OF SINGLE-PURPOSE DEVICES AND SMARTPHONES

A spreadsheet on a second tab labeled Relative Cost Comparison contains the cost comparison.  Below is the cost comparison if all of the single-purpose devices documented were compared.

Cost Category	SPD cost	Smartphone cost	Notes
Hardware device	$1,548	$199	Assumes including all SPD's listed in Replacement List tab in the comparison.
Data plan	$0	$600	Assumes cost of a $25 per month data plan for 24 months.  None of the SPD's chosen use a data plan.
Voice plan	same	same	Assumes a "traditional" cell phone with a voice plan in the comparison.  Replace with the cost of your voice plan as part of a total cost of ownership.
Apps	$0	$8	Total cost of the apps to replace all of the SPD's.  See spreadsheet in the Replacement List tab for breakdown.
Subscription/other	$0	$50	Total cost of subscriptions over 24 months.  See spreadsheet in the Replacement List tab for breakdown.
Total cost	$1,548	$857	The relative cost difference over 24 months

If your situation does not support the comparison of all of these single-purpose devices, you can download this spreadsheet.  Make changes to the column at the far right titled Place "X" for Your Situation on the spreadsheet in the Replacement List tab.  This will compute the total cost related to the single-purpose devices you mark with an "X" and automatically update the spreadsheet on the Relative Cost Comparison tab.

OTHER COST SAVINGS AND BENEFITS OF SMARTPHONES

There are a number of niche applications for smartphones that are of great benefit to certain individuals.  Buying an equivalent single-purpose device may be more costly and may not be as functional or beneficial.  These replacements could be added to your overall comparison.  A few examples:

• Star Walk (Vito Technology):  This is a personal planetarium.  A user can use it to identify all of the celestial objects in the sky in front of you in real-time or at any date you wish.  The application is $2.99 in Apple's App Store.  Purchasing a stargazing book would be more expensive and less functional.  Purchasing a Celestron SkyScout Personal Planetarium single-purpose device costs $212.29 (Amazon.com, July 17, 2010), more than the cost of a subsidized smartphone hardware.
• Bird reference guides:  There are a number of apps available from National Geographic to the Audobon Society.  These go beyond typical field guides and provide functionality like searching, identification, and listening to bird songs.
• SignalScope (Faber Acoustical):  This app turns an iPhone into a real-time spectrum analyzer and oscilloscope.  A very niche market, but for those in need of the capabilities provided by the app, it could help offset the cost of purchasing a single-purpose device equivalent.

Some of the smartphone apps can also be used to save money in other areas of your life.  These examples are mainly for the U.S. market, but there may be equivalent apps for other parts of the world:

• RedLaser (Occipital):  A user can scan (or enter) the barcode of thousands of products and see the prices of the same products from several retailers.
• Gas Buddy (Bottle Rocket):  This app shows the gas prices from nearby gas stations so the user can pick the place with the lowest price.  From the developer's own advertisement:  "Want a Free iPhone?  Gas Buddy will pay for itself in one take of gas and will pay for your iPhone after one year of use.  We're not kidding.  We didn't believe it at first either."
• Amazon Mobile (Amazon):  A user can do price comparisons and read reviews before making purchase decisions.
• CardStar (Mesa Dynamics): A user can store and retrieve loyalty, reward, and club membership cards, which is helpful in getting discounts and earning reward points.

There are a number of benefits of having a smartphone, independent of the outcome of the cost comparison.

• Availability:  A smartphone is more likely to be with you than many of the single-purpose devices.  This means a user will tend to use the functionality more.  How many times have you wanted to use your camera or video recorder but you did not have it with you?  Had a few minutes to read but did not have the newspaper or book with you?
• Convenience:  A smartphone takes up less space than most of the single-purpose devices listed.  Carrying several single-purpose devices at the same time requires a purse or bag, which may be inconvenient and therefore, less likely to be available.

There are some disadvantages of a multi-purpose device like a smartphone compared to several single-purpose devices.  Keep these in mind if they apply to you.

• Lending:  A single-purpose device can be lent to a colleague or friend.  A smartphone is less likely to be shared, and it cannot be in two places at once.
• Selling:  A single-purpose device can be sold or given away once its use is finished, perhaps recouping some or all of the cost of the device.
• Losing/breaking:  The loss of a smartphone will negatively affect a user much more.

HELP IMPROVE THE LIST OF SINGLE-PURPOSE DEVICES

If you have additional suggestions, please send me an e-mail at daniel @ danielsklar.com with the SPD name, SPD example, iOS app equivalent, and any notes.  I will review and add to the spreadsheet.

Happy replacing!

Toolkit: Pivot Tables in Microsoft Excel

The applications in the Microsoft Office suite (including Word, Excel, and PowerPoint) are extremely powerful tools.  The majority of users touch on a fraction of the capabilities of these tools, either because (1) their work does not require it or (2) because they do not know certain capabilities exist.  The first reason is understandable; no need to use or learn the powerful equation editor if you have no need for it.  The second reason — not being familiar with key features — is unfortunate.  If users would enhance their knowledge of these tools beyond the basics they would be much more productive and produce better results.

I will have a series of postings on a few of the powerful features of Microsoft Office that everyone should learn once they get past the basics of these applications.  They are part of my consultant’s toolkit because they both add value and save the client time and money.

The capabilities I will share are not “rarely applicable” or designed to “show-off your skills”.  They are frequently useful, meaningful, and beneficial.  I list a few here so you get an idea of the features I am referring to:

• Pivot tables in Excel
• Style sheets in Word
• Tables in PowerPoint

If you are not familiar with these capabilities or do not use them, I encourage you to research them, learn more about them, and apply them.

This posting introduces you to pivot tables in Microsoft Excel by:

• Providing a simple real-life example of a pivot table and its benefits
• Presenting an overview of the key concepts of pivot tables
• Sharing several real-life examples to give you additional ideas on where to apply it in your work
• Sharing some practical tips on using pivot tables
• Listing additional resources to learn more about pivot tables.

I focus on the concepts and the big picture of pivot tables in this posting.  I do not provide step-by-step instructions.  Microsoft Excel has changed both the location of where to create/manipulate pivot tables and the look and feel in their releases over the last several years.  Please refer to the help within Microsoft Excel to learn how to specifically perform the pivot table operations in your version of Excel.  The concepts and examples apply to all versions.

Note: The pivot tables depicted in this posting are based on real-life uses.  The data values (e.g., numbers, names, amounts) are fictitious, however, to remove any proprietary data.

SIDEBAR - AN EXAMPLE OF WHY TO LEARN NEW FEATURES A client sent me an Excel file with a long spreadsheet filled with sales data.  He said he spent a full day each month formatting the information.  Specifically, he spent time inserting new rows in the spreadsheet to put in column headings each time a page break occurred. I asked him if the “Rows to repeat at top” feature in Page Setup would work for him.  He was not familiar with it.  I showed him the feature and the few clicks needed to use it.  If someone had not showed him this built-in capability of Excel, he would still be doing this work at considerable expense (eight hours versus one minute).  This is independent of whether the formatting task itself added value to the company and could have been eliminated altogether. I asked him what else he did with the sales data to see if I could help him further.  He mentioned he spent a couple of days each month computing sales by salesman, sales by customer, sales by customer grouped by salesman, and a few other views on the data.  I asked him if he was familiar with pivot tables.  He was not.  Using an export from the sales system I showed him with pivot tables how he could create his reports each month in a couple of hours and with less likelihood of error. Bottomline:  Three days each month was reduced to a less than half a day by learning and leveraging just two of the features of the tool he used.

REAL-LIFE EXAMPLE OF A PIVOT TABLE AND ITS BENEFITS

One typical activity of a project manager is to track expenses on a project.  For large projects, a project management tool or an accounting system may be used to track these expenses.  For smaller projects or when you want more control over what and when you track expenses, a spreadsheet may be a good choice.

Here is a small portion of a spreadsheet I have used track expenses for multiple programs with multiple projects.

(Click image to enlarge)

Each row in the spreadsheet is an expense.  Each expense notes which program and project it is associated with, the month incurred, the company and person or asset billing the expense, the expense type, the funding type and who will fund it, and the expense in dollars.

A project manager with this information can now ask and receive answers to multiple questions:

• How much have we spent to date? By month? What is our trend?
• How much have we spent on each project? On each program which has multiple projects?
• How much have we spent on consultants? On software?
• How much have we spent on each phase?
• Hold it…how much have we spent each month by phase and by project?

Answering these questions (and more) is a mouse drag or mouse click away using a pivot table.

How much spent to date on all expenses tracked? One mouse drag gives the answer.

(Click image to enlarge)

What about by month? One additional mouse drag gives the answer.

(Click image to enlarge)

On the each project? Two drags displays the expenses broken down by project within program.

(Click image to enlarge)

Let’s jump to the last question…how much have we spent each month by phase by program?

(Click image to enlarge)

Several benefits of pivot tables can be seen by this example.

First, pivot tables can answer many questions important to people who use data to make decisions.  By viewing the data in multiple ways the user can grasp a large amount of detailed data more easily, spot potential issues or trends, or find out more about an existing issue.  Pivot tables allow this viewing of the same data in multiple ways.

Second, it is easy to change the way data is viewed.  Once the data is set up in a spreadsheet and a pivot table structure has been created (fairly straightforward using Microsoft Excel’s built-in PivotTable Wizard), it literally is a mouse drag or mouse click to change the view of the data.

Third, the computations in pivot tables are done automatically.  When you change the view, the calculations are handled for you. You do not need to write formulas.

Alternatives to pivot tables include writing formulas, using the subtotal feature of Excel, using filters on the data, or writing macros / purchasing add-ons to help redisplay or analyze the data.  There may be cases where these alternatives are more appropriate or provide a view pivot tables do not support (there are some limitations to pivot tables.)  But these alternatives tend to be more error-prone.

KEY CONCEPTS OF PIVOT TABLES

Three key concepts are the data table, the pivot table, and crosstabs.

Data Table

A data table stores the data to be viewed, analyzed, and summarized.  The top row of the data table contains the column headings.  The rows below the top row contain the data with each row representing one record of data.  Each cell on a data row contains a data value for that record.

(Click image to enlarge)

Each row in a data table needs to contain all the data for that record.  This may mean that some data is duplicated on multiple rows.  For example, if you want to be able to view data (in the earlier example) by project and by program you need to fill in the data value "TRW Program" in the program field on each row of the “Turtle Project” even though it the “Turtle Project” is always associated with the “TRW Program”.

The key design aspect of your data table is to determine how you may want to view your data.  By month?  By day?  By region?  By city?  By product?  By product grouping?  By ship date?   By month? A column needs to be set up and the data needs to be stored in the table for each dimension you would like to view.  Each piece of numerical data you want to compute with (sum, average, minimum, maximum, etc.) should also be a column, such as units, sales, and costs.

Pivot Table

The pivot table is where you view the data from the data table.  The pivot table and the data table are two separate tables.  The pivot table points to the data table behind the scenes.

The pivot table is also where you change the view so you can see the data in different ways ("slice and dice the data" or pivot it).  You do this by moving the field names around, filtering which values you want to see (for example, only show the "Turtle Project"), and setting field values for advanced computations (such as sum, count, variance, running totals, percent of).   Changing the view does not impact any of your data in the data table.  For example, removing a field from your pivot table does not change the underlying data table.

Crosstab

A cross-tabulation (or crosstab) is the intersection ("crossing") of two or more dimensions so that the intersection points represent the computations (sum, count, etc.) for each combination of dimensional values.  Below is a subset of a data table.

(Click image to enlarge)

There are two dimensions in the data table above:

• Gender, with possible values of Male and Female
• Handedness, with possible values of Left and Right

A cross-tabulation on these two dimensions can compute how many Males are Lefthanded, Females are Lefthanded, Males are Righthanded, and Females are Righthanded. Below is the crosstab with counts from 732 data rows.

(Click image to enlarge)

Excel can also display the results of the crosstab in percentages.

(Click image to enlarge)

The pivot tables in Excel are quite powerful.  It can perform these crosstabs on more than two dimensions at the same time.  If we had another column heading of “Eye Color”, we could compute the number of Blue-eyed Female Lefthanded people compared to the other combinations of dimensional values.

ADDITIONAL REAL-LIFE EXAMPLES

I have created scores of pivot tables over the years.  I find value-added uses for them in many situations.  Here are a few more examples to give you ideas.

I created this pivot table to track the status of over 200 individual projects each month while managing a large program.

(Click image to enlarge)

Microsoft Excel also has the capability to create pivot charts so you can view the data visually.

(Click image to enlarge)

How about this?  Leverage a tool like SurveyMonkey to capture survey information online through a web browser.  I used it to capture over 200 survey responses to a company event.  SurveyMonkey will allow you to export the data in a format perfect for a data table.

(Click image to enlarge)

Then create several pivots on the same page with the results.

(Click image to enlarge)

TIPS ON USING PIVOT TABLES

Start simple and try it out.  Build a simple data table with the information you think you need, put in column headings and a few sample data rows of mock data, build a pivot table using the PivotTable Wizard, and try it out.  Does it answer the questions you have?  Go back and make adjustments to your data table until you feel you have the right model.  Once you get comfortable with the pivot table concepts and its capabilities, designing data tables properly will become natural.

Include computed fields in your data table, if appropriate.  In the first real-life example, the Total Cost field is computed by multiplying the Units field by the Cost per Unit.  Color this column (to remind you it is computed with a formula and not entered directly) and place at the far right of the data table.  As you add rows to your data table, you can then copy the formulas to the new rows.

You can even use computed fields based on look-up values.  For example, if the “Turtle Project” will always be associated with the “TRW Program”, you could make the Program field a computed value with a VLOOKUP in a Project-Program cross-reference table.  Just be careful.  Make sure that the association will always be a true statement.  For example, a project manager may be leading the “Turtle Project” today but could be replaced by a new project manager later in the project.  If you want to view the data by project manager, you should not compute this field but manually fill it in each time for each record.

A pivot table is based on a range of rows in your data table.  If you insert rows outside of this range, your pivot table will not know to use this data.  To prevent this from happening, clearly mark the last row in the range of your data table.  When you insert rows above and inside the range, the pivot table will automatically adjust and include the new rows.  Here is an example:

(Click image to enlarge)

You can create multiple pivot tables in the same spreadsheet against the same data table.  Put one pivot table on a tab labeled “Playground”.  This can be used to slice and dice anyway you want -- it is your playground to play around with the views.  Put on separate worksheets pivot tables with the specific view and format that you always want to see whenever the data table is updated.  Multiple pivot tables allow you to have your "standard" pivot reports always available and a version of the pivot table you can always change.

While pivot tables are less prone to errors than other approaches, there are still some gotchas you should always check.

• First, refresh the values in the pivot table by pressing the exclamation point refresh icon.  This makes sure the table includes any changes to the data table since the last time the pivot table was refreshed.
• Second, make sure the pivot table is referring to all of the data rows in the data table.  Check that the range for the pivot table includes all of the rows you want in your results.
• Third, make sure there are no filters within the pivot table omitting some values from the results, unless that is what you wanted.  Pivot table functionality allows you hide and filter certain dimensional values.
• Fourth, make sure the calculation on the crosstab is the calculation you want.  A common mistake is for the computation to be a Sum when you want a Count or vice versa.
• Finally, as with the use of any spreadsheet, spot-check the answers to make sure they make sense and are not out of line of what you expect.

ADDITIONAL RESOURCES TO FURTHER YOUR KNOWLEDGE

Here are some places to find more information about pivot tables:

• The Help feature in Microsoft Excel. While it is not as extensive as it should be, it should be your first stop.  More recent versions of Excel include "courses" to step you through simple examples and how-to's within your particular version of Excel.
• The Microsoft website with support for Excel.
• Any of the free Pivot Table tutorials online.  Do a search on "Excel pivot table tutorial".  I found this example on a website called ProductivityPortfolio. This tutorial provides examples in Excel 2007.

Happy pivoting!