The Columns of a Spreadsheet

Columns Fundamentals

 

Introduction to Columns

Imagine you have a list of students whose grades you want to organize to be able to easily view and analyze them. When creating the list, you can start with their names. Here is an example: First Name Last Name Roland Becker Chrissy Groans Robert Farell Alexa Schwitts Because these are students, you may also add their courses to the list where you would enter their grades. Here is an example:

First Name Last Name English History Geography Math Chemistry Physics 1. Roland Becker 10.50 12.00 12.00 16.50 16.75 14.00 2. Chrissy Groans 12.00 14.50 14.00 12.75 12.00 10.50 3. Robert Farell 16.00 15.50 16.50 14.50 14.00 15.50 4. Alexa Schwitts 15.50 14.00 16.00 16.50 13.50 14.25 This type of list is referred to as a sheet or a spreadsheet. To organize its information, this type of list uses vertical arrangements as categories of information. In this case, the categories are First Name, Last Name, English, History, Geography, Math, Chemistry, and Physics. On a spreadsheet, each category arranged vertically is called a column. As you can see from the above list and as we will learn in the next section, each column has a name and borders so it can be identified from the other columns. Practical Learning: Introducing Columns Open the RTHS1 file Identifying a Column  To make it easy to create a type of list like the above, Microsoft Excel provides a ready-made arrangement of columns. To easily identify each category of the list, a column is created as a vertical object. On top of each column, there is a (blue) bar called the column header. As seen in the previous lesson, the columns are organized as a row of (blue) bars: To distinguish each column, it has a name. The name of a column displays in the column header. The name uses one, two, or three letters. The most left column is called, and is labeled, A. The second has a label of B, and so on. A Microsoft Excel document contains 16,384 columns going from Column A to Column XFD: When you start a document in Microsoft Excel, the application makes all these columns available. You can use all of them or just a few, but they are always available. Among the various ways you can use a column, we will see in various sections that you can click it or use the keyboard to get to a column. You can also right-click a column. When you do, an expanded menu would appear:   Practical Learning: Checking Columns To review the columns, click and hold the mouse on the right arrow button of the horizontal scroll bar for a few minutes Release the mouse and press Ctrl + Home Columns Selections At times you will almost want to alter the display of a column or various columns. You have to select that column or the group of columns first. Another reason you may need to select a column or a group of columns is because you would need to take some action on it. Some of these issues will be addressed soon, some others will be reviewed as we move on. You can select a column or a group of columns using the mouse, the keyboard, or a combination of both: To select a column using the mouse, position the mouse on the column header: and click (with the left mouse button) a column header; it would get selected and all small boxes under it To select a column using the keyboard, click anything under it, then press and hold Ctrl. While Ctrl is down, press the Space bar and release Ctrl. You can also select more than one column. Selecting columns in a range consists of selecting adjacent columns. To perform this type of selection, you can use either the mouse or a combination of the mouse and the keyboard: To select columns in a range using the mouse, click one column header and hold the mouse down. Then drag in the direction of the range   To select a range of columns using the mouse and the keyword, click one column at one end of the desired range. Press and hold Shift. Then click the column at the other end, and release the mouse. Random selection consists of selecting columns that are not adjacent. For example, this allows you to select columns B, D, and H. To do this, click one column header, press and hold Ctrl. Then click each desired column header. When you have selected the desired columns, release the mouse. Practical Learning: Selecting Columns To select a column, click the column header D To select more than one column, click column header C and hold the mouse down. While the mouse is still down, move it right until column F is selected, then release the mouse To select columns in another range, click column header B Press and hold Shift, then click column header E Release Shift To select columns at random, click column header H Press and hold Ctrl Click column headers B, E, and C Release Ctrl Click any box under column header G To select a column with the keyboard, for example column G, press Ctrl + Space Press Ctrl + Home Adding or Deleting Columns   Inserting a New Column As mentioned already, Microsoft Excel has columns named from A to XFD with a maximum of 16384. Microsoft Excel allows you to add a column. Actually, you can insert a column on the left side of an existing column. When you do, Microsoft Excel internally removes the very last column to keep the count to 16384. To add a new column: Right-click the column header of the column that will be on the right side of the new column you want to create, and click Insert Click the column header or any box under  it. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Sheet Columns To add more than one column, first select the columns, whether in a range or randomly. Then: Right-click one of the columns (whether one of the column headers or a box of one of the selected columns) that will be on the right side of the new columns you want to create, and click Insert (After selecting the columns,) On the Ribbon, click Home. In the Cells section, click the arrow button Insert and click Insert Sheet Columns If you select columns randomly (non-adjacent), a new column would be created on the left side of each of the selected columns. To undo any of these actions: On the Quick Access toolbar, click the Undo button Press Ctrl + Z Practical Learning: Adding Columns Right-click Column Header E and click Insert Click Column Header C On the Ribbon, click Home if necessary. In the Cells section, click the arrow button under Insert and click Insert Sheet Columns Press Ctrl + Home Save the document Removing a Column If you find out that you have a column you do not want, you can remove it. To remove a column: Right-click the column header and click Delete Click the column header or any box under  it. On the Ribbon, click Home. In the Cells section, click Delete and click Delete Sheet Columns To delete more than one column, first select the columns, whether in a range or randomly. Then: Right-click one of the columns (whether one of the column headers or a box of one of the selected columns) and click Delete (After selecting the columns,) On the Ribbon, click Home. In the Cells section, click Delete and click Delete Sheet Columns If no box under the column header has anything, you would not receive a warning and the column would simply be removed. If at least one of the boxes under the column header has a value, you may receive a warning to indicate whether you want to continue with the operation or not. To undo any of these actions: On the Quick Access toolbar, click the Undo button Press Ctrl + Z Practical Learning: Deleting Columns Right-click Column Header C and click Delete Press Ctrl + Home The Width of Columns   Introduction To display the information under it, a column uses a measure from its left border to its right border. This measure is referred to as its width. By default, when Microsoft Excel comes up, all columns use the same width. You are allowed to change the width of one column or a group of columns. There are various techniques you can use to change the width of a column. You can manually resize a column or a group of columns, or you can use a dialog box to exercise more control. Manually Resizing the Columns To manually resize a column, position the mouse on the short line that separates a column header from its right neighbor. Here is an example: Click, then drag left or right until the small box displays the width you desire, then release the mouse. You can also resize a group of columns. First, select the columns you want to work on. Then position the mouse on the column header border of one of the selected columns. Click and drag left or right in the direction of your choice until you get the desired with. Then release the mouse. To undo this action: On the Quick Access toolbar, click the Undo button Press Ctrl + Z Practical Learning: Controlling the Widths of Columns Position the mouse between columns H and I until the mouse turns into a short line with double horizontal arrows Then double-click. That resizes column H Position the mouse on the separator of column headers B and C, then click and drag in the right direction until the small box displays Width: 10.00 Release the mouse In the same way, position the mouse on the separator of column headers C and D, then click and drag in the right direction until the small box displays Width: 10.00 Press Ctrl + Home To save the document, press Ctrl + S Automatically Resizing the Columns If one of the boxes under a column header displays the width you want, you can resize the column to the content of that box. To do this, click the box that has the desired width. Then: Double-click the short line that separates the column header from the column on its right (if you are on the most right column, that will be the line that serves as the column's right border) On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Column Width In the same way, to set the widths of columns based on some boxes under their columns headers, select those boxes (in Lesson 4, we will learn how to select the boxes). Then: Double-click the short line on one side of the column headers On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Column Width To undo any of these actions: On the Quick Access toolbar, click the Undo button Press Ctrl + Z Practical Learning: Automatically Resizing the Columns Randomly select Columns D, F, G, and I On the Ribbon, click Home if necessary. In the Cells section, click the Format button, and click AutoFit Column Width Press Ctrl + Home Save the document Setting the Width Value of Columns You can use a dialog box to set exactly the desired width of a column or a group of columns. To specify the width of a column: Right-click the column header and click Column Width... Click a column header or any box under it. Then, on the Ribbon, click Home. In the Cells section, click Format and click Column Width... To specify the same width for many columns: Select a range of columns. Right-click one of the columns (right-click either one of the column headers or inside the selection) and click Column Width... Randomly select a group of (non-adjacent) columns. Right-click one of the column headers and click Column Width... Select the columns, whether in a range or randomly (non-adjacent). On the Ribbon, click Home. In the Cells section, click Format and click Column Width... Any of these actions would open the Column Width dialog box. From there, accept or enter the desired value and click OK To undo any of these actions: On the Quick Access toolbar, click the Undo button Press Ctrl + Z Practical Learning: Setting the Width of Columns Right-click Column Header J and click Column Width... Type 6.50 and press Enter Press Ctrl + Home Columns and Their Content   Introduction You may have noticed that, in the documents we have used so far, there are some values under some column headers. Because a column is primarily a group of values, you can copy its values to the clipboard and put them in another column. Moving Columns In our introduction, we saw that columns assume some default positions when Microsoft Excel starts. On a normal computer spreadsheet, you can move a column from its current position to another. To move a column, first click its column header to select it. Position the mouse on one of the vertical lines of the selected column: Click and hold your mouse down. Drag left or right. Two vertical lines would guide you. When you get the column to the desired location, release the mouse. When you move a column, its boxes move but it assumes the lettered name of the new location so the names would still follow the alphabetic sequence. To move a group of columns, select them. Position the mouse on one of the vertical lines of the selection: Click and hold your mouse down. Drag left or right. Vertical lines would guide you. When you get the columns to the desired location, release the mouse. When you move the columns, their boxes move but they assume the lettered name of the new location with the appropriate alphabetic sequence. Copying and Pasting Columns As mentioned already, when moving one or more columns, their location changes. In some cases, you may not want to move the column(s) but only its(their) content. To support this, the operating system provides the clipboard and Microsoft Excel has a high level of support for it. In other words, you can copy the contents of column(s) to the clipboard and paste it(them) to other column(s). To copy the contents of a column to the clipboard: Right-click the column header and click Copy Click the column header. On the Ribbon, click Home. In the Clipboard section, click Copy After copying a column to the clipboard, all of its values are made available. To put those values on another column: Right-click the target column header and click Paste Click the column header. On the Ribbon, click Home. In the Clipboard section, click Paste Cutting the Contents of Columns As seen already, if you move one or more columns, they go with their contents. If you copy the contents of columns, you would have duplicate (contents) of columns. As an alternative, you can move only the values of columns, not the columns themselves. The Microsoft Windows operating system supports this operation through cutting to the clipboard. To temporarily move the contents of a column to the clipboard to wait to be pasted: Right-click the column header and click Cut Click the column header. On the Ribbon, click Home. In the Clipboard section, click Cut After cutting a column to the clipboard, if you do not want to paste it anywhere, you can press Esc. If you want to paste it to another column: Right-click the target column header and click Paste Click the column header. On the Ribbon, click Home. In the Clipboard section, click Paste To temporarily move the contents of many columns to the clipboard to wait to be pasted, select the columns. Then: Right-click either one of the column headers or inside the selection, and click Cut On the Ribbon, click Home. In the Clipboard section, click Cut If you want to paste the values to another group of columns: Right-click a target column header and click Paste Click a column header. On the Ribbon, click Home. In the Clipboard section, click Paste When you paste, the values of the boxes under the original columns would be emptied. Hiding, Freezing, and Splitting Columns   Hiding and Revealing Columns When working on a list, you don’t always need all columns displaying all the time. You can hide a column whose presence is not required at a particular time. In Microsoft Excel, you can hide one or more columns. To hide one column: Right-click the column and click Hide Click the column header. On the Ribbon, click Home. In the Cells section, click Format, position the mouse on Hide & Unhide, and click Hide Columns When a column has been hidden, its letter disappears from the sequence and the line between the previous neighbors is thicker than the other dividing lines: To hide many columns, select the columns. Then: Right-click one of the column headers or inside the selection and click Hide On the Ribbon, click Home. In the Cells section, click Format, position the mouse on Hide & Unhide, and click Hide Columns To reveal the hidden columns: Right-click any column header and click Unhide On the Ribbon, click Home. In the Cells section, click Format, position the mouse on Hide & Unhide, and click Unhide Columns Practical Learning: Hiding a Column Right-click Column E and click Hide Save the document Freezing One or More Columns If you have a list wider than the Microsoft Excel area can show, you can scroll to the right to see hidden columns. While you are scrolling to the right, some columns would be disappearing from the left section of the Microsoft Excel interface. If you want, you can freeze a column so that, when you scroll to the right, a column or some columns would be fixed and would not move. Also, the column(s) from the left of the frozen column would not move either. To freeze a column, click the column header of the column that will lead the moving columns. On the Ribbon, click View. In the Window section, click Freeze Panes, and click Freeze Panes. Practical Learning: Freezing a Column Open the RTHS2 file Click Column Header E On the Ribbon, click View In the Window section, click Freeze Panes Press Ctrl + Home Scroll to the right. Notice that Column D and its left columns are not moving In the Window section of the Ribbon, click Freeze Panes and click Unfreeze Panes Splitting the Columns An alternative to freezing is to split the group of columns into two sections. Just as done for the freezing, you can choose a column to use as reference and scroll the columns from its side. To split the group of columns in two, click a column header. On the ribbon, click View. In the Window section, click Split. This would display a bar: The similarities between the freezing and splitting are as follows: The columns are divided in two groups The user can scroll the columns from the right side of the divider The differences between the freezing and splitting are as follows: If you freeze a column, you can scroll the columns on the right side of the frozen line but you cannot scroll the columns from the left side of the frozen line. If you split the columns, you can scroll the columns from either the left or the right side of the splitting bar If you freeze a column, you cannot move the freezing line to another column (you would have to unfreeze the column, then re-freeze). If you split the columns, you can move the splitting bar to the left or the right If you freeze a column, to remove the frozen line, you use the Ribbon. If you split the columns, to remove the splitting line, you can double-click it or, in the Window section of the View tab of the Ribbon, you can click the Split button Practical Learning: Splitting the Columns Click Column Header I In the Window section of the Ribbon, click the Split button Press Ctrl + Home Position the mouse on the split bar   Scroll in the left frame Scroll in the right frame In the Window section of the Ribbon, click the Split button Close Microsoft Excel. If asked whether you want to save, click No