Microsoft Excel for Beginners
Microsoft Excel is spreadsheet application that can be an excellent tool to calculate financial and statistical information. It’s a tool that is used in many companies as well as households. But it can be very frustrating and confusing to someone who has never used any type of spreadsheet software. In this hub, I will explain some basic knowledge that is needed to use Microsoft Excel and give you a few tips and tricks that I find useful when using Microsoft Excel.
NOTICE: It is important to inform you that all the instructions that I have put in my Hub are for versions of Microsoft Excel 2007 or higher.
What are Spreadsheets?
Spreadsheets simulate an accounting worksheet. They are made up of a grid of rows and columns. In the following image, you can see the different parts that make up a spreadsheet. In a cell you can type text, numbers, or formulas, which uses content from other cells. Spreadsheets are useful because when you enter a new data, the formulas are automatically updated.
Using the Help Option
If you are unsure of anything, Microsoft Office already provides you with numerous of answers in the Help Option. You can type your specific question or you can browse the most common questions asked in Microsoft Excel, like formulas, or charts. You can access Excel Help by pressing F1 on your keyboard or by clicking on the question mark icon on the top right corner.
A Few Helpful Microsoft Excel Products
How to enter data?
When entering data, it is important to know how to do it correctly. A tiny mistake in typing can be catastrophic, so one must be careful. It is also important to know which operators to use to perform the basic mathematical operations. We use the plus sign (+) for addition (2+2= 4), we use the minus sign (-) for subtraction (5-3= 2) and negation (-1), we use the asterisk (*) for multiplication (3*3= 9), we use the forward slash (/) for division (2/2= 1) and we use the caret (^) for exponential (4^2 = 16).
When typing an equation or formula, the first thing that must be entered is an equal sign (=). The equal sign makes it possible for Excel to calculate your equation. Another thing that you must know is that the use of parenthesis is extremely important. If you don’t place a parenthesis in the right position, you will end up with an entirely different answer than the one you desire. For example, 5+2*3 (= 11) is not the same as (5+2)*3 (=21). To correctly use the parenthesis, you must remember the Order of Operations. First, you perform the calculations inside the parenthesis, then all the multiplications and divisions (from left to right), and finally all the additions and subtractions (from left to right).
How to use Formulas?
To correctly insert a formula, you must first know a few basic rules. There are two ways in which you can create a formula, by typing or by clicking. When clicking, you just select with your mouse the cells that you want to use. When typing, it is important to learn how to refer to the cell that you want. For example, if you want to add cells B1 and C1, you type =B1+C1, press enter and the cell provides you with the answer. When referring to cells, you use the colon (:). Here is a little table to understand how to refer to cells.
Click on A1
A1, A2, A3
Click on A1, and drag to A3
Click on A1, and drag to B1
Click on A1, type in comma or hold down control key, and click on B3
A1, A2, B1, B2
Click on A1 and drag to B2
Microsoft Excel even provides us with a set of predetermined formulas that make calculating data even simpler. You can look over these formulas in the Formulas Tab (it’s even divided it by categories). But sometimes it is easier to just type in the formula. To make things a little easier for you, here is a screencap of a spreadsheet that I created, where you can find some of the formulas that I find are the most common and how to use them.
Now, let’s put what we learned into practice!
Let’s suppose that you have opened a brand new store, let’s call it YadiraE’s Place, and you need to know how much money you are making on its first quarter. Well, you create a chart with the earnings of each product for the first 3 months. You add the columns and to determine the total for product and add the rows to determine the monthly revenue. Once you have added all your information, it’s time to create a chart so that you compare your total revenue.
For creating a chart, all you have to do is select the data and go to the Insert Tab and select the type of chart that most suits you. Here are a few examples of the type of charts that you can create using Microsoft Excel.
Tips and Tricks for Using Microsoft Excel
- Always save the files in Excel 2003-97 compatible mode. This is just in case the file is open in a computer that doesn’t have Microsoft Excel 2007 or higher.
- Keyboard Shortcuts – Hold down ALT to see the available shortcuts.
- Inserting Rows and Columns – There are a few ways to do this, but the easiest way is to select the column (or row) that will go after the desired column (or row) and select Insert on the Home Tab, or simply right click and press Insert.
- Hide rows (or columns) – Select the row (or column) that you want to hide and select Format and select Hide or Unhide (Home>Format>Hide/Unhide). Even easier, simply right click and press Hide or Unhide.
- Autofill – Instead of entering data manually, you have an option where Autofill does it for you. NOTE: This option is for data with a pattern, like a sequence of numbers or the months of the year, etc. You select the data that you want to Autofill and move your cursor to the bottom right corner where you will see a little cross, and then you will drag down until the desired input.
- Sort and Filter– If you need to sort data, either from smallest to largest, from A to Z, or a custom sort, or create a Filter, all you need to do is select the data and select the Sort and Filter Button (Home>Sort & Filter). When you use the Filter option, in addition to sorting data, you can also sort and filter by color, and you can filter the data by what it contains, if its equal or not equal to a certain number, greater than or less than a certain number, and many others.
- Freeze Panes – When you have a spreadsheet with numerous rows or columns, it can be annoying to be constantly going back and forth from top to bottom. This is where the Freeze Pane option comes in handy. (View Tab>Freeze Pane)
- Format Painter – The format painter is an option to match format of a cell to those of another cell. This option can be found on the Home Tab.