Subscribe to Podcast

Subscribe on iTunes
Subscribe on Stitcher
Subscribe on Google Play
Subscribe on Spotify

VAs – Making Time Add Up in Excel

VAs – Making Time Add Up in Excel

How do You Make Time Add Up?

Do you use Excel to keep track of your time? Most VAs I know use some sort of time tracking software which notes what they do. It can be a professional online piece of software which does all kinds of things from tracking your time to monitoring which websites are distracting you!

Some VAs prefer a different approach. Simply keeping a timer on their desk – starting it up when they begin a task and stopping it when they are done. Then they can record the time spent in a notebook or an Excel Spreadsheet.

I get asked a lot – how do I make time add up?

First of all, you need to make sure that Excel knows you are adding time to the worksheet and not just random numbers or data. To do this select the cells where you will be adding in the time values and format them as Time.

  1. On the Home Ribbon
  2. Click the drop down in the Number Formats box
  3. Choose Time

Now you won’t actually see anything on the worksheet, the cells will look like normal. It is only when you start to add numbers in that you will see the Time Format and then you need to type the numbers in correctly for the Time Format to work.

To see numbers as time, they need to follow the number format and you will need to type them in like this:

02:05:00

02 – hours

05 – minutes

00 – seconds

If you simply type in 2 then you will see 00:00:00 as Excel does not know whether the 2 is hours, minutes or seconds!

Next do a sum

Now that you have added the data to the spreadsheet and it is formatted correctly Excel will add it up using the Sum Function as normal.

  1. Place the cursor in the cell where you want the answer to appear
  2. Click on the AutoSum icon and select Sum
  3. Now drag over the cells to include in the formula
  4. Press Enter

You will see that Excel has added up the column of times and you have the correct answer. In this case 1 hour and 45 minutes.

What about working out the difference between the start and end times?

In this case, you will need to have a column for the Start Time and a column for the End Time. You can then enter the values as times and calculate End Time minus Start Time.

To calculate use the Cell References and the minus sign. So the formula reads =D2-C2 where D2 holds the End Time and C2 the Start Time. Next you can use Autofill to copy the formula down the column so that it is replicated in every row. You will now see the Time Spent total for each row.

Create a Total for All Time Spent

The next step is to create a total for the Time Spent. I did this in another column completely and included in the Sum all the possible rows in the calculation.

This calculation is =SUM(E2:E15) meaning that it will add up any numbers within the cells E2 to E15. If I were to add more rows in, then I will need to update the formula.

You can read about Autofill in Excel –VAs- Why Autofill in Excel Will Save You Hours

You can read more about Sums in Excel – VAs- Why Everything you know about Sums is Wrong

You can learn more about making things add up in my course Essential Excel Skills for VAs, there is both a Windows and a Mac version of the course and you can find them at https://courses.tomorrowsva.com and as a bonus for being one of my readers you get 20% off the list price of the course! Just add the Coupon Code BLOG20 at the checkout.

 

VAs – Why Number Formats Matter in Excel

VAs – Why Number Formats Matter in Excel

Number formats

Why do Number Formats Matter?

When working in Microsoft Excel you may have noticed that the data you enter does not have a Number Format when you first enter it, rather it has a General Format. You may be wondering why it matters what kind of format the data has.

Excel spreadsheets are set out as a grid and can be used for many different purposes. Some people use them for lists, checklists, trackers etc. However, the most frequent use is for making things add up.

Excel can add up numbers that have a General Format however if you want to show Currency or decimals and get them adding up properly then you do need to apply one of the Numeric Formats.

The Number Group

On the Home Ribbon you will see the Number Group. This is where you choose the format for the data you are entering into your spreadsheet, or you can change the format after you have entered the data.

Number formats

Click the drop-down arrow to see the immediately available options.

Format What it does
General No specific format – will add up numbers and display text. Does not display decimals or currency notation
Number 12.45 Displays a number with two decimal places
Currency £12.45 Shows the currency symbol and two decimal places – the currency symbol is aligned close to the numbers
Accounting Shows the currency symbol and two decimal places but the currency symbol is aligned to the far left of the cell

