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.
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 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 BLOG20at the checkout.
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.
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/
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!
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.
