by Shelley Fishel | Excel Mac, Excel Windows
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.
- On the Home Ribbon
- Click the drop down in the Number Formats box
- 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.
- Place the cursor in the cell where you want the answer to appear
- Click on the AutoSum icon and select Sum
- Now drag over the cells to include in the formula
- 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.
by Shelley Fishel | Excel Mac, Excel Windows
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.
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.
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/
by Shelley Fishel | Excel Mac, Excel Windows
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.
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!
by Shelley Fishel | Excel Mac, Excel Windows
How can Matt find joy analysing Data with Excel?
Matt was a marketing executive who had recently started in a new London firm. Part of his role was to collect and analyse market research data and present the findings to a team meeting each week.
He loved his job as a creative team player and inputting ideas and seeing them through, however the number crunching aspect of his role was not something he relished. This was because he found himself spending more time on working out how to create spreadsheet functions and formulae than focusing on the content and creating the ideas from the results. This was stopping him from feeling confident in his own true skills and abilities.
For Matt, this was a pain!
How could Matt make the necessary chore of data analysis an easier task, so that he could excel at his real job?
In a one-and-a-half-hour session I taught him the joy of Subtotals! We looked at ways of totaling groups of data using the subtotals feature. Matt’s eyes were opened, and his confidence raised in a
matter of minutes.
He also admitted to me that a previous training day where he was part of a group, he had felt embarrassed to ask some basic questions on constructing simple formula in front of the others. In feeling like this, he was unable to learn what he specifically needed and then to do his job to the best of his ability.
What is holding you or the people you work with back? Are you afraid like Matt that you or will look stupid by asking questions that you think are simple when on a full day training course?
In my experience if there is one person who asks a seemingly simple question then there are others who have the same question and are too shy or embarrassed to ask.
How does this apply to a VA?
This applies to a busy VA as well as to a team player in a busy marketing company. Perhaps you are asked by your client to analyse sales data or trends? What if you need to create a tracker for various tasks?
You could use our online courses to work at your own time and pace go to – www.courses.tomorrowsva.com . Alternatively, you could book a one to one learning session with me, you can pick the topics and the time. It can be face to face or over something like Zoom and we can get you working smarter not harder in just an hour and a half!
Time well spent I’d say.
Here is what Jo Brianti of JLB Support Solutions said after spending just an hour and a half with me recently.
I love it and wish I had found you/your help much earlier. Using this has streamlined my toolset and will save me money as I don’t need to pay for other tools as I have in the past.
You can find Jo here www.jlbsupportsolutions.co.uk
Leave a comment below if you have any questions.
by Shelley Fishel | Excel Mac, Excel Windows
How Jill could use the filter button to save time!
Remember Jill? She has been wasting so much time. Up to 4 hours on just one task every week! Could be even more to be honest. If Jill had learned just this one thing it would have cut down her time wasting significantly. Filtering data is just one of a series of ways of finding out information that is stored in your spreadsheet. Let’s see how Jill could use the filter button to save time.
Follow the steps below on your own data.
The data I am using here is from the sales of coffee at Koffee Island which is the coffee shop that features in my Excel book – did I mention I have written a book? It is called Business Barista – Essential Excel Skills to Streamline your Business and can be purchased from Amazon here.
Now how about that filtering
Filter data – the filter icon
Filtering allows you to hide the information that you do not want to see. So if I want to see sales of Cappuccino in London only, I can use the filter option to do just that. Then I can change the filter to suit my needs and refocus on different sets of data.
Filter on the Home Tab
- Click anywhere inside your list
- On the Home Tab click Sort and Filter
- Click Filter
Filter on the data tab
- Click anywhere inside your list
- Click on the Data Icon
- Click Filter in the Sort and Filter Group
Filter on text
Now that you know where the filter lives, let’s use it to find sales of Cappuccinos.
- Click on the drop down arrow by the column heading you want to filter
- Un-check Select All
- Select the item on which you want to filter
- Click OK
I now have only rows where the value in the Item column is Cappuccino.
Use the Right Click
- Right click on the cell with the value you want to filter out – in my example, I have clicked on a cell with Cappuccino
- Click Filter
- Click on Filter by Selected Cell’s Value
You will now see only rows with the value you chose.
Filter on more than one column
I would now like to see the sales of Cappuccino in Manchester only. I will apply the filter to both the Item and the Centrecentre columns.
Apply the filter to another column
- Click on the drop down arrow next to the second column to filter on
- Un-check Select All
- Click to select the item on which you want to filter
- Click OK
I now have only rows where Cappuccino was sold in Manchester. You can tell that rows have been hidden by looking at the row numbers – which will also have turned blue.
Remove the filter
Now I want to see all of the data. There are a few ways to clear the filter.
Clear the filter
Click on Clear
All the data comes back with the Auto filter arrows still in place
Turn off the filter
Click on the Filter icon
The filter is cleared and the auto filter arrows are removed
Clear the filter from one column only
- Click the drop down arrow of the column whose filter you want to clear
- Select Clear Filter From “the field name”
- Click OK
Remove with Right Click
- Right click in the column you have filtered
- Select Filter
-
Select Clear Filter From “Name of Column” (in our example it is from the Centre Column)
Now that you can see how simple it is to learn how to do things, why not hop over to our courses and see if there is one that suits your need at the moment. Excel courses are coming soon.
Please leave me a comment if you are finding the blog posts useful and let me know the kind of things you would love to see here.
