#Excel Common Tools Full Of Craftsman

For example, C6 is the address of a cell in column C (the third column) and row 6 (the sixth row). Excel uniquely identifies each cell by column letter and row number. This tool allows you to freeze rows.The smallest unit in your worksheet is the cell. The following tools are fairly simple but can be very helpful when manipulating data. 8 Common Excel Formula Errors and Fixes: Here comes the list of the 8 most common excel errors occurring in the spreadsheet.

And if you clump the addresses and ZIP codes together, you have no way to count the number of people in a certain town or neighborhood. For example, you can start by typing information into cell A40 without worrying about filling any data in the cells that appear in the earlier rows.FigureВ 1-4.В Top: If you enter both first and last names in a single column, you can sort the column only by first name. However, you donвЂ™t have to follow any set order. Every spreadsheet file can hold a virtually unlimited number of worksheets, as youвЂ™ll learn in ChapterВ 4.When you enter information, enter it one cell at a time. In the unlikely case that you want to go beyond those limitsвЂ”say, if youвЂ™re tracking blades of grass on the White House lawnвЂ”youвЂ™ll need to create a new worksheet. Incidentally, an Excel cell can hold approximately 32,000 characters.A worksheet can span an eye-popping 16,000 columns and 1 million rows.

ThatвЂ™s because the formula bar gives you more work room than a typical cell. This is particularly useful when a cell contains a formula or a large amount of information. Instead of editing this value in the cell, you can click anywhere in the formula bar and make your changes there.You can use the formula bar to enter and edit data instead of editing directly in your worksheet.

However, thereвЂ™s another optionвЂ”you can resize the formula bar so that it fits more information, as shown in FigureВ 1-9.POWER USERSвЂ™ CLINIC: Using R1C1 Reference StyleMost people like to identify columns with letters and rows with numbers. If you have a really long entry in a cell (like a paragraphвЂ™s worth of text), you need to scroll from one side to the other. Click the checkmark to confirm your entry or вЂњXвЂќ to roll it back.Ordinarily, the formula bar is a single line. Or you can use the mouse: When you start typing in the formula bar, a checkmark and an вЂњXвЂќ icon appear just to the left of the box where youвЂ™re typing.

The R1C1 format reverses the order of conventional cell addressing.R1C1 addressing isnвЂ™t all that common, but it can be useful if you need to deal with worksheets that have more than 26 columns. The letters R and C tell you which part of the address represents the row number and which part is the column number. That means the cell address A10 becomes R10C1 (read this as Row 10, Column 1). In R1C1 style, Excel identifies both rows and columns with numbers. When you first install Excel, it uses this style of cell addressing.However, Excel lets you use another cell addressing system called R1C1.

Then, look under the вЂњWorking with formulasвЂќ heading, and turn on the вЂњR1C1 reference styleвЂќ checkbox.R1C1 is a file-specific setting, which means that if someone sends you a spreadsheet saved using R1C1, youвЂ™ll see the R1C1 cell addresses when you open the file, regardless of what type of cell addressing you use in your own spreadsheets. In the list on the left, choose Formulas to hone in on the section you need. This shows the Excel Options window, where you can change a wide array of settings. On the other hand, the R1C1 address for the same cellвЂ”R1C50вЂ”gives you a clearer idea of where to find the cell.To use R1C1 for a spreadsheet, select Fileв†’Options. For example, if you want to find cell AX1, it isnвЂ™t immediately obvious that cell AX1 is in column 50. But this approach can get awkward.

Excel groups the buttons within a tab into smaller sections for clearer organization.The ribbon makes it easy to find features because Excel groups related features under the same tab. Now, you have a slew of options for tasks like adjusting paper size and making a decent printout. But hereвЂ™s what happens when you click the Page Layout tab.

Excel Common Tools Full Of Craftsman

Instead, itвЂ™s your gateway to ExcelвЂ™s backstage view, as described on Going Backstage.Home includes some of the most commonly used buttons, like those for cutting and pasting text, formatting data, and hunting down important information with search tools.Insert lets you add special ingredients to your spreadsheets, like tables, graphics, charts, and hyperlinks.Page Layout is all about getting your worksheet ready for printing. And youвЂ™re sure to notice the way the ribbon rearranges its buttons when you change the size of the Excel window (see FigureВ 1-11).File isnвЂ™t really a toolbar tab, even though it appears first in the list. Another nice detail is the way you can jump from one tab to another at high velocity by positioning your mouse pointer over the ribbon and rolling the scroll wheel (if your mouse has a scroll wheel). Instead, you see a friendly pop-up box with a mini-description of the feature and (often) a shortcut that lets you trigger the command from the keyboard. For example, when you hover over a button, you donвЂ™t see a paltry two- or three-word description in a yellow rectangle. In other words, the ribbon isnвЂ™t just a convenient tool, itвЂ™s also a great way to explore Excel.The ribbon is full of craftsman-like detail.

It also lets you pull off a few fancy tricks if you want to view several separate Excel spreadsheet files at the same time see Viewing Multiple Workbooks at Once.FigureВ 1-12.В Do you want to use every square inch of screen space for your cells? You can collapse the ribbon (as shown here) by double-clicking any tab. It also has buttons that let you add comments to a worksheet and manage revisions.View lets you switch on and off a variety of viewing options. It also includes tools for dealing with large amounts of information, like sorting, filtering, and subgrouping data.Review includes the familiar Office proofing tools (like the spell-checker). This tab helps you build super-smart formulas and resolve mind-bending errors.Data lets you get information from an outside data source (like a heavy-duty database) so you can analyze it in Excel.

The same trick works if you trigger a command in the ribbon using the keyboard, as described in the next section.If you use the ribbon only occasionally, or if you prefer to use keyboard shortcuts, it makes sense to collapse the ribbon. As soon as you click the button you want in the Home tab (or click a cell in your worksheet), the ribbon collapses again. For example, if you click Home, the Home tab pops open over your worksheet. All you need to do is click a tab. And if you want to perform the same trick without lifting your fingers from the keyboard, use the shortcut Ctrl+F1.Even if you collapse the ribbon, you can still use all its features.