Microsoft Excel is one of the most important tools for a number of people who work digitally, so we want to help you with some tips and tricks that will help you get up and running with Excel spreadsheets. From using formulas to conditional formatting, excel has a vast range of things to improve your spreadsheets. ManSys are global trade software providers and they allow their software to export in Excel for reporting, they said that “harnessing the capabilities of Excel can allow you to sort through and understand data so much easier than trawling through the whole spreadsheet”. This highlights the fact that spreadsheets are often very large and very hard to understand on the whole, so making them simpler to read and understand is a great way to make your life easier.
CTRL + Shift select
Selecting an entire data set can sometimes be a pain if there is a lot of data, dragging your cursor down the page to select all the cells can take time. You can solve this by holding Ctrl and shift and pressing the down/up arrow to select a full column, or the left/right arrow to select a row. Doing this will select the furthest away cell that is attached to this row or column, if there is a break in between cells it will stop there.
Pressing ctrl + shift and down here selected everything from A2 down to the closest touching cell, it stopped before it reached shoes as there is a gap. If you continue holding ctrl and shift and press down again, it will continue to select more data. Much simpler than dragging your cursor down!
Simple Formulas for Calculations
Excel is the perfect tool for making time consuming things quick, such as doing number calculations in your head or on a calculator – minimise your time by using simple mathematical formulas in Excel. These are as simple as:
- Add using “+”
- Subtract using “-”
- Multiply using “*”
- Divide using “/”
Here you can see that a jacket costs £50, so in order 1, which is for 3 jackets, we can take cell B6 (price of jacket) and multiply it by 3 (*3), to get the price. You must put an equal sign and do the equation in brackets so that excel knows it is a formula.
Upon pressing enter to confirm the formula, you can see that it works out the price of three jackets at £150.
Now we want to work out the total of order 1, 2 t shirts and 3 jackets, so we can add the two prices together by adding E2 and E3, as you can see below.
These are a few very simple examples of calculations, but when you are working with large amounts of data, these simple formulas can be of great use!
Sums and averages
As you saw above, you can use + to add things together, but there is an even quicker method of doing so with large datasets and it is very simple.
All you need to do is input =sum( into a cell and select the range of data that you want to add together. This makes adding numbers such as prices very easy – this simple equation works out your total sales.
As well as adding the total sales, we can also simply find out the average order price using =average( in the same way we used sum.
Simple Conditional Formatting
Conditional formatting changes the appearance of a cell based on a rule that you set. This can be used to make it clear when a task has been done on a to do list, as a simple example.
We will set the rules so that “complete” is green and “to do” is red. If I select the appropriate cells, then go to home > conditional formatting > new rule, then select “only cells that contain”, we can set the rules there.
We can then tell excel that when the cell value is equal to “complete”, make the cell green. We can then repeat the same steps but set another rule that says when cell value is equal “to do”, make the cell red. We can then easily see from a birds eye view that we have completed the first task and the second two are still to do.
Sort Alphabetically
When you are dealing with a large amount of data, sorting the dataset alphabetically can be a great save of time and a good way to get your work in order, this is a very simple tip that can save you a lot of time.
Here we have a long list of unordered names, navigate to “data” in the main navigation and select “sort”. We then set the rule in column A, to sort cells A-Z, you can also sort them the other way round. There are some more complex rules that you can set yourself here, but as we are running through simple tips for beginners, we will leave it at that.
Freeze Panes
Typically, when you are using a spreadsheet you will have headings, and when you scroll down, the heading will disappear, this is where freeze panes can be of great use. Go to View > Freeze panes > Freeze top row and your headings at the top of your spreadsheet will remain there whilst you scroll through your spreadsheet. You can also freeze any other pane/column/row, if you require to do so.
As you can see now, we are at row 35, but you can still see the headings in row 1.
Cell character count
There may be certain circumstances where you require a character count in something that you are working on. For example, if you are creating a spreadsheet bios for people in your company but there is a character limit, you can quickly write, edit and check the length to make sure that it is correct with a simple formula.
Using the =len( formula will give you the character length in a cell, as you can see below.
You can now quickly see that your bio is 80 characters, and you can add more or subtract to fit your character count, constantly checking the length as you update.
Find and Replace
Find and replace is a process on Excel that pretty much does what the name suggests. You input a word or phrase and Excel will find all occurrences of this, and replace it with whatever you would like to replace it with.
For example, we have a spreadsheet of people with the second name Smith, but the data has downloaded all the second names in lower case. Go to the navigation section “home” > click “find & select” > click “replace” (Or for a shortcut, ctrl+h). We will then tell Excel to find “smith”, with the lower case S, and replace it with “Smith”, capitalised. Doing so will update all occurrences and save you clicking into each cell to update them to a capital letter. You can also do this for full words, phrases or anything that is in a cell.
Auto Filling Cells
If you need a column of cells filling with the same value, there is a very simple method that avoids the need to copy and paste, it will auto fill your cells. You do this by finding the small square on the bottom right corner of your cell and dragging it down as far as the required column needs to be. For example, if all the people in the below spreadsheet are going to be attending, you can type “yes” in the first row, and drag it down.
You can also do something called “fill series”. Notice the candidate number is 1, if the candidate numbers go down in order, you can repeat the same steps as the above, which will make all of the numbers 1, you can then click the small box that appears and select “fill series”, which will then change the numbers to 1-7.