£ 12.45

Short Date 12/08/2018 – displays the data like this
Long Date 12 August 2018 is how a long date displays
Time 12:08:34 displays time in hours minutes and seconds
Percentage 12% shows a number as a whole number with a percent sign
Fraction Turns 1/4 into ¼
Scientific Long scientific numbers are displayed using this format
Text Anything that is absolutely Text should be formatted this way.
More Formats Get to even more formats and the ability to customise how certain formas look

Make your numbers look good

Number formats also help make the spreadsheet make sense so that when you first look at a sea of numbers you can differentiate between them. Everyone knows that £12.45 denotes money whereas 12.45 is simply a number.

The Number Format Icons

Just underneath the Number Format drop down, are five icons. Here is what they do.

Icon What it does
Choose which currency format you wish to apply. However, be aware that this only displays the currency with the correct symbol, it won’t do any conversions!
The percentage format. This add a percent sign to a number. If you have a decimal like .5 it will turn into 50%.
The comma format. This applies a comma to separate the thousands. For example –

12344.00 will be 1,2344.00

Increase and decrease Decimal places. Choose how many decimal places you want to see by clicking on these icons.

Use Number Formats to make your spreadsheet make sense

When you need to present your spreadsheet to other people, using Number Formats can make all the difference between them understanding your worksheet or not.

To learn more about Number Formats and making your spreadsheets add up, head over to my course Essential Excel Skills for VAs – there is a Windows version and a Mac version. You will find them at https://courses.tomorrowsva.com/

 

VAs – How to add Headers and Footers in Excel

VAs – How to add Headers and Footers in Excel

headers and Footers in ExcelHeaders and Footers

Headers and footers appear on every printed page. This is how you add page numbers, date and time, the file name and path and where you can add a logo to every page in the Header.

Headers and Footers are added via the Page Layout View and are typed into the Header or Footer Area directly. A Header and Footer Ribbon will pop up when you begin.

Change to Page Layout View


Figure 1 – Get to Page Layout

Click the Page Layout Icon – bottom right of the screen.

Or

Click on the View Tab and select Page Layout View from the icons there

Add a header

Once in Page Layout View click into the Header area.


Figure 2 –See the header/footer area

The Header (and Footer) areas are divided up into three sections, Left, Center and Right. Click into the area you wish to have the Header (or Footer) appear in.

Once there a new Ribbon Tab Appears – Header and Footer Tools.

Header and Footer tools ribbon


Figure 3 – Header and footer tools Ribbon

The Ribbon that pops up when you click into the Header area gives you various options to add to the Header or Footer.

Pre-set Header or Footer


Figure 4 – Select from a pre-set header or footer

Choose one of the pre-set Headers or Footers and it will be added to the Header or Footer exactly where your cursor is

Page Number


Figure 5 – Add page numbers

There are two icons for Page Numbers, Page Number and Number of Pages. If all you need is a simple page number, then select the first one.

If, however you want it to show for instance Page 2 of 4 then you need to

  1. Type the word Page
  2. Click on the Page Number Icon
  3. Type a space then the word of then a space
  4. Click on the Number of Pages icon

It will then say – Page 2 of 4

Adding Header and Footer Elements


Figure 6 – Header and footer elements

  • Current Date – this adds the current date to the worksheet header/footer and will always be up to date
  • Current Time – like the current date this will always be up to date
  • File Path – this adds the path and name showing where the file is saved – C\Documents\ExcelBook\Coffee Sales for example
  • File Name – this adds just the file name without the location
  • Sheet Name – this adds the name of the current worksheet (assuming you have renamed it from Sheet 1)
  • Picture – allows you to add a picture to the worksheet header
  • Format Picture – having added a picture you can format it. This icon will only light up if you have a picture in the Header.

Switch between Header and Footer


Figure 7 – Switch between the header and footer

Click the icon that will switch you between the Header or Footer

Options


