Techie Corner: Excel Basics
Need Help Learning Excel?
Did you already know Excel before reading this hub?
Excel at Excel!
I'm not a professor, nor do I work for Microsoft, but I do have years of experience with tech support and working with Excel. It's a very useful program and while it might take a little practice and even some experimenting, you'll find it to be an extremely user-friendly, fascinating, electrifying and intellectual experience.
Okay, just kidding. There's nothing elecrifying about it, but it is a great tool.
Let's Not go to Extremes...
Unless, of course, we are going to extremely basic!
To open Excel, either double click on the icon, if it is already on your desktop, or go to Start, programs, Microsoft Excel.
You will be brought to a blank spreadsheet. You can start working right on this spreadsheet. Multiple worksheets are called a Workbook.
The boxes that you see in the spreadsheet are called cells. A bunch of cells going across are called rows and a bunch of cells going down they are called columns. Rows are labeled with numbers (on the left hand side) and columns are labeled with letters (on the top of each column).
To select an entire row, click on the number of the row you wish to select.
To select an entire column, click on the letter of the column you wish to select.
To type in a cell, just start typing. To edit a cell you already typed in, you can do so in three ways.
- Double-click on the cell and position your cursor where you want to edit.
- Hit F2 and position your cursor where you want to edit. Or
- Position your cursor in the formula bar and edit.
To move from cell to cell, you can use your arrow keys, tab keys or enter keys, depending on where you want to move.
Remember to Save
You will hear me nag about this in every tech document I write, but it is important to save your work. The last thing you want to do after you finish working all day on a big spreadsheet, is lose all the information and then hear me say how you should've saved it!
Do yourself a favor, and remember to save the spreadsheet every ten minutes or so. To do this:
- Go to File
- Choose Save and if saving a spreadsheet for the first time, a dialog box will appear as shown below.
- Choose where you want to save the document. (Usually in the folder called ‘My Documents’), by clicking on the ‘Save in’ drop down box and choosing your folder
- Name the file where it says ‘File Name’.
- Then Select Save.
- Once the file is saved for the first time, any time you just press ‘save’ it will automatically save the file.
Another Save Feature
Another save feature is ‘Save As’. This feature is useful if you want to take the same file, but save it as a different file, leaving you with two (or more) separate files. To do this:
- Click on File.
- Click on Save As.
- The same Dialog Box will appear as shown above.
- Choose where you want to save the document. (Usually in the folder called ‘My Documents’), by clicking on the ‘Save in’ drop down box and choosing your folder.
- Name the file where it says ‘File Name.’
- Select Save.
- Once the file is saved for the first time, any time you just press ‘save’ it will automatically save the file.
Closing the Worksheet
Once you have saved your work and wish to close the worksheet, just click on the X in the upper right hand corner of your screen, or go to File>Close. Then, to completely exit out of Excel, go to File>Exit.
Okay. This is the extreme basics. In a minute, we'll move up to ‘The Basics’. No need to be alarmed. It's not much more difficult!
Sample Screen Shots
If you are familiar with Microsoft products, you will recognize some of the features, as they are similar in Excel. If you are not familiar, it is never too late to learn.
When you are printing a worksheet, there are more than a few ways that you set your page up. If you click on Page Layout, the following ribbons will show many different available options.
If you click on Margins you can either choose from the buttons shown or you can choose ‘Custom Margins’ where you will see a screen pop up.
This screen enables the page set up all in one dialog box. (You can also access the this screen on the 'Layout’ ribbon by clicking on the arrow under the Page Layout Section).
Page Tab Contains:
- Orientation- Here, you can select Portrait or Landscape - Just choose the button you wish and then click okay. (You can also configure this directly from the ribbon in ‘Page Layout).’
- If you need to make the page smaller or larger, you can choose to adjust the percentage of its normal size. Just click the up or down arrow next to %normal size.
- Fit to ___ page - To use this feature just click on the radio button next to ‘Fit to ___pages, and adjust accordingly. This is great feature, just be sure to preview it by clicking on print preview before actually printing it. It is possible to make it illegible, by making it too small.
- Paper Size- By default, this usually is left as letter. (You can also access this directly from the ‘Page Layout’ section). If you need to print on Legal, or envelopes, etc. Be sure to click the down arrow here and make the correct selection.
Click on the up or down arrows next to the margins you wish to change. If you wish to center your spreadsheet, place a check mark in ‘Horizontally’ or ‘Vertically’ or both on the bottom of this tab. Then click OK.
This option is used to create headers or footers that will not appear on the screen of your worksheet, but will appear when you print it. It could be used to create a title, put the date and time, page numbers, etc. (You can also access Headers/Footers from the Insert menu).
To use this, click on Custom Header or Custom Footer and a dialog box will appear.
Type in the space where you want it to appear. For this (the header), if you wanted the upper left section of your spreadsheet, you would choose left section. You can type in there or just click on one of the icons shown. In order, those icons from left to right are used for:
- Changing the font
- Inserting page numbers
- Number of pages
- File Name
- Worksheet Name
(You can also set your print area directly from the ‘Page Layout’ ribbon).
Suppose you had a 300-row spreadsheet, but only wanted to print the first fifty rows. In Excel, you have the option to choose the first fifty. Highlight the rows and columns you wish to print, select File>Print area> Select Print Area. Only the selected rows/columns will be printed. The rest will stay on your spreadsheet, but will not show up on print. If you wish to undo this option, select Page Layout>Print area> Clear Print area.
To customize your worksheet’s print area, click on the blue and red box on the right hand side of ‘Print area’. This will bring you to your worksheet. Just highlight all the cells that you want to print and then click on the same blue and red box (now located on your worksheet). You will be brought back to the Page Setup-Sheet dialog box.
If you want certain Rows to repeat at the top, click on the blue and red box on the right hand side of ‘Rows to repeat at top’. Then just highlight the row number that you want repeated.
If you want certain Columns to repeat at the left, click on the blue and red box on the right hand side of ‘Column to repeat at top’. Highlight the column letter that you want repeated.
Under the heading ‘Print,’ if:
You want gridlines to show, place a check mark next to ‘Gridlines.’
You want Black and white; place a check mark next to ‘Black and White.’
You want Draft quality; place a check mark next to ‘Draft Quality.’
You want Row and column Headings; place a check mark next to ‘Row and column headings.’
Additionally, under the heading ‘Page order,’ you can choose which direction you want your page to print.
‘Down, Then Over is a good choice if columns are where you are labeling most of your information and it is more important to view consecutive information going across.
‘Across, then Down’ is a good choice if rows are where you are labeling most of your information and it is more important to view consecutive information going down.
To view how your spreadsheet will appear in print, select this feature. To do so, go to the Microsoft Office Button>Print Preview.
To print your document as click on the Microsoft Office button>Print>Print. A dialog box will appear.
This box will show the name of the printer for which it is printing. If you have multiple printers installed, you can change the printer here. You can view the status, type of printer and where it is printing.
Under the heading ‘Print range,’ you can choose all pages or click in the radio button for ‘Page’ and select the pages, for example, Page 1 to Page 3.
Under the heading ‘Print what,’ you can select to print the active sheet or entire workbook.
From this screen, you can also go to Properties to change any printer properties, the type of paper, landscape or portrait, print quality, print on both sides, watermarks, and source tray- (if your printer has more than one tray- I.e. Letter or Legal).
Suppose you just entered an extremely well thought out, intensely long formula. You then decided that you may have a better calculation, attempted to run the formula, but it did not produce the expected results. As long as you have not saved the document, you can just press undo. If you then changed your mind again and realized that you DID like the new formula, you can press redo. You can go back and forth as long as you’d like, but…that probably would not be the best way to be productive. This is located in the top portion of your screen on the quick access toolbar.
This is the same idea of the pair of scissors you have learned to use when you were just a little computer operator. Assume that you have a great, long formula in cell C3. However, you decide that you want this formula to be in F6 instead. You can just position your cursor on C3, right click and select ‘Cut’ (or go to Home>Cut (represented by scissors icon) and then go to F6 right click and click paste. Voila, C3 is now blank, but F6 now contains that great formula!
Copy is similar to cut, except that if you copy a cell-in this case A1, you can paste it into a new cell-A2, but A1 will still contain the data. It will NOT be blank!
Now that you have ‘cut’ or ‘copied’ data to the clipboard, you may want to ‘paste’ it somewhere.
- Click the cell of your choice.
- Right click and select ‘paste’, or go to Home>Paste (represented by a clipboard).
- To quickly clear All, Format, Contents or Comments, highlight the cells you wish to clear
- Select Home>Clear> and choose what you wish to clear.
Highlight Entire Worksheet
To quickly select your entire worksheet:
1. Go to the gray area between the 1 and the A and click there. Your entire spreadsheet will now be highlighted.
To delete cells:
- Highlight the cell (s) that you wish to delete
- Select Home>Delete. A dialog box will magically appear and give you options for what to delete.
- If you choose to delete cells, you can choose to shift the rest of the cells to the left or upwards, or just delete the entire row or column.
- Then press ok.
Okay, so you finally are finished with that ‘confidential worksheet,’ but your boss tells you that it is so confidential, that he wants it completely deleted. After you stop whining and complaining, delete it! To do this:
- On the bottom left, highlight the worksheets you wish to delete.
- Select Home>Delete. A dialog box will magically appear and provide you with some addional options. Choose Delete Sheet.
- You will need to confirm that you want to delete this sheet.
- If you are 100% completely positive that this worksheet is to be deleted and erased out of any existence, press ‘Delete.’ Otherwise, press Cancel, double check, get it in writing, get it notarized, repeat steps 1-3 and then press ‘Delete.’
Move or Copy Sheet
There may be many reasons why you would want to move or copy a worksheet. For instance, suppose that you have been creating separate spreadsheets in the same workbook consecutively every month throughout the year, using the same format and formula for each month. (Please excuse the run-on sentence). One minor detail is that you forgot April and May and had to add it at the end.
Not a problem! You can easily just create April’s blank spreadsheet and then copy it to create May’s blank spreadsheet. You still have to fill in the data, but at least the formatting and formulas were copied. So, you have finished April and May and now realize these months now fall at the beginning of your workbook, right before January. Since this workbook is supposed to be in consecutive order, this wouldn’t make much sense.
- Click on the spreadsheet you wish to copy.
- Either right click and select copy or go to Edit>Move or Copy Sheet. You will then be brought to this screen:
- Just click on Create a copy. Your new sheet will be placed now as the first sheet.
Move a sheet
In the above example, you just created April and May to go before January. To move this sheet to the middle, where it belongs:
- Click on the spreadsheet you wish to move.
- Right click and select copy or go to Edit>Move or Copy Sheet. Another screen will appear.
- Click on the worksheet that is placed before where you want your worksheet to appear.Then click OK. Your spreadsheet is now in order.
I know I wrote down $20,000 in that spreadsheet, not $2.00. Didn’t I? You probably did, but why not be sure? To find text or currency in a cell, you could search for it yourself, which is fine…if it is five rows. But what if it is five rows and 5000 columns? I would opt to use the find feature. It is a lot easier...and faster. To find a piece of text or data:
- Go to Home.
- Select Find.
- Type in the data you are in search of in the ‘Find What’ Field.
- Press ‘OK.’
In the previous example, if you find that you DID in fact write $2.00 in 47 different cells, you can quickly change that.
- Go to Home.
- Select Replace.
- Type in the data you wish to replace in the ‘Find What’ Field.
- In the ‘Replace With’ field, type in the data that you ACTUALLY want there.
- Choose ‘Replace’ to just replace it once or ‘Replace All’ to change it every time it appears.
Make sure that $2.00 shouldn’t appear anywhere else, because the replace function WILL replace ALL fields that have $2.00 in it, if you choose that option. If you are unsure, do not choose ‘Replace All.’
If you quickly want to move to a cell that you KNOW contains the data you need:
- Go to Home>Find.
- Select ‘Go to.’
- Type in the cell number that you wish to go to. You will be brought directly there.
Once you have mastered all of the information up to this point, you can start using Excel to create spreadsheets, an invoice, tracking logs…whatever you wish! It is a great tool to learn for both your personal and professional needs. We are now going to move onto more of the interesting stuff, and yes, some of it IS interesting…or at least helpful.
Maneuvering Through Excel
Maneuvering Through Excel
One of the benefits of Excel is the ability to manipulate the cells to automatically formulate your data each and every time. Whether it is having a certain group of cells add, subtract, multiply or divide for you or have the average displayed on the bottom right hand side of the screen every time you highlight those cells, it is fully automated depending on your needs.
Another benefit is the ability to automatically enter the information for you. For example:
Suppose you are creating a spreadsheet that will contain data for the next 36 months. Instead of spelling out month in each consecutive field, you can do the following:
- Type the first month in the first cell, either abbreviated or fully spelled out. For example, Jan or January.
- Position your cursor on the bulky part of the black box surrounding January.
- A crosshair will appear over the cell. When you see the crosshair, drag the cell downward or across (outside of the cell-if you drag in towards the cell, you will clear the data in that cell).
- The following months will automatically appear without you having to type them. Just drag outside the cell until you want it to end.
You have just begun to create your customized spreadsheet! You can also use this function with full dates or days of the week.
What is a Formula Bar?
In addition to just typing text in the cells, Microsoft Excel’s spreadsheets actually perform some very helpful functions! One of those functions is automating calculations. In order to automate these calculations, Excel uses some primitive and some very advanced built in formulas. Those formulas appear in what is called ‘The Formula Bar.’ (Screenshot above).
The item circled in red is the actual Formula Bar. That is the formula that is written in cell B7 (circled in blue). Although what you see in B7 is a number, it is actually the sum of cells B2 through to B6. The formula for that equation is =Sum(B2...B6), which is what is written in the formula bar.
If you ever wanted to see all of the formulas that exist in the actual cells, you could click the CTRL+~ and it would change the actual VALUE of the cells to a formula.
Notice how all of the values in the Monthly Total Row are now replaced with formulas. This is especially helpful if some of your calculations are not working out correctly and you need to see all them to compare and/or modify them.
What is a Status Bar?
The status bar is another cool feature in Microsoft Excel. It resides on the bottom right-hand portion of the screen and is used as a quick reference for any calculations in your spreadsheet. This lends a hand to a system of checks and balances to ensure more efficiency on your calculations and formulas.
It takes whatever numbers you have highlighted and performs a specific calculation on them. The calculation is based on which one of the following items is currently selected. You can change the selection type by right clicking on the status bar and choosing from the menu.
If you had wanted the status bar to display the Minimum amount, you can check off ‘minimum’ from the status bar menu. You can count how many numbers are highlighted if you checked ‘Numerical Count.’ The same holds true for Average and Count. (One thing to Note-‘Numerical Count’ displays how many NUMBERS are highlighted. ‘Count’ displays how many cells alphanumeric or numeric are highlighted).
Enter Text and Numeric Data in a Spreadsheet
Entering data into a spreadsheet is somewhat simple, but if you are creating a complex or possibly complex spreadsheet, you may want to take some time to plan and organize. Will you ever need to insert new columns? Should they be in any type of specific order? Should you label the columns, the rows, or both? Once you have your design on paper (or in your head), you are ready to begin.
Basically, if a spreadsheet is used in the context in which it is intended, then the columns will have headers labeling what its purpose is for, such as month, or product number, name, etc.
The Rows may have some other type of label as in the example above. The columns were for each month and the rows defined the expenses spent in each month.
Numbers are entered into Excel as items that will are able to be calculated in any type of formula. There are reasons, however, when you need to enter a number into a cell, but they DO NOT need to be calculated, such as a phone number or zip code.
In those particular cases, you can precede the number with an apostrophe (‘). This will tell Excel that this number is not to be included in any calculations. Another situation where you would not want the number to be calculated would be if you were numbering the cells. This situation may not be necessary if you are at the beginning of a worksheet since the cells are already numbered. It may occur, however, if you were in row 377 and needed to start numbering a list starting from the number 1. In this instance put an apostrophe before the 1 and Excel will realize this is a non-formula number.
Text can be entered without any apostrophe before it as it cannot possibly be interpreted as anything that can be calculated.
Format and Enhance the Data
Suppose you wanted to quickly add a currency symbol to each number in our Budget example. While it may not seem like such a big deal in this example, imagine there were 2000 rows. You would want an efficient, expedient way of adding that currency. Luckily, there is such a way.
First, highlight all of the cells by clicking on the first cell you wish to capture, then left click and drag your mouse to the last cell you wish to capture.
Right click on the highlighted area and click ‘Format Cells’ from the menu that appears. You could also select Format>Cells from the main menu. You will see a dialog box appear.
- Click on the category under the ‘Number Tab’ that you wish to format and to the right, depending on the category, you may have the option to customize this format even further.
- If you wish to change the alignment, font, border, patterns protection just click on each of the tabs, as shown.
The alignment tab also has text control at the bottom, where you can ‘wrap text.’ This function is can be used for many different reasons, but one example is if you have to enter a long last name in one or more of the cells. Wrap text will allow the text to appear in a bigger cell going downward instead of all the way across the screen. For example:
Theresa Annmarie Guberman-Jones
will wrap to the next line so it looks like Theresa Annmarie
‘Shrink to fit’ will make the text a smaller font.
The next tab – Font is for formatting the font of your text. Just click on the desired font style, size, type, if you want to change the color, etc. Then press ok.
Borders are the boxes you see surrounding your cells. This feature is good to use, so that when your spreadsheet is printed, it is clearer as to where one cell begins and one cell ends.It will look more defined and easier to view.
You can choose the line style, color and border type in the border tab.
Fills are helpful, if you want to make certain cells stand out because they may signify a distinct topic. You can make the cell a different color or pattern in this tab. Click on the color you wish, or for a ‘pattern,’ click on the drop arrow next to Pattern, and select a pattern from the box that appears. Select ‘OK.’ The selected cells will now be in the color of your choice.
This is a feature that is great to use, especially if you will be distributing the spreadsheet to someone else for who will be filling in certain fields. For example, let’s suppose another person was designated to fill in the inventory for a bunch of different products. Since there are some cells that contain important formulas, you certainly make certain that none of the product names located in cell A1 to F1 got erased. To use this feature you would do the following:
- Type in your product names first.
- Highlight the cells you wish to protect. In this case A1 to F1.
- Right click and go to the protection tab and make sure ‘Locked’ is checked.
- Go to the cells that you WANT people to type in.
- Right click and go to the protection tab and make sure ‘Locked’ is NOT checked.
- Select Review>Changes>Protect Sheet.
- To unprotect sheet, select Review>Changes>Unprotect Sheet.
Insert and Delete Columns and Rows
There may be times when you need to insert a new row between two rows that already have data in them. For instance, suppose you are creating a list of names in alphabetical order. Somewhere between Smith and Stern, you found a last name of ‘Stein.’ Excel makes it easy for you to just insert a row between the two and you can enter in your data for Mr. or Mrs. Stein.
To do so, highlight the row where you want the data to go. For instance, suppose the cell containing ‘Smith’ was A1 and the cell containing ‘Stern’ was A2. You would highlight A2 because that is where you want to insert the new data containing ‘Stein’s’ information.
Once you highlight the row, select Home>Insert>Insert Sheet Rows.
To delete a row, highlight the row and select Home>Delete>Delete Sheet Rows. If you hit delete on your keyboard it will delete the data in that particular cell, but not the row.
On the flip side, there may be instances when you need to insert or delete a column. The premise is exactly the same as inserting or deleting rows, except that you will be highlighting the column where you want to insert a new column (to the left), then select Home>Insert> Insert Sheet columns. A new column will appear on the left. (Or Home>Delete>Delete Sheet columns to delete a column).
Change the Size of Columns and Rows
In addition to inserting and deleting a column or row, you may want to change the size of the column or row. Some reasons why you would do that is perhaps the particular column or row you wish to modify has an uncommonly larger dataset than the rest, or you may want to change the size of all of the columns or rows.
To select ALL columns and ALL rows, position your cursor on the rectangle in the upper most corner of your screen. It is the one right between A and 1.
This will highlight the entire spreadsheet.
To highlight just one row or column, position your cursor either on the letter of the column or the number of the row. If you want to select a few more of either just left click and drag the mouse to highlight the multiple columns or rows.
To select rows or columns that are NOT next to each other, hold the Ctrl key down and position your cursor on each of the letters or numbers of the columns or rows you wish to select.
To now change the size of your selected row or column, you can simply position your cursor on the right side of one of the columns you are resizing and drag it (same for the rows). Another way to resize it is by selecting Home>Format>Rows >Height or Format>Columns>Width from the main menu and manually enter in the height and width.
Formulas are one of the great things for which Microsoft Excel is known. This is how you can automate all of your calculations just by entering data in the selected cells. Typically, you need to enter cells that have or will have some numbers in them. Let’s call those cells ‘data cells.’ Underneath the data cells, you will have a cell or cells containing a total or calculation of some kind. Let’s call those cells the ‘formula’ cells.
You can search for more by selecting Insert>Function from the Formula tab. The Insert Function screen will ask you to type in what you are looking to do and then attempt to find it for you. There are literally dozens to choose from, far too many for me to list here.
They are also displayed in categories in the function library group of the Formula tab.
The system that I use for writing out difficult formulas is to first write it down exactly how it should be in regular terms. I then try to interpret it into a formula. Sometimes it is easy, but sometimes it takes a lot of thought and logic.
Some of the easier formulas are:
=Sum(b2:b6) -- This will add the fields from b2 through to b6.
=(b4-b5) --This will subtract whatever resides in b5 from whatever resides in b4. In the preceding example, it would be 1500-85.
=b4/b2 – This will divide b4 (1500), by b2(30) which equals 50
If you are ever unsure, you can select help from the main menu and then type in “Examples of Commonly used formulas.” This will then provide instructions on entering some formulas.
In addition, (no pun intended), when you are working with formulas, the general math rules “Order of Operations” do apply. To remember the rule, use the saying: “Please Excuse My Dear Aunt Sally (Parenthesis, Exponents, Multiply, Divide, Add, Subtract).” You can use any way you want to remember it- that is the most common one
Calculations are always done from left to right.
The Calculations in brackets (or parenthesis) are done first.
Exponents are done next.
Multiply and divide in the order they occur.
Add and subtract in the order they occur.
Practice makes perfect with formulas. The built-in functions are a great help, but you will need to think some of these through.
Once you experiment with them, you will soon be able to calculate whatever you want. Congratulations- you are on your way to becoming knowledgeable in Excel.
Thanks for reading!