Formulas

Got a spreadsheet problem that you just can’t figure out?

Wondering whether you’re going all round the houses to do something that could be solved with a few clicks?

Don’t fear… Traci’s Tips are right here!

Here are a few Formulas that you may find useful.

Tip 1

Concatenate: Adding text together, i.e. First Name & Surname (=A1&A2)

Tip 2

Subtotal: =SUBTOTAL(9,A1:A20)…this will show the sum of a column, but adjust the total each time a filter is applied

Tip 3

Calculate VAT from NET Value: =A1*.2 (if Net value is in cell A1)

Tip 4

Calculate VAT from Gross Value: =A1/1.2*.2 (if Gross value is in cell A1)

Tip 5

=TODAY() This will return ‘Todays Date’ (and continually update)

Tip 6

=MONTH(A1) This will return the Month Number of the date in cell A1

Tip 7

=YEAR(A1) This will return the Year of the date in cell A1

Tip 8

=PROPER(A1) This will copy the data in cell A1, but make the first letters of each word a capital letter

Tip 9

=LEN(A1) This will count the number of characters in cell A1, including spaces and punctuation

Tip 10

Use ‘Nested If’ formula’s to apply more than one criteria and possible outcomes

Tip 11

=ROMAN(A1) This will convert your numbers into Roman Numerals………….…useful for confusing your Accountant! 😉

Tip 12

=NOW() This will return Todays Date & Time (and continually update)

Please don’t hesitate to get in touch if you have any spreadsheet related questions, and don’t forget you can access loads of my other free templates

Traci x