Tapping Into Formula and Function Fundamentals
Excel is to computer programs what a Ferrari is to cars: sleek on the outside and a lot of power under the hood. Excel is also like a truck. It can handle all your data — lots of it. In fact, in Excel 2019, a single worksheet has 17,179,869,184 places to hold data. Yes, that’s what I said — more than 17 billion data placeholders. And that’s on just one worksheet!
Opening files created in earlier versions of Excel may show just the number of worksheet rows and columns available in the version the workbook was created with
Excel is used in all types of businesses. And you know how that’s possible? By being able to store and work with any kind of data. It doesn’t matter whether you’re in finance or sales, whether you run an online e-commerce store or organize wilderness trips, or whether you’re charting party RSVPs or tracking the scores of your favorite sports teams — Excel can handle all of it. Its number-crunching ability is just awesome! And so easy to use!
Just putting a bunch of information on worksheets doesn’t crunch the data or give you sums, results, or analyses. If you want to just store your data somewhere, you can use Excel or get a database program instead. In this book, I show you how to build formulas and how to use the dozens of built-in functions that Excel pro vides. That’s where the real power of Excel is — making sense of your data.
Don’t fret that this is a challenge and that you may make mistakes. I did when I was ramping up. Besides, Excel is very forgiving. It won’t crash on you. Excel usually tells you when you made a mistake, and sometimes it even helps you correct it. How many programs do that? But first, the basics. This first chapter gives you the springboard you need to use the rest of the book. I wish books like this were around when I was introduced to computers. I had to stumble through a lot of this.
Working with Excel Fundamentals
Before you can write any formulas or crunch any numbers, you have to know where the data goes and how to find it again. I wouldn’t want your data to get lost! Know ing how worksheets store your data and present it is critical to your analysis efforts.
Understanding workbooks and worksheets
In Excel, a workbook is the same as a file. Excel opens and closes workbooks, just as a word processor program opens and closes documents. When you start up Excel, you are presented with a selection of templates to use, the first one being the standard blank workbook. Also there is a selection of recent files to select from. After you open a new or already created workbook, click the File tab to view basic functions such as opening, saving, printing, and closing your Excel files (not to mention a number of other nifty functions to boot!). Figure 1-1 shows the con tents presented on the Info tab
Excel 2019 (also Excel 2016, Excel 2013, Excel 2010, and Excel 2007) files have the default .xlsx extension. Older-version Excel workbook files have the .xls extension.
Start Excel and double-click the Blank Workbook icon to create a new blank work book. When you have more than one workbook open, you pick the one you want to work on by clicking it on the Windows Taskbar
A worksheet is where your data actually goes. A workbook contains at least one worksheet. If you didn’t have at least one, where would you put the data? Figure 1-2 shows an open workbook that has two sheets, aptly named Sheet1 and Sheet2. To the right of these worksheet tabs is the New Sheet button (looks like a plus sign), used to add worksheets to the workbook.
Don’t delete a worksheet unless you really mean to. You cannot get it back after it is gone. It does not go into the Windows Recycle Bin.
You can insert many new worksheets. The limit of how many is based on your computer’s memory, but you should have no problem inserting 200 or more. Of course, I hope you have a good reason for having so many, which brings me to the next point.
Worksheets organize your data. Use them wisely, and you will find it easy to man age your data. For example, say that you are the boss (I thought you’d like that!), and over the course of a year you track information about 30 employees. You may have 30 worksheets — one for each employee. Or you may have 12 worksheets — one for each month. Or you may just keep it all on one worksheet. How you use Excel is up to you, but Excel is ready to handle whatever you throw at it.
You can set how many worksheets a new workbook has as the default. To do this, click the File tab, click Options, and then click the General tab. Under the When Creating New Workbooks section, use the Include This Many Sheets spinner con trol to select a number.
Introducing the Formulas Tab
Without further ado, I present the Formulas tab of the Ribbon. The Ribbon sits at the top of Excel. Items on the Ribbon appear as menu headers along the top of the Excel screen, but they actually work more like tabs. Click them, and no menus appear. Instead, the Ribbon presents the items that are related to the clicked Rib bon tab
Figure 1-5 shows the top part of the screen, in which the Ribbon displays the items that appear when you click the Formulas tab. In the figure, the Formulas tab is set to show formula-based methods. At the left end of the tab, functions are categorized. One of the categories is opened to show how you can access a partic ular function.
These groups are along the bottom of the Formulas tab
Function Library: This includes the Function Wizard, the AutoSum feature, and the categorized functions
Defined Names: These features manage named areas, which are sections on worksheets that you give a meaningful name to for easy reference
Formula Auditing: These features for checking and correcting formulas have been through many Excel incarnations, but never before have the features been so prominent. Also here is the Watch Window, which lets you keep an eye on the values in designated cells, but within one window. In Figure 1-6 you can see that a few cells have been assigned to the Watch Window. If any values change, you can see this in the Watch Window. Note how the watched cells are on sheets that are not the current active sheet. Neat! By the way, you can move the Watch Window around the screen by clicking the title area of the window and dragging it with the mouse.
Calculation: This is where you manage calculation settings, such as whether calculation is automatic or manual.
Another great feature that goes hand in hand with the Ribbon is the Quick Access Toolbar. (So there is a toolbar after all!) In Figure 1-5, the Quick Access Toolbar sits just above the left side of the Ribbon. On it are icons that perform actions with a single click. The icons are ones you select by using the Quick Access Toolbar tab in the Excel Options dialog box. You can put the toolbar above or below the Ribbon by clicking the Customize Quick Access Toolbar drop-down arrow on the Quick Access Toolbar and choosing an option. In this area too are the other options for the Quick Access Toolbar.
Working with rows, column, cells, ranges, and tables
A worksheet contains cells. Lots of them. Billions of them. This might seem unmanageable, but actually it’s pretty straightforward. Figure 1-7 shows a work sheet filled with data. Use this to look at a worksheet’s components. Each cell can contain data or a formula. In Figure 1-7, the cells contain data. Some, or even all, cells could contain formulas, but that’s not the case here
Columns have letter headers — A, B, C, and so on. You can see these listed hori zontally just above the area where the cells are. After you get past the 26th col umn, a double lettering system is used — AA, AB, and so on. After all the two-letter combinations are used up, a triple-letter scheme is used. Rows are listed vertically down the left side of the screen and use a numbering system.
You find cells at the intersection of rows and columns. Cell A1 is the cell at the intersection of column A and row 1. A1 is the cell’s address. There is always an active cell — that is, a cell in which any entry would go into should you start typ ing. The active cell has a border around it. Also, the contents of the active cell appear in the Formula Box.
When I speak of, or reference, a cell, I am referring to its address. The address is the intersection of a column and row. To talk about cell D20 means to talk about the cell that you find at the intersection of column D and row 20.
In Figure 1-7, the active cell is C7. You have a couple of ways to see this. For start ers, cell C7 has a border around it. Also notice that the column head C is shaded, as well as row number 7. Just above the column headers are the Name Box and the Formula Box. The Name Box is all the way to the left and shows the active cell’s address of C7. To the right of the Name Box, the Formula Box shows the contents of cell C7.
If the Formula Bar is not visible, choose File ➪ Options, and click the Advanced tab. Then, in the Display section in the Excel Options dialog box, select the Show Formula Bar check box to make it visible
GETTING TO KNOW THE FORMULA BAR
Taken together, the Formula Box and the Name Box make up the Formula Bar. You use the Formula Bar quite a bit as you work with formulas and functions. The Formula Box is used to enter and edit formulas. The Formula Box is the long entry box that starts in the middle of the bar. When you enter a formula into this box, you can click the little check-mark button to finish the entry. The check-mark button is visible only when you are entering a formula. Pressing Enter also completes your entry; clicking the X cancels the entry.
An alternative is to enter a formula directly into a cell. The Formula Box displays the for mula as it is being entered into the cell. When you want to see just the contents of a cell that has a formula, make that cell active and look at its contents in the Formula Box. Cells that have formulas do not normally display the formula, but instead display the result of the formula. When you want to see the actual formula, the Formula Box is the place to do it. The Name Box, on the left side of the Formula Bar, is used to select named areas in the workbook
A range is usually a group of adjacent cells, although noncontiguous cells can be included in the same range (but that’s mostly for rocket scientists and those obsessed with treating data like jigsaw puzzle pieces). For your purposes, assume a range is a group of continuous cells. Make a range right now! Here’s how:
1. Position the mouse pointer over the first cell where you want to define a range.
2. Press and hold the left mouse button.
3. Move the pointer to the last cell of your desired area.
4. Release the mouse button
No comments:
Post a Comment