AI Fame Rush
Computers

Microsoft Excel Beginner Tips and Tricks

×

Microsoft Excel Beginner Tips and Tricks

Share this article
1 5

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.

hyU66YTy8diSTOvJm 0jbjAkgprpN6WuBp0MEp 46xex5yATGDNN0qDG4xfHd9ybKHX9y3RInTNbcos62gVDFxWGfWNno2cpcVGOr2sp10xb4h3wqS F3zYK4p2QwB7jJLW4VUOy

Upon pressing enter to confirm the formula, you can see that it works out the price of three jackets at £150.

1goE5l7ThkBKy7hijiInT4f7d0twQe 1HUIchWKQBWLW2l4PGa n6VxTZqH4ze5L2D5rps7jzT2mzsWVcXykzzI3jk1ku7a9ThLpT3

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.

k5 JfqLdv3eWeyzm3YISXn1yOLVBcEckMT8x3Q1m6gYnQRhltLUIvo3B0HSjWAhK80YS jM1WR PQ Y497CYalOZGeeSlyTu FIVFnLDjGn qXkw3D7U6 Nn7u D8lnhdMt6rYUR
D6B81atdwyDQsM9uN67diHH3JCNP 6Er2sgqzmEa3SKl5sUNHtVIcYYiiw2rlsaFqIXI6WZwgpssd1 T3u37ePNwDd0X 5dSQ nEfmV v9 TB Ohl5gyBWfNA7K2CezV8

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.
b67BzDbjRMtLHdnF9ytmhBcZ1 Rlmvk Xutf3gPKf7padSTFJmspE O8JGcc7yNU4FFfIqhOUkAvHWSbTNNUMeWuYT9DgGAGBM1prW 6dMf OqkHXh AiHr dTw7WEvOv zKleWUOGinU3p6FUDYxw017du LW06m999jys0LLnl hVBOTHAp732Nl4OwuBZg9Sqq4Ks9XfKXmxo

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.
FHrJ1tDa9igxuq6Wiy17CJZbkAEz1Bk8Jg6M2jYKx7m tIHlHezFJbDN3Unmt2K869B TqzIt04ToPCMpMJXDw3R

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.

u3EzY8m4m0f uAJct5SLeHjlG9OsVFsTXRoT1LZol1dHuRvDlE8xL71BMBWrq 6C

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.

coo7 hHftH0my4pl5h9 yJ0ehVS8UCM6mLG ILI6nJFLcA5cLxBn8BepnOIUsTbAhd1f1nuRQ 0ZQVWYWPPPScUz8znRuDnbPDSubo 8jK

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.s5RdnRB70JXrgXD9vXrBAxC8cMVbVeQQZIVOeGVYdIRoPu3op8MWgNBL74ZaE4nqB5O5JIyQe5tTFmM9j SqUi yasZ4rNfIukbO28e33pPWEd swFfgHzehiWPLSD05vzumg0Qq

cLrZfhYUOg57DGbUi75ab4kXSSY30ptJxZ1oGeD4rIMeNxQQOAp9SQCz8QQ 6HlnYphM vfe FGdN1QmDRpM9D30 bfmpUp9noUiGzr G4 cysDMvMm

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.

LS8I65NtUuaqF3qTLMgSXUsk5MUA9z2hdgyZmqD1YpZ7A683yXGAERN5SczsXDPV9ooJnMrP3bgk5TG0 vsWfyCnWAptVoEFbZyvEwKINnn11tv9MKukLEphK1IYbHk600Zf8WOi

As you can see now, we are at row 35, but you can still see the headings in row 1.

vC1oKc aHEY0XNW9WO3BXYE WjajZ iQH36sQkFNGPLLhyWK qw8YeZr j3r3m4nbQIcAN3D 7

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.

vU2075Qp0raA4 GvwWnL1hhfn 0C69wNrg 1w 74p4MHn jTT 9vVm4NH JN 57zyXSjtZ1A4muHblvpkDzo ZfgP2fWBgFHgfiNVr5ac BIhsH5XXgDrF tGujPrmekwPIv8loK
rNKc1D02oaGS3WmgfnuMwUbcs5vk7teiQvY1jixqTWTXgJk41ETJqTh 9m0Fcqg1FWjUlLUBZCHD ByL3rVNFHlUbVKkPBw2VvbeYX9V5Pk4R nd2bsg23GNBKKqdiIE7wtb4Snf

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.

qPYdj3IEXMK8cALH8TLYSYpai9NzHbo1jISKb0YoitmoYZwIlwWMfJqidQBPwsGAgihbwu7ljNjC9Z05wwUpQ2sYB9hMG E5sHcJGx8rNvBAJg9SbY1C 9Xq8lhEDRsVV4jyCWKX
DPDYN5e qsPI8t

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.

Oo7NziTFzGe0Luu3jbgsf9u0K9cW9TboRlqJTC5f 2Dxf36f hrzBdvw9rJlZXTegwBtUshgpu3RrlD9s1xvMEua0ROWLdOhtjJVkJL5NjTUtUSF4fprcPwPCl 9u8 nY3fF 2td
JLI9BqdI54OBFI8jXqhm6drN9kXqNdcwUI4WOZpSzJ iOGvWsl3wFsUV9ja1NidTzOEOsw1KbqXdu7GC
image?w=23&h=22&rev=6&ac=1&parent=1gQ8VNIIXpy6ItsJVcYQ2gGD3CYvsmzU0qICMGiOPKo8

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.

9dUtTR63CfhQFxM0mlWXtNDrM CEe9iFFMgPuLODZg80kUs33 eWRocEo0MpgZujmPLy7EYUvRXTYsfSvTe4p IR xaF0CQuuzGQyldhBYeIXbEMmY q7d8qywqWGfbl H 4RwCs