“Computers are like Old Testament gods; lots of rules and no mercy.” - Joseph Campbell, The Power of Myth
While it seems that the younger generations seem to know everything about technology, the older generations often struggle. However, there are programs that can make your life easier.
Whatever operating system your computer uses, office suites such as OpenOffice, LibreOffice, or Microsoft Office can allow you to create charts, graphs, and spreadsheets.
Most people, however, using a PC or Mac, will be accustomed to using Microsoft Excel, or not as the case may be...
So how do you use Excel and how can you create a spreadsheet?
Whether you want to format a spreadsheet, create a spreadsheet, print out a sheet, learn some keyboard shortcuts, hide columns, copy and paste data into a pivot table, insert functions, or do calculations, here’s a crash course on how to create spreadsheets.
Discover all the features of Excel!
An Introduction To Microsoft Excel
Excel is a programme and part of the Microsoft Office suite (which includes Microsoft Word word processor and PowerPoint presentation programme). Excel is used for creating spreadsheets and graphs on PC, Mac, Android, or Linux. It allows you to organise and share data through spreadsheets and charts.
Check out online IT courses here.
Excel isn’t the only programme for making spreadsheets, but it’s definitely the most popular. Students in primary school are already learning how to use computers and office software.
Knowing how to create a spreadsheet is useful for jobs involving mathematics, economics, sciences, but also in everyday life.
You have to pay for Excel. You can get Microsoft Office for £59.99 per year with Office 365 Personal. This includes Word, Excel, PowerPoint, Outlook, Publisher, and Access. There’s also Office 365 Home for £79.99 per year for multiple users. You can also get a smartphone or tablet version.
Once you’ve downloaded it, you can open Excel by double-clicking on the desktop icon. If you already have a page open on Excel and you want to create a new one, you just need to go to “File” then “New”. If it's an application you see yourself using often, then the best thing to do is to pin it to your task bar at the bottom or add to Start menu. To do this, right click on the Excel icon and select what you want to do. By having it on your taskbar, you can flick between Excel and another app with ease.
The Layout of the Programme
Once the programme is open, you’ll see a page of cells, which is the basis of your spreadsheet. The boxes are known as cells and the columns are arranged by letter and the rows by number. You'll notice that the page is infinite, i.e. the cells and columns just keep on going to accommodate even the biggest of spreadsheets, so once A-Z have been used up you will start over on AA, AB, AC, etc... (Too much? Okay let's slow it down and focus on some smaller tasks!).
Putting data into your spreadsheet is so simple and all you need to get started with a basic spreadsheet.
Let's Create A Simple Spreadsheet!
Right, let us jump us straight in and learn how to create a simple example spreadsheet, step by step.
- Open Excel
- Select 'Blank workbook'
This will present you with a fresh, workbook
- Familiarise yourself with the cells
You can try scrolling from left to right, up and down and getting a feel for how the program works
- Enter some data
Most people will start their table in cell A1, using the first row to add headings or criteria, and the same for the column A. See how you can type a word (or value, as it's called) in cell A1 then press enter and be taken to A2, A3, etc... If you want to work your way along a row, then instead press the tab button to skip to the next. You can equally use the keyboard's arrows to move around the workbook when not editing a particular cell's data.
- Rectify any mistakes
If you notice some errors in your spreadsheet, you can delete data by clicking on the cell and pressing the delete button. If you notice that an entire column is wrong, you can click and hold your mouse whilst hovering down the cells and selecting multiple to delete. This can also be done for non-consecutive cells by holding down the tab button and choosing the desired cells.
- Create a total
Supposing the simple chart you are creating is for something basic like adding up how much stock you have in your virtual clothes shop, then you might like to have a column that lists garments like so: Jumpers, Tshirts, Trousers, Skirts, Shorts. Then, in the next column (i.e. in the nearest right column, displayed vertically), you'll have your stock count: 5, 4, 6, 2, 4. To add these numbers up, you will place your cursor in the box directly underneath the list of numbers and type =SUM( and then, at this stage, you will either manually type in the cells you wish to add up or you will use your mouse cursor and click and drag again to highlight the required fields. Your total box will then look something like this =SUM(B1:B5) and you can hit ENTER for the calculation to do be worked out and the total displayed. If you aren't sure you can remember the formulas, then don't worry as you can use the top menu to select your formula of choice!
And that is it! Your first Excel spreadsheet complete. Let's look at some of these processes in a little more detail and see what more you can do with the application by Microsoft.
Want to see a visual tutorial covering the basics? Then head over to YouTube and check out a video on creating a simple spreadsheet.
Organising Data in a Spreadsheet
To create a spreadsheet, you need to organise your data into columns and rows so that the spreadsheet makes sense.
The table can have one set of data or multiple entries.
A simple table has one type of data. This could be the months of the year as a header and the names of people who have their birthdays each month. You can use both text and numbers in Excel.
A table with multiple sets of information allows you to combine different types of data. You could have the month of the year on a given row and the expenses across the columns. In this case, you may need to include the month and also the types of expenses (rent, shopping, etc.).
Formatting a Table
Once the data’s in the cells, you can create a more obvious table if you so wish. You can use the “borders” option in the toolbar but there are a lot more functions than that.
To create a table, you need to start by selecting all the data you want to include in a table. You can do this by selecting the top left cell and dragging to the bottom right cell.
You can also do this by pressing shift. First click on the top left cell, press and hold shift and then click on the bottom right cell. This will select all cells from the top left one to the bottom right one.
You can then just click on the “Insert” tab and then “Pivot Table”.
A window will pop up and you just need to click “OK” and check “My table has headers” if necessary.
And your table is ready!
Find out more about logical operators in Excel.
Changing the Design of your Excel Table
A spreadsheet doesn’t need to be dull. Excel allows you to format your data and make them clearer to the reader with colours and formatting options.
You just need to click on the “Design” tab. You’ll only see this when you have a table selected.
You can then go to the “Table Style” section or “Quick Styles” to choose the colours that you want.
You can also resize the table, delete duplicates, etc. You can select cells you want to keep, delete, or highlight, etc.
The header row can be used to name different columns. If you unselect this, the first row will disappear. In most cases, it’s a good idea to keep the entire row. This will allow you to add data at the end of your spreadsheet.
You can alternate the colours between rows. This makes your table easier to read. By alternating colours, it’s harder to skip a row.
The “First Column” and “Last Column” boxes allow you to put these columns in bold. You can also alternate the colours of the columns just like with the rows.
Once you’ve finished, you can just go back to the main tab.
Filtering Table Data
In the "Design" tab, you can also select the filter button. This allows you to filter and sort data in ascending or descending order.
You just need to click the arrow next to one of the headers and a dropdown menu will allow you to choose:
- Sorting from smallest to largest.
- Sorting from largest to smallest.
- Sorting by colour.
- Numerical filters.
Your data will be sorted according to the criteria you choose.
Doing Calculations in an Excel Spreadsheet
One of the best features of Excel is its ability to do calculations. Whether you want to calculate the sum, multiply, or another function, there are shortcuts you can use to save you time.
If you’re going to do any calculations, you’ll need to start by typing the equals sign (=) into the cell. This indicates that you want to do a calculation.
You then need to select the cell that’s the object of your operation and use operations symbols and hit enter. The result of your calculation will be automatically displayed in the cell.
Here are some of the operations:
- Multiply: an asterisk (*)
- Subtraction: the minus sign (-)
- Division: slash (/)
- Percentage: the per cent symbol (%)
Just like in maths, you can use multiple operations at once.
You can also copy the formula into the following cells. To do this, you just need to click on the first cell and then click the small square on the bottom right of the cell. Drag this into the cells below. The formula from the first cell will be copied and adapted into the following cells.
You can also click the “fx” button to do an operation.
How Do You Export an Excel Spreadsheet
The Excel format isn’t great for presenting a spreadsheet or a graph. The best way is to present your data on another document.
So how do you insert an Excel file into a word processing document?
To get started, you can use the design tab. This allows you to create a clean table. You can also merge cells if you need to.
You can save your table as a .pdf file if you want. You just need to change the document format when saving.
You can also insert the table directly into a Microsoft Word document by copying and pasting the table into the word processor. This is also true for graphs. You can do this for both Word and PowerPoint. You should now know how to create your own spreadsheets and tables.
Did you know that, instead of selecting a blank worksheet, you can also choose from a range of templates with pre-formatted sheets?
If you feel like you need more help with Excel tools, consider getting in touch with one of the tutors on Superprof. Superprof has plenty of tutors in the UK who specialise in IT and the Microsoft Office suite. By searching for what you want tutoring in and where you live, you'll be able to find all the tutors offering tuition in your area.
There are three types of tuition: face-to-face tutorials, online tutorials, and group tutorials.
The first type is the most cost-effective and expensive but you'll get tailored tuition. The second is when your tutor teaches their lessons over a programme such as Skype29 and they tend to be cheaper. Finally, group tutorials are usually the cheapest because you share the lesson with other students but you won't get as much one-on-one time with your tutor.
Fortunately for you, most tutors offer the first hour of tutoring for free so you can see if they're right for you!
Search for online IT courses here.