The depths of Microsoft Excel
We’ve all been there; “knowledge of Microsoft Excel is required”. But you’ve only used it in school, or years ago at a previous job. We’re going to try and help. This isn’t really a guide, it’s more of a base to find what you might need. Enjoy and good luck.
First off, what can Excel do? Well, that’s a big question. In a nutshell, Excel is a spreadsheet program; a very advanced calculator, a statistical analysis tool and way of storing and manipulating information.
We’re not going to re-write guides available elsewhere on the internet, instead, we’re going to make sure you know where you can find the best information. Please note, i2i Recruitment takes no responsibility for the content on third-party websites.
Getting to know Excel:
- The main menu, or Ribbon. Read more >
- Workbooks – These are the main files that hold all the information. Read more >
- Worksheets – Each workbook has to have at least one worksheet, this is where the data is written and manipulated. Read more >
- Formatting Cells – Cells are where data is stored. In order to make them work correctly, or at least make them work better for you, it’s best to format them. This could be making sure the data input has the correct number of decimal places, the time is in the correct format, the price of something actually has the £ symbol or many other things. Read more >
- Printing – A spreadsheet can be a difficult thing to print. They quickly become larger than A4 can handle and what happens on page 2 with table headers? Well, you can repeat headers and spilt pages easily with the print and page options. Read more >
- Errors and how they’re shown – Knowing what errors mean is useful and it can save you a lot of time. Knowing that ‘#######’ means the cell is too small means you’ll save time trying to figure out why the formula you entered is incorrect (because it probably isn’t). Read more >
Functions – the real power behind Excel:
- Count and Sum – A simple and powerful function that allows you to quickly count cells. It can count the total number, blank cells, or cells with specific contents. This could be a specific ‘Y’ or ‘N’, another function, like ‘>9’ (greater than 9). Read more >
- The ‘If’, ‘And’, ‘Or’ Functions – Very powerful functions that can be useful for a lot of different reasons. An example of the If function could be checking if another function hits certain limits. You can also ‘nest’ Ifs and use more than one in one formula.
- Relative, absolute and mixed cell references – Knowing the difference and how to utilise them correctly can be extremely useful. Read more >
- Text Functions – These can be really useful for something like customer information. For example, if you have a huge column of text but each entry had a 7 digit code in front of it, you could use =RIGHT(A1, 7) and only the first 7 characters would be shown. Read more >
- Lookup and Reference – VLookup is a powerful function in Excel. It allows you to quickly find the value from a table based on the value you choose. This will also run through Match, Index, and HLookup.
- Average, Median, Min, Max and more – Statistical analysis is very useful and is one of the main reasons for using Excel. Read more >
- Find and Select – A powerful tool that means you can quickly search the workbook or sheet and replace certain values with other values quickly. Read more >
- Data Validation – Validating data can be a very important process when inputting large amounts of information. It can restrict what you or others can input into a cell meaning a phone number can only be numbers (not letters), or a number can only be between 0 and 10. It can also give you drop-down menus of options. Read more >
- Rounding – Rounding numbers allows you to automatically round long numbers to a set number of digits. Read more >
- Sorting data – For example, showing the data in alphabetical order. Read more >
- Filtering data – This can be used to show only data that meets certain criteria. Read more >
- Conditional formatting – This is automatic cell formatting depending on the value (e.g green when a positive number, red when a negative). Read more >
- Charts – Read more >
- Pivot tables – A complicated and powerful feature that a lot of companies use. They allow you to reorganise and summarise whole (or select parts of a) tables without changing the original data.
We hope this quick guide has been helpful. If you think we need to add more information, please let us know at firstname.lastname@example.org. Now it’s up to you. Play with Excel to learn as much as possible.
If you don’t have Excel at home, try Google Sheets. It’s not 100% the same or quite as powerful, but for a free program/application it’s excellent and you can get some practice. If you struggle knowing what to practise with, why not create a sheet that mimics your bank account incomings and outgoings? You can have a couple of columns to add transactions and dates, another few for ongoing balance. You could even add more details like your monthly salary and work out how much is going to be left after upcoming bills.
Finally, if in doubt about anything in this guide, Google it. There are thousands of sites out there offering free Excel help. Try and enjoy it.