This is a resource page for anyone wishing to learn about excel spreadsheets and also how to use them to their full potential. Excel spreadsheets are essentially tables which arrange, manipulate and display numbers and information in organized rows and columns. Their popularity stems from the fact that they are easy to master, practical and also very useful.
Microsoft Excel is the leading spreadsheet in the world and by far the most widely used, therefore this page will focus primarily on it rather than any other applications. This page contains tutorials, useful tips as well as advice and guidance on how to get the most out of your Excel software.
The importance of planning your Excel spreadsheet
Before you do anything it’s good practice to sit down and think about the following points;
What information will you be entering.
How much information will you be inputting.
What is the actual purpose of the spreadsheet? Are you aiming to write reports or simply create charts or graphs that you want to print out.
Thinking about and planning your spreadsheet before you do anything can save you a lot of time and headache later on. One of the best ways to do this is to clearly identify at the start what you want to achieve and then work backwards from there, highlighting the key points that need to be done to get there.
Set the goals and objectives of the spreadsheet, what is its purpose.
Familiarize yourself with the actual problem you are trying to solve.
Design the layout of the spreadsheet, think about how you want to look and where you want certain information to be. It’s always best to have the same layout running throughout the sheet as this will make it a lot easier to read and it will also be simpler and quicker to input data into. Another good tip is to draw a few layouts on a piece of paper first before you decide on a particular design. Other points to consider are;
Focus on having a design that is practical and allows you to enter data quickly and accurately.
The actual size of the sheet.
How it will look when its printed out.
What color and fonts do you want to use.
Appropriate headings for rows and columns.
Do you need grid lines?
Identify what output (results, reports and information) you want the excel spreadsheet to produce, for instance the average price, total purchases etc.
Do the same for the inputs, think about and identify what facts and information you need to enter to get the output you want.
Determine the functions and formulas that need to be used to calculate any inputs you make and that will then work out your desired results.
Tip - if you create a excel spreadsheet, start using it and then later on find it unsuitable and want to change it then the good news is that you can. Making alterations is simple and easy using Excels numerous updating and editing features.
The basics; what are excel spreadsheets used for
Despite the fact that it is essentially a business tool, they are widely used in both personal personal and business computers. They have a variety of practical uses including;
Creating colorful graphs and charts
Performing automatic calculations
The advantages to individuals and companies of using computerized Excel spreadsheets
They are excellent tools for creating ‘what if’ scenarios, where you can look at how changing some data will affect other figures. For instance if one number or figure is changed, then calculations are revised and updated automatically everywhere within the spreadsheet.
They are excellent tools to organize and manage large amounts of data, thereby assisting tin any decision making process.
Allows a user to visualize data quickly in various layouts and formats i.e. bar graphs, pie charts etc. This means users can immediately and easily analyse information and statistics.
Graphs or charts created in excel can comfortably be imported into other Microsoft tools like PowerPoint or Word.
What is a workbook
These are files in which you can store data, they can contain one or more worksheets. It can be likened to a filing cabinet where you can file, organise and store individual pages of data.
A individual workbook is used to group together related data on a particular subject i.e. ‘expenses’ or ‘yearly budgets’ etc. Any worksheets created within a workbook can be linked, referenced and made to work together.
How many worksheets can a workbook have?
In Excel 2007 theoretically there is no limit, with the only limitation being the size of your computer memory ands hard drive space. However from a practical and professional view point it is not a good idea to have too many i.e 100’s. As this is not a good way to use spreadsheets.
A new workbook in Excel 2007 is defaulted to have three pages, although as explained above you can add as many as you want.
In Excel 2003 and previous versions the limit was 255 worksheets.
What is a worksheet
This is essentially another name for a spreadsheet. It is basically a group of cells where you can keep store data that can be manipulated.
Tips for worksheets
You can input and edit data on several worksheets simultaneously.
Their default settings and layouts can be changed and customized even after you have started using them.
The main uses of spreadsheets
There are two separate categories of spreadsheets, the first are sheets which are regularly updated with information and data. These could be for monthly expenditure accounts or a customers invoices. The second kind of spreadsheets are those that you do not want to update regularly and are simply used to summarise reports at a certain point in time.
ENTERING DATA INTO A EXCEL SPREADSHEET
The simplest way to input raw data is by clicking on a cell where you want the information to go, then entering your data and finally hitting the return key or clicking on another cell. Any cell that you select is immediately highlighted, you can choose other active cells by moving your mouse or by using the arrow or tab keys.
Steps for inputting data into a series of cells simultaneously;
Highlight the cells you wish to update.
Do not click or move the cursor.
Type your data into the first cell you highlighted .
Tip – try not to leave empty rows or columns, as these can hinder some of Excels in built features.
A brief history of Microsoft Excel
The original version was launched in 1984 and one of the first spreadsheets to use a Graphical User Interface. This feature made it much easier for users to enter data and Excel quickly became popular. The next version was released in 1987 as part of the Windows operating system. By the late 1980’s there were many spreadsheet packages (i.e. Lotus 1-2-3) on the market launched by different software companies, but Excel remained the market leader. Other key Excel milestones are;
September 1990 – Excel 3.0 launched
April 1992 – Excel 4.0 launched for Windows 3.1
December 1993 – Excel 5.0 launched
July 1995 – Excel 7.0 launched
January 1997 – Excel 8 released
OPENING A EXCEL WINDOW
After opening Microsoft Excel a window called ‘Book 1’ will appear. In the centre of the page (most of the page) there will be a grid of rectangular cells, these collectively are known as the worksheet. A worksheet is where you can enter and organise your data. Horizontally and vertically the columns and rows will be labelled with numbers and letters. Each particular cell in the grid will have its own ‘cell address’ or ‘cell name’ (for instance ‘A3’) this denotes its position in the worksheet. A number is associated with each row and a letter is associated with each column. The different sections of the Excel window are listed below:
If you select a cell by clicking on it or if you enter data into it, then it will become active.
This is at the top of the workbook, and when you create a new workbook it is automatically set to and given the name of ‘Book 1’. The title bar displays the name of the workbook and can be changed and indeed should be to reflect what the book related to.
This is a list of the users most frequently use commands, and is at the top of the page just below the title bar. It will allow users quick access to the various commands throughout Excel. If you click on any of the commands you will get a drop down menu appear. Like most features in Excel the menu bar commands can be changed and customised to suit your needs.
This is a blank link above the actual worksheet. When you enter data into a cell it will automatically appear in the formula bar as well. The formula bar lets you see the formulas, values and calculations that are being carried out. Users are able to edit and change the information in the formula bar.
This is above the worksheet and lists buttons that carry out frequently used functions. The most common used functions being; new, open, save, cut, copy and paste.
Column header button
These are alphabetical letters listed directly above (horizontally) the worksheet and are used as a reference for a whole column. If you click on the letter you will select a whole column.
Row header buttons
These are numbers listed to the side of the worksheet (vertically). If you click on a number you will select a entire row.
There are vertical and also horizontal scroll bars at the side of worksheet to help you navigate sideways or up and down.
Every workbook will by default contain three similar worksheets that are referenced as Sheet 1, Sheet 2 and Sheet 3. These tabs are normally listed at the bottom of the screen and can be opened by clicking on them. The worksheets tabs can be renamed.
USING EXCEL AS A CALCULATING TOOL
Excel has features that are similar to that of a sophisticated calculator. Users can carry out complex calculations and then store and recall the results back at a later stage. However there are significant differences between the calculator and Excel. For instance a single Excel worksheet can store millions of bits of data in its cells and it also allows you to view these bits of information all at once and to link them to each other.
Calculations are carried out using formulas, these can be very simple ones or more complicated ‘recipes’ for manipulating figures, financial modelling or statistical analysis.