Skip to content

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.

Photoshop – improve photo with HDR


Using HDR to adjust (equalize) the contrast so you get more detail in a photo.  You can also add adjustment layers at the end to tweak the image.  Done with Photoshop CS6, the techniques work with earlier and newer versions.

If the file has layers, make a duplicate as HDR will flatten the image.  This brief video demonstrates methods to tweak and improve your photo.  You can also create some very interesting effects with the photo.

Excel – getting 0’s to appear at beginning of the cell


Very often competent Excel users get frustrated when they cannot get a 0 at the beginning of a cell or when they type in month and year (i.e. September 2017), it appears as 9/1/17.  Naming worksheets and copying them is also reviewed.   This 4 minute video covers all these topics.

Link data between Excel and Word or PowerPoint


If you are doing a presentation – either PowerPoint or Word – and are obtaining data from a spreadsheet, you can literally link the data so that changes in the spreadsheet will appear in the document.

You use paste link feature which is in PPT and Word – on the home tab, click on the drop down arrow under paste, choose paste special, select link on the radio button that appears.  Future edits in Excel will appear in the other document – if it does not appear, right click in the image and select Update Link.  Video below demonstrates this.