Excel basic tips and tricks

Excel seems daunting to use at first, here’s some tricks to turn it into an automated tool for pretty much anything!

Funny thing about Excel is once you’ve worked out the basics, you are opened up to a growing list of functions and controls, pushing Excel’s usability much further. From Basic accounts to label printing contact lists..

Basic terminology

To try and explain the examples, rather than delve in and say “here works :+-*/… etc” I’ll just put FUNCTION and try to explain it in a brief example. Majority of it will be maths/numbers related.

To test your functions, you can run the Function Checker (fx button) which will give you a result if its valid.

Here’s some easy functions.

SUM

Usage in cell ‘=SUM(valueFUNCTIONvalue)’ or ‘=SUM(valueFUNCTIONvalue)FUNCTIONvalue’

Valid values: Cell ID’s (A1, Sheet1!a1) solid numbers, calculated numbers

Valid Functions type: Maths

Examples: ‘=SUM(A1:A6)’ will total up all numbers in cells A1 to A6. ‘=SUM(A1:A6)*1.20’ Will total up numbers in A1 to A6 first and then times that total by 20%, Ideal for VAT calculation.

Uses: Calculating totals or prices. Account tracking/ spending forecast/tracking.

ROUNDUP

Usage in cell ‘=ROUNDUP(valueFUNCTIONvalue,value2)’

Valid values: Cell ID’s (A1, Sheet1!a1) solid numbers, calculated numbers. Value2 would be what you want to roundup to, 0 will be the closet whole number.

Valid Functions type: Maths

Examples: =ROUNDUP(14.5*1.2,0)

AVERAGE

Usage in cell ‘=AVERAGE(value,value,value,…)’

Valid values: Cell ID’s (A1, Sheet1!a1) solid numbers, calculated numbers.

used to find the average of a set of values.

COUNT

Usage in cell ‘=COUNT(value)

Valid values: Cell ID’s (A1, Sheet1!a1) solid numbers, calculated numbers.

Used to count the amount of times Value appears in selection

Much more…

There’s a lot more stuff you can do with Excel sheets, More than I can ever teach anyone before understanding myself. lots of guides all over the web if you are curious.

For troubleshooting, run the Function helper/builder in the input bar. Common errors are #REF which will be invalid cells, #VALUE will have cells what don’t contain numbers usually.

 

Photo by AJC ajcann.wordpress.com

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.