Skip to content

Summarize data in an Excel Pivot Table


Pivot tables are very powerful in summarizing data…..sometimes you may wish to do more than just display the actual numbers. This example shows you how to show % of either column (category) total or the grand total.  In addition, discover how to change the formatting of the numbers.  You will also know how to refresh data – if there is ever a change in the source data, the pivot table will NOT show it until it is refreshed.

2017 Photographer(y) Resolutions


For those of you who enjoy photography, below are a few resolutions that I am hoping to follow next year!  Some are tongue-in-cheek, yet serious

  1.  When I take pictures, I promise not to take 37 shots of the same image – I will take a moment or two to look at the area, take my picture(s) yet not overdo it.  Back in the days of film (and it could be costly!), I’d think things out first.  Even though it is “free,” it can be very time consuming to delete excess photos or to just waste time wading through all of the images.  This rule can be broken when you’re in a once-in-a-lifetime experience such as being at the top of Mt. Everest.
  2. Deciding what accessory, lens, gadget I need rather than want.  Too often the gadgets get in the way and can slow you down (either in weight) or deplete the checking account.  If it is something you need, get it.  If it is a want, wait about 90 days and then you’ll decide whether or not it’s worthwhile.
  3. Try to learn a shortcut or two in your post production processing – actions and batches can be huge time savers when you ignored resolution #1!
  4. Try to spend some time just looking and enjoying the sights, vistas, beauty.  We can get more joy and pleasure by admiring what we see rather than trying to recapture it!  This is a great idea especially when you forgot your camera.
  5. Get a picture (or 2 or 3) off of the camera/computer – print it out – hang it so you (and everyone else) can enjoy the image!  Change the images in the frame every few months or weeks – it’s an inexpensive way to keep your personal momentum going (as well as decorating your home or office).

10 essential Excel basics to know


if you have some time off during the holidays and are planning on improving your skills for your current (or future) job, these are the 10 basics you need to know:

  1. How to enter basic formulas and calculations
  2. Add up a column or row of cells
  3. Absolute and relative references
  4. Rounding numbers
  5. Scale your spreadsheet to fit on one page when printing
  6. Print a spreadsheet with page numbers (and knowing footers with dates, sheet name, file name)
  7. Freeze or lock rows and columns in
  8. How to use the IF function in Excel to calculate values based on different criteria
  9. How to use Autofilter in Excel
  10. How to create a Pivot Table – this is an intermediate function that is required more and more in the workforce – indicating knowledge of this topic on your resume  is more than slightly helpful!

There more than a few references on the internet on how to do any or all of these – if, like most people, you desire hands on tutoring, find someone to help or take a class – either in continuing education or at a local college.

info@big-pic.net

 

 

Excel – solving common mistakes


Even though we may have basic Excel competence, below are a few errors, issues you may get – typically easy to solve!

Circular reference – this means that you included your cell address in a formula – for example you’re in cell D10 and you’re adding the numbers above (which should be D1:D9) yet you highlighted cells D1:D22 – this is including the cell that the formula should appear in.

quick fix – delete the formula and start over again.  Or, you can click in the formula bar and edit the formula.

 

#DIV/O or #VALUE! – there is something wrong with the data entry in the cells you are used in the formula – it could be many issues:

a) that a cell was blank (this happens in division/multiplication)
b) text  (a word) int he cell that was to be a number (you could have selected an incorrect cell)
c) you could have deleted a cell, row, or column that was to be used in the formula.

Sometimes it is a good idea to walk away from the spreadsheet for a few minutes and come back.  If you are attempting to do anything complex, I’d suggest saving the file before starting.  If you end up with a real mess, close without saving the file.  Or you can do a save as and give the file a different name.

Good luck!

 

Printing the Excel worksheet


Printing a spreadsheet is basically simple. This goes into a additional topics – i.e. printing what should only be a one page worksheet that is too many pages long and how to fix it. We also discuss hiding data that we don’t others to see or have access to (hiding columns doesn’t do the trick as they can unhide them).

Copy and paste special are included in this along with rounding of numbers, you will get 4.50 instead of 4.48934533 and using paste special so you don’t get the full unrounded value.

Excel – print formulas/comments


This is a very basic function in Excel – sometimes  you may need printed documentation of both of these items – the comments can be used as a reference as what needs to be done.  Having formulas printed out can be a method of seeing how a complex formula was created – these printouts can be used as reference in future tasks (we cannot remember everything!).

This will work with any version of Excel up to 2016/365!

Excel – working with tables


Tables have their uses in Excel where data can be instantly formatted. You can also filter (query) the data set to find data that meets specific conditions.  They are useful if you need to do some quick calculations – they can also be attractively presented.