One of the hardest things when you become a VA is to decide what rates you are going to charge. The important thing is not to short-change yourself and instead consider the value you add to your clients.
You can charge by the hour, per project or through a monthly retainer.
It helps to know what you can do – and how long various tasks are likely to take you.
This all gets easier over time.
You get to know what tasks you are best suited to (so you can promote those more strongly in your marketing).
You learn to track your time and get to know precise (and approximate) times for how long it takes you to do specific tasks.
You become more productive as you get to know the time-saving tips and tricks of your computer tools and applications.
Create a spreadsheet and list all the common tasks you get asked (or may get asked) to do by a client.
Assign each task a time you know or think it takes to complete it. You can segment them if it helps to build a more accurate picture.
For example, for proofreading a document you could break that down into (i) proofread 10 pages, (ii) proofread 20 pages or (iii) proofread 30+ pages.
Create a column for ‘Quantity’ (the number of times that task is needed) and one for ‘total hours’.
When a client asks you to work on a project you can quickly calculate how much of your time it’s likely to take.
If you work on an hourly rate, you can then multiply the ‘total hours’ by the hourly rate. That gives you the likely fee you should charge.
It’s just a short step up to using that information to create a proposal (should one be required) with a neat, professional document.
And, of course, you can use Excel to record your money in and your money out.
What should you list?
When you are self-employed, freelance or running your own business it’s worth remembering what you may have given up from when you were working in a company:
Holiday pay
Sick pay
Maternity leave
National Insurance and tax sorted for you
A desk, stationery, phone, photocopier, fax and other equipment
Petrol and mileage
Rent
Utilities including broadband, phone lines, water, heating, electricity
Free coffee and tea
Training, network meetings and Personal Development
Guaranteed hours
Security of income
If you work from home you will have overheads to cover – your phone, broadband, heating, water, lighting and electricity, plus all your stationery.
It’s important to weigh that all up when you set your charges.
And, of course, if you offer specialist VA services – such as website tech or Social Media marketing – you can add that into the money mix, too.
Using Excel can help to work it all out so you know where you are – and can set your charges with confidence.
How do you decide what to charge? Do let me know in the comments below.
When you think of the changes in technology it’s easy to be seduced by the latest shiny new thing. Yet when it comes to handling numbers and data the King and Queen of applications is still Excel. It’s hard to believe the program is more than 30 years’ old.
The Mac version was launched in 1985 – the same year as BT announced it was getting rid of the UK’s famous red telephone boxes, and the same year the UK’s first-ever mobile phone call was made.
Windows gained Excel two years’ later – the same year as the UK saw its first female Prime Minister, Margaret Thatcher, the same month as T’Pau were topping the UK singles charts with ‘China in Your Hand’.
Excel was born to last the distance.
It helps you create and manage lists, use checklists, track expenses or events, make stuff add up, analyse data and make sense of information with ease.
Excel can work really hard on your behalf. I haven’t counted them all, but I’m told there are more than 500 functions available in the application. And I believe it!
For today, I’m just going to mention 8 functions which you’ll probably want to know about if you don’t already.
1.Paste Special
Copy and paste is one of the simplest and most used functions in Excel.
But in the process, you may find you carry over a format you don’t want or copy a formula over when all you want is a value. And to put that right takes up a bit more of your time.
Enter Paste Special. It allows you to pick up which bits of the copied cell you want to bring over. Neat and precise.
2. Add Multiple Rows
I’ve seen people who add new rows between existing rows, line by line. They clearly are not aware of the “Ctrl, shift, +” shortcut or another little trick for speed.
3. Flash Fill
This Excel function automatically fills your data when it senses a pattern. For example, let’s say you’re entering a series of product numbers, all starting ‘TVA’. You type ‘TVA00001’ in the first cell of a column and for the next cell down you only need to start typing the number… the ‘TVA’ prefix gets added by magic.
4. SUM
This is probably one of the first things you’ll learn to do in Excel – how to add up items in a row or column. But have you discovered the neat shortcut to speed the process?
5. CTRL Z / CTRL Y
This is the simple way to get yourself out of a jam. It’s Ctrl Z, of course, to undo mistakes in Excel. Use Ctrl Y to do the opposite, i.e. redo. A handy little safety net for those over-eager keyboard fingers.
6. Freeze Panes
You know when you are scrolling down a large table of data and you cannot recall which columns are which? That’s the time to use Freeze Panes. It allows you to freeze just the top row, first column or any number of either, so you know exactly what relates to what.
7. The F4 Button
You can use this button to toggle you through the various options when creating an Absolute Reference. You can also use it to repeat your last action, where the function is available. As an example, if you’ve just applied a border to one cell, use F4 to apply it to others.
8. CTRL + Arrows
Stop scrolling and scrolling through your data. You can use this shortcut to take you instantly to where you wish to go – to the end of a row, end of a column or the very end of your file. (Just mind if you have gaps between data).
Each of these time-saving functions can make you more productive – not just for simple tasks but also on more complex ones. It pays to know how Excel works and what it can do for you.
How many of these are you already using? Hit reply and tell me your favourite!
P.S. And, of course, if you want to be more productive with lists, expense tracking, sums and working out what the data really means you can take one of my online courses.
I am sure that this scenario resonates with you. You have just finished doing a whole lot of data entry for a client. It is a really long list. Now you just need to print it off for whatever reason.
The only problem is that the heading or title row does not print on every sheet of paper, it prints only on the first!
How do you make it print on every page?
Print Titles
Print Titles is how, and for those of you who are Mac users, this works the same on Windows and Mac.
Start off on the Page Layout Ribbon (same on Mac)
Click on the icon labelled Print Titles
In the dialogue box that pops up, click into Rows to Repeat at Top and click on the row number of the row that holds the headings (it is not always row 1 – although in my example it is Row 1)
Click OK.
Now whenever you print this worksheet, the title row will print on every page.
You can see it here on Page 1 and Page 2.
How is this tip going to save you time? Did you know this already?
To learn more fun things you can do with Microsoft Excel get my course Essential Excel Skills for Business there is a Windows Version and a Mac Version.
P.S – Have you joined my Facebook Group yet? Hop over there and ask to join. Ask questions, interact with others who have questions and have fun.
You have a mega spreadsheet. Loads and loads of data. It has taken you ages to compile it for your client. As you finish, panic sets in. Adding the data was easy. What if the client asks you to provide it sorted in a particular way?
What is the fastest way to sort your Excel data?
When I teach this in the classroom, I always ask the question – how do you sort your data? I usually get a few different answers.
Select the whole spreadsheet then click the Data Ribbon and the big Sort Button
Select the column to sort by and click Click A/Z or Z/A sort on the Data Ribbon
Click the Sort and Filter button on the Home Ribbon
Then I get someone say, but it never sorts everything. My data is all over the show.
So let’s debunk some sorting myths.
The absolutely fastest and most accurate way to sort a whole spreadsheet by the values in one column is to:
Click into the column
Click on the A/Z sort icon (it is on the Data Ribbon the right click or the drop down on the Home Ribbon)
Hey Presto! Your whole worksheet is sorted and it keeps everything together.
So to recap:
Click into the column to sort by
Click on the sort icon you like to use.
Did this help? Have you learned something new? How will you use it? Leave a comment below.
Shelley
P.S – have you joined my Facebook Group yet? Join in and ask questions, get answers and generally interact. See you there
You are a busy VA – that’s a given! A client has asked you to show her what her data means. What is the top value in the list of sales? Can you see values that meet a condition? Conditional Formatting will show the way.
What is Conditional Formatting?
Conditional formatting is exactly that, a format applied that matches a condition.
There are 5 kinds of Conditional Formatting to choose from:
Highlight Cells Rules – pick this to show cells that match a particular number/text value/date.
Top/Bottom Rules – pick this one, for example, to find the Top 10, Top 10%, Bottom 10 or Bottom 10%.
Data Bars – these shade the cells based on top and bottom values, so the greater the value the more shading in the cell. You can pick the colour scheme to suit.
Colour Scales – use Colour Scales to apply a scale from smallest to largest and set what each colour means.
Icon Sets – Use Icon Sets to show crosses and ticks, traffic lights etc as symbols in the highlighted cells.
Create your own rules
You can also create and apply your own rules to automatically change the formatting of cells based on the contents.
Where does Conditional Formatting live?
You will find Conditional Formatting on the Home Ribbon. To use it, first select the cells you want to apply the condition to and then choose the type of condition to set, followed by the format you would like to see.
Select the data
Click on Conditional Formatting
Select the type of formatting
Once you have decided which of the formats to use, you can then drill down and specify the exact format you want.
For example, if I want to see Total Sales at Koffee Island above £900 I will choose the Highlight Cells Rules and then Greater than, adding in the amount I want it to look for and setting the format.
Have a play with all the different types of Conditional Formatting, you will be amazed at how easy it is to spot patterns and get information from your data when you can visualise it.
Change the background fill of a cell
Your client has asked you to create a workbook or worksheet for her. She wants the background colour of some of the cells to change based on the text typed in or a number entered. For this you will need to create a custom rule.
Use a formula to control conditional formatting
To add conditional formatting to show a range of colours – I have a list of rooms names Red Yellow and Green and I would like the fill colour to change according to the name of the room. This is handy if you need to keep a visual track of room bookings for example.
Change the colour fill
I applied three formats to the cells one on top of the other.
If A2:A10 contain the word Red apply Red formatting
If A2:A10 contain the word Yellow apply Yellow formatting
If A2:A10 contain the word Green apply Green formatting
Apply the first condition
Select the cells to which you want to apply the conditional formatting and then click on Conditional Formatting and select New Rule.
Format cells that contain
In my example, I selected Format only cells that contain
Then picked Specific Text from the drop-down list
Added the word Green in this case
Set the format by clicking Format and going to the Fill tab to pick the colour.
Repeat for the other conditions
Go back and add the other two rules to the selected cells. Remember to change the criteria for each accordingly.
Now test your rules! Type one of the words into one of the cells that you selected when you started and watch the background colour change!
How did you find this tip? How will you use it? Leave me a comment to let me know.
To learn more about Excel you can take one of my Excel Courses, there is one for Windows and One for Mac.
I was recently asked about formatting cells in Excel, specifically about changing the colour of the cells. This made me think that perhaps it’s time to write a blog post about cell formatting, so here goes.
Why Format?
Formatting does several things:
Makes certain things stand out
Makes your spreadsheet easier to read
Makes things neat and tidy
What can I format?
Font style
Number format
Cell shading
Borders
Shading
Alignment
Text or Cell Colour
Column width
In today’s post I will look at the top three on the list and come back to the rest in another post.
Font Style
This is the font face, the size, the alignment, all the things in the Font Group on the Home Ribbon.
Now you can also create Styles in Excel just like In Word – well almost like in Word. The principle is the same. More on that later on.
To change the formatting, select the text or numbers to change and then make the change from the icons in the Font Group. You can see that my font is Arial and 12 points in size with nothing else added.
To change the colour choose the colour you want from the drop down list under the A and to add borders, choose the border style you want under the borders icon.
When you add a number to a cell and you want to use that number in a calculation, whether it is a simple number or a date, you need to use the right format so that Excel knows that it can work with that number.
When you add numbers to the cell they start off with a General Format. Now Excel will be able to do most things even with a General Format but if you have entered a date and want to work out the difference between two dates, or add 30 days to a date to find when an invoice is due for example, then it is very important to tell Excel that this number is in fact a date.
There is no need to type in notation such as Currency Symbols, these can all be controlled by the number format. This is a great time saver as you can just type in the numbers and then apply the formatting later. Or apply the formatting to the cells first and then as you type the notation is applied.
How do I tell Excel I have a date?
When you type in a date you can type it in several ways.
03/07/2019
03-07-2019
3rd July 2019
3 July 2019
All of these will change the number format to show Date. However, if you type in 3.7.2019 then Excel will not know that this is a date and will treat it as text. It is made up of numbers and full stops. None of the symbols that Excel will use to recognise a date format are present. If you try and add 30 days to this number, it just won’t work, and you will get an error message. You can of course select the cells and apply the Short Date or Long Date format to them and then as you type in the date it should show the format you have selected.
Cell Shading
Now for the question I was asked: How do you change the background colour of a cell in Excel?
There are a couple of ways to do this.
Select the cells to change then click on the drop down under the Fill Colour icon and choose the colour to apply.
You can choose from the Theme colours or click More Colours where you can pick from a larger selection or add the RGB values for a specific colour.
Alternatively select the cells to change (1) Click the drop down under the Borders Icon (2) then click on More Borders (3) now make your selections (4)
Here you can choose to add a pattern style and also choose from Fill Effects.
Once you have made your selections, the cell colours will have changed.
Now that you know how to change the Cell Formatting in three of the ways, look out for another post from me, which will look at Borders, Alignment and Cell Styles in Excel.
Leave me a comment and let me know how this has helped.
To learn more about Microsoft Excel, take one of my Excel Courses – there is one for Windows and one for Mac and more are in the pipeline.