Figure 8- Header and footer options

  1. Different First Page – if your first page is a cover, you may not want to have the same Header or Footer as the rest of the pages
  2. Different Odd and Even – when printing a report, you may want to have different odd and even paged Headers/Footers
  3. Scale with Document – this keeps the Header/Footer in proportion if you scale down or up.
  4. Align with page margins – this makes sure that the Header/Footer lines up with the margin. If you have a narrow margin you will want to tick this box so that the Header/Footer spreads out to meet the margin and is not inset.

Current Date

The Current Date icon places the &[Date] field into the header/footer and shows the current date


Figure 9 – Set the current date in the Header or Footer

Current Time

The Current Time icon places the &[Time] field in the Header/Footer and shows the current time as above.


Figure 10 – Show the current time

File Path

The File Path places the &[File]&[Path] field into the header/footer – I am now using the Footer and it shows the file name and the path to the file – so you can find the spreadsheet when you need it


Figure 11 – Show the document path and filename

File Name

This places the name of the current file in the Header/Footer – it looks like this &[File]


Figure 12 – Show the Filename on the sheet tab

Sheet Name

This is the name of the worksheet – it looks like this &[Tab]


Figure 13 – Show the worksheet name

Picture

This is used when you wish to add a picture to your Header or Footer – either as a watermark or because you want to add a logo in the top right as in the image above

Figure 14 – add an image to your Header/footer

Format Picture


Figure 15 – Format the image

This button only becomes available when you have a picture in your Header/Footer

Use it to change the size, crop, change brightness etc.

Take our course Essentials Excel Skills for Virtual Assistants to learn how to do this and a lot more besides.

You can find the Windows version Here.

You can find the Mac version Here.

What’s New at tomorrow’s VA?

What’s New at tomorrow’s VA?

Excel Training

Exciting times

I set up tomorrow's VA to enable Virtual Assistants to hone their Microsoft Office skills and get more done in less time. I have been diligently creating mini online training courses for hot topics in Microsoft Office.

Today I am excited to announce that there are now two Excel courses. Well, when I say two, I mean one course in two flavours.

Essential Excel Skills for Virtual Assistants is now live in both Windows and Mac versions.

So what will you learn?

In this course you will learn how to set up your worksheet in seconds, create calculations and formulas with flair, format everything so that it looks clean and professional (and easy to understand!) and make sure the whole thing works without a worry!

Imagine, when you are given a task to do in Excel by a client and you sit there staring at a spreadsheet. You are wondering where to begin. What are all these boxes that you can type in, where should you type? What kind of data does Excel hold?

Should you be bothered about formatting? How about making stuff add up? The list goes on.

Foundation Skills

In most of the software packages, you can likely muddle along finding your way and working things out. You will always find a better, faster, more elegant way of doing things when you learn how to do them from someone who has been teaching this for 20 years - wait did I say 20 years? Where did that go?

When you start to use Excel though, there are some fundamental skills that you will need.

  • How to add data to your spreadsheet so that it makes sense and works for you not against you
  • How to build a formula from scratch
  • How to apply formatting to your data so that it looks awesome
  • How to fix a cell reference – essential

I cover all the above in this course and there is one for you whether you use Windows or Mac.

Just pick the right flavour and away you go.

Excel Windows
Excel Mac

I love helping people get the most out of the software they use on a daily basis - and I am creating more courses all the time. Come back often to see.

Special Launch Offer

The first 10 people to buy this course will benefit from a 20% launch discount!

So go get this course today to benefit from the discount.

Remember – This course will give you the skills to build on.

Let me know what other courses you would like! I want to make sure I am building the learning you want and need.

Go get your course!

What every VA needs to know about dollar signs in Excel formula

What every VA needs to know about dollar signs in Excel formula

You have just been sent a spreadsheet. It has been set up for you and you are eager to get to work filling in the blanks and completing the calculations. But what’s this? You suddenly notice in some of the formulas there are some Dollar $ signs lurking. What do they mean? Why are they there? What should you do?

Don’t Panic

These dollar signs are a secret code for Excel. They tell Excel to fix a Cell Reference. Huh? What do you mean? Fix a Cell Reference? What is a Cell Reference and why does it need to be fixed? Is it broken?

