Microsoft Excel 101: Helpful Tips For Excel Users
Microsoft Excel is a powerful and robust application — but are you a bit intimidated by it?
It’s OK if you are, you’re not alone. Don’t let that stop you from learning the basics of using Excel, so you can become a more effective and productive user.
Get started right now by checking out this Greater Bethesda Chamber Of Commerce webinar on Excel basics, featuring our CEO, Mike Shelah:
What Is Excel And How Is It Organized?
Excel is an electronic spreadsheet program that’s used to store, organize and manipulate data.
You enter data into Workbooks that are made up of individual Worksheets. In the Worksheets, you enter data into cells that are organized into rows and columns. Excel data can consist of text, numbers, dates, times, and formulas.
Why Should You Use Excel?
If you or your employees work with financial data, it’s a great tool to use for:
- Basic mathematical operations like adding, dividing, and multiplying.
- Finding values like profits or losses.
- Calculations like averages, percentages, and number counts.
Performing calculations in Excel is only the tip of the iceberg. There’s much more you can do like creating charts and graphical layouts to make it easier to recognize trends and more easily analyze data.
The Basics Of Navigation
What’s great about Excel is that it has the same setup as other Microsoft products you’re familiar with. You have tabs across the top, where each tab has a corresponding ribbon with many functionalities to choose from.
The Quick Access Toolbar is a drop-down menu where you’ll find functions that you commonly use like Print and Save. You can also customize the Quick Access menu with other functions you use on a regular basis.
The Formula Bar is located underneath the ribbon next to the Name Box that shows you where your cursor is located on your Worksheet. The Formula Bar is important because it’s what calculates the math for you. Excel does the calculation and displays the answer in the cell you choose. The Formula Bar also shows you the contents of the particular cell you’re in.
How To Add Data
There are three ways you can add data to your Excel Worksheet. You can:
- Type in the data,
- Copy and Paste data, or
- Import data from other sources.
Importing is useful if you have a large amount of data. For example, if you have customer lists in a database, you can even pull this into Excel.
You can enter data into only one cell, into several cells at the same time, or even on more than one Worksheet at once. And, as mentioned, the data can be numbers, text, formulas, dates, or times.
On your Worksheet, simply click a cell and type in the information that you want to enter. Then hit ENTER or TAB. If you typed in a date, Excel will recognize this and format it the way you’ve specified in your default settings.
How To Use Formulas
Excel computes the correct answer when you enter a formula into a cell. Once you’ve done this, it recalculates whenever you change any of the values. The way Excel knows that you’re entering a formula is by starting with an equal sign. Then you follow the equal sign with a SUM or AVERAGE.
For example, C2: =A2+B2 means that the number in C2 is what occurs when you add the numbers in A2 and B2.
You can type this in manually, but now Excel has great functionalities to help you do this. The simple way is to put your cursor in cell C2, hit = and type in A2+B2. The numbers in A2 and B2 will be added, and the SUM will be entered in cell C2.
Note: You always want to calculate using the actual cells rather than typing in numbers like 1 + 2, etc. The reason for this is so you can go back at any time and change the values in cells and the formula will calculate with the new numbers.
Let’s say you want to add a bunch of numbers together in your Worksheet. You can type = sum (a1:a5) in the cell where you want the answer to appear. Or you can do this and drag your mouse across the cells you want to add. Type =sum ( and drag your mouse across the cells and hit ENTER. The sum will appear in the cell without you having to type in all the numbers. When you put your cursor on the cell, you will see the actual formula you just created.
There are many ways to do the same thing in Excel. It’s like this across all Microsoft products. You can go to the Ribbon at the top to “Auto Sum” to perform the same calculation. Select a cell next to the numbers you want to add, click AutoSum on the Home tab and press Enter. Do what works best for you.
Once you create a formula, you can copy and paste it into another cell. You can also copy and paste formulas into different Worksheets as well, which can save you a lot of time.
With Excel, you can format your Worksheets much more easily than you could before. You can use document themes throughout the Worksheets in your Workbook to present a professional and consistent appearance. You can also apply predesigned formats as well.
Let’s say you have a Worksheet with many rows that are hard to read. You can go in and create fill colors and more to differentiate the rows, columns, and headers to make reading much easier.
You have options to create borders around cells, rows, or columns from the drop-down menu. You can also shade cells with a solid background. Don’t forget that you can change the style and types of fonts. Right-click the text, and a drop-down menu will appear where you can make these and other selections easily.
If the data isn’t complex, you can easily read it, but if you have a lot of data, creating a chart will help you better analyze it. You can select specific cells, rows, and columns for your chart. One way to do this is to highlight the data and go to the top ribbon to select the type of chart you want to create.
With Excel, you have a “recommended charts” option. Excel will help you choose the chart that best suits your data.
You can then go in and further customize your chart in the “Chart Tools”. You can change the color scheme, 3D effects, shading and more. If you change the data in the cells in your Worksheet, your chart will reflect the changes.
You may be used to creating tables in Word or PowerPoint. Some people think the format in Excel is already in a table, but it’s not; at least until you tell it to do so. If you want to do this, select your data, go to “Insert” and select “Table.” Similar to other Microsoft products, tabs will appear to help you format your table.
When dealing with lots of information, it can get unruly trying to work around various rows and columns. This is where Viewing Worksheets can be helpful. You can freeze a portion of your worksheet with “Freeze Panes” to more easily view it.
You also have the ability to “split” the data to view different parts of your Worksheet. You can compare two Worksheets in the same Workbook or even in different Workbooks by viewing them side by side.
Saving and Printing
If you have Worksheets that are so large they won’t fit on one page, go to “Save As” and decide on the name, where it gets saved, and go to “Print” where you can save the file to a pdf that you can send.
You can select options for printing the entire sheet, part of it, resizing it, and more to suit your needs. Going to “Page Setup” will allow you to shrink the entire Worksheet down to a size that’s more manageable for printing.
Sharing & Security
In Excel, you can share Workbooks and Worksheets with others and password-protect them. The people you send them to need to know your password to open them, whether you send them via email, share them on your network, or via the cloud. From within Excel, you can designate who can access your Worksheets and Workbooks, and also whether they can edit them or not. There are a variety of parameters you can set within a Worksheet.
For example, if you want to hide employees’ salaries, you can hide this section when you share it. Or, you can let people see your data but lock it down, so they can’t change it. You can also protect your Worksheets and Workbooks to keep them secure from non-authorized users.
The Quick Analysis Tool
When highlighting data, click on the Quick Analysis button to create a chart, highlight specific cells, and much more. It doesn’t give you the functionality you’ll find in the Ribbon, but you can get things done quickly and easily with this tool.
Don’t Be Intimidated By Microsoft Excel
No matter how complex an application may seem, all it takes is a little time and effort to get to know it a little better. The more time you invest in learning the many features it offers, the more of an expert you’ll become.
Advantage Industries is a Managed Security Service Provider (MSSP) providing practical networking and software solutions, as well as web site and application creation services. For nearly two decades, Advantage has worked collaboratively with hundreds of clients in understanding complex business processes, identifying needs, and providing recommendations tied with sound technology solutions custom-tailored to their business.