Chapter 4

Getting Started with Excel Chapter-4: Working with Excel Ranges and Tables Structure 1.0 Learning Objectives 1.1 Ranges in MS Excel 1.2 Tables in MS Excel 1.3 Summary 1.4 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain Ranges in MS Excel  Explain Tables in MS Excel 1.1 Ranges in MS Excel The term Range is used in MS Excel for the selection of one or more than one cell either horizontally or vertically or even in a block. There are various manners in which a range can be defined in MS Excel:  Selecting a complete single row. To select a complete row as a range, take your mouse to the row label and click. This will select the whole row. Page 25 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  Selecting complete multiple rows. To select complete multiple rows as a range, take your mouse to the first row’s label that you want to select. Click on the label and drag your mouse vertically downwards on the row’s labels, selecting all the desired rows.  Selecting a complete column. To select a complete column as a range, take your mouse to the column label and click. This will select the whole column.  Selecting complete multiple columns. To select complete multiple columns as a range, take your mouse to the first column’s label that you want to select. Click on the label and drag your mouse horizontally to right side on the column labels, selecting all the desired columns.  Selecting a cell You can select a specific cell by simply clicking on it. Page 26 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  Selecting a block of cells in 3 x 3 cells. Let’s assume that you want to select all the cell starting from “B2” and ends at “D4”. For this, you will click and drag your mouse from Cell B2 to Cell D4 diagonally, which will select all 9 cells falling within the range.  Selecting random cells. You can select random cells by keep pressing the “Ctrl” key and clicking on your desired cells.  Filling a range.  If you have a value that replicates either vertically (within the same column) or horizontally (within the same row), instead of typing it again and again, you can use the range filling feature of MS Excel. You can do this by entering the value in one cell and then click and drag on the anchor point either vertically to the column or horizontally on the row. This action will apply the same value on selected range. Page 27 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  You can also use the same method to autofill the values. For example, you want to put serial number from 1 to 100 in Column A. Now, instead of typing numbers one by one, you will type 1 in Cell A1 and 2 in Cell A2. Then you will select both cells, and click and drag the anchor point vertically on Column A, till you reach Cell A100. You will notice that all range will automatically be filled with incremented numbers. This action will save you much of time and hassle.  Copying and Pasting a Range. To copy a range from one location and pasting it to another location is quite easy and simple in MS Excel. First, you need to select the range from where you want to copy the text. For example, you have a table having values from Cell A1 to Cell G6 and you have to make a copy of it on Cell J1 to P6. First of all, you will select the table by clicking on Cell A1 and dragging it diagonally towards Cell G6, defining your range. Then you will right-click on the selected text and will select “Copy” from the Context Menu. You will then right click on J1 and again, will select “Paste” from the Context Menu. Your range of select text will be copied to J1 to P6. Page 28 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  Cutting and Pasting a Range. To cut a range from one location and pasting it to another location is quite easy and simple in MS Excel. First, you need to select the range from where you want to cut the text. For example, you have a table having values from Cell A1 to Cell G6 and you have to remove it from its present location and place it on Cell J1 to P6. First of all, you will select the table by clicking on Cell A1 and dragging it diagonally towards Cell G6, defining your range. Then you will right-click on the selected text and will click “Cut” from the Context Menu. You will then right-click on J1 and again, will select “Paste” from the Context Menu. Your range of select text will be a move to J1 to P6.  Moving a Range To move a select range to a new location, one method is explained under the “Cutting and Pasting a Range” section. However, there is another method, in fact a quick method of doing so. You will select the desired range and instead of placing you mouse on the anchor point, you will place it on the border line of your selection and click and drag the whole range to new location. Page 29 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.2 Tables in MS Excel To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list). A table can include the following elements:  Header row: By default, a table has a header row. Every table column has filtering enabled in the header row so that you can filter or sort your table data quickly. You can turn off the header row in a table.  Banded rows: Alternate shading or banding in rows helps to better distinguish the data.  Calculated columns: By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column.  Total row: Once you add a total row to a table, Excel gives you an AutoSum drop-down list to select from functions such as SUM, AVERAGE, and so on. When you select one of these options, the table will automatically convert them to a SUBTOTAL function, which will ignore rows that have been hidden with a filter by default. If you want to include hidden rows in your calculations, you can change the SUBTOTAL function arguments.  Sizing handle: A sizing handle in the lower-right corner of the table allows you to drag the table to the size that you want. Page 30 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  Create a table: You can create as many tables as you want in a spreadsheet. To quickly create a table in Excel, do the following: Step-1: Select the cell or the range in the data. Step-2: elect Home > Format as Table. Step-3: Pick a table style. In the Format as Table dialog box, select the checkbox next to My table as headers if you want the first row of the range to be the header row, and then click OK.  Working efficiently with your table data: Excel has some features that enable you to work efficiently with your table data:  Using structured references: Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula. For more information, see Using structured references with Excel tables.  Ensuring data integrity: You can use the built-in data validation feature in Excel. For example, you may choose to allow only numbers or dates in a column of a table. For more information on how to ensure data integrity, see Apply data validation to cells. 1.3 Summary  The term Range is used in MS Excel for the selection of one or more than one cell either horizontally or vertically or even in a block.  To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table. Page 31 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.4 References https://support.microsoft.com/en-us/office/overview-of-excel-tables- 7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://www.certiology.com/tutorials/excel-tutorial/working-with- ranges-in-ms- excel.html#:~:text=The%20term%20Range%20is%20used,be%20defined %20in%20MS%20Excel.&text=To%20select%20complete%20multiple%2 0rows,that%20you%20want%20to%20select. https://www.excel-easy.com/introduction/range.html https://www.excel-easy.com/data-analysis/tables.html Page 32 of 59 All Rights Reserved. Vol. TLE001/03-2022