What those Dollar Signs mean

When working in Excel and creating formula in a column you may well be struck by the fact that you need to complete the same formula in every row. So in the image above I need to complete the formula in cell E2 in the other cells E3 E4 E5 E6 and E7.

Now that is not so difficult in this example, there are not many in this list. However, if you had a list of even plus that is going to be very time consuming not to mention frustrating.

Is there a faster way?

You know I am going to say yes, now don’t you?

When filling a column (or a row for that matter) you can use a handy feature called Autofill see  Virtual Assistants – why Autofill in Excel will save you hours.

Autofill is a fabulous tool and it copies exactly the referencing in the cell you start from – in this case it would copy =D2*J1 and in the row below it will be D3*J1 – or rather it should.

However, what actually happens is in each case the reference to J1 gets changed as well. So, in the cell below you would see D3*J2. In the cell known as J2 there is actually no value. So, the result in cell E3 will also be 0.

This is where the dollars come in

To make the reference to cell J1 copy throughout the column – we need a way of telling Excel to fix the reference. We want every cell in Column D to refer back to cell J1 not to J2 J3 J4 etc.

Enter the F4 key.

The F4 key – one of the function keys on the top row of your keyboard, adds two dollar signs to the cell reference you are currently adding to the formula.

The first time you press it, you get $J$1 this fixes the cell reference absolutely to cell J1 and is known as an Absolute Cell Reference.

The second time you press it you get J$1 – this fixes the reference to just the row – so when you drag across several columns the column will change but the row stays fixed.

The third time you press is you get $J1 – this fixes the reference to the column – so when you drag down the row will adjust but the column stays the same.

The fourth time you press it, the dollar signs are removed completely.

Would you like to learn how to put this into practice?

To learn how to build your formula properly and what happens when you need to add subtract divide and multiply, plus what order Excel will perform calculations in, and how to use Absolute Cell references with the F4 key, then you need to take my course – Essential Excel Skills for Virtual Assistants.

In this course you will learn how to set up your worksheet in seconds, create calculations and formulas with flair, format everything so that it looks clean and professional (and easy to understand!) and make sure the whole thing works without a worry!

What are you waiting for? Head over there now.

Essential Excel Skills for Virtual Assistants 

The first 1o to sign up will qualify for a 20% discount — just enter the coupon code 20%F4 at checkout.

 

 

Virtual Assistants – Why Autofill in Excel will save you hours

Virtual Assistants – Why Autofill in Excel will save you hours

Autofil in Excel

What is Autofill?

Simply put, Autofill automatically fills a series of data whether that is a range of numbers, days of the week, months of the year or any sequence that you care to set. For example in the image below I created a telephone rota or office staffing rota. Using Autofill I could set up the days of the week along the top without having to type them all.

Autofill in Excel

I started out by typing Monday and formatting the cell. Then using the little fill handle in the bottom right hand corner, I hold it down and click and drag across all the columns. Then as I don't want Saturday and Sunday I can choose Fill Weekdays from the button also known as a Smart Tag that pops up.

So what?

Well, not only can it create the series of days starting from any day of the week, but it can also fill in a sequence of months or even increment dates. When you type in a date and use the Autofill handle to create the series you get to choose whether to increment by one day at a time, or fill months or years so creating a monthly or annual schedule.

If you are in the business of managing an event and have timings - then it will save you time filling in the session times. Just set the first two and Excel will do the rest.

Autofill Excel

Want to learn about this and many other Excel tools?

To learn how to use Autofill , build your formula properly and learn what happens when you need to add subtract divide and multiply, plus what order Excel will perform calculations in, then you need to take my course - Essential Excel Skills for Virtual Assistants.

In this course you will learn how to set up your worksheet in seconds, create calculations and formulas with flair, format everything so that it looks clean and professional (and easy to understand!) and make sure the whole thing works without a worry!

What are you waiting for? Head over there now.

The first 10 people who sign up qualify for a 20% discount. Add the coupon code AUTOFILL20% at checkout.

Or click the button that already has the discount applied.