Managing data with tables



948962925921Excel Bitesize 2 – Creating a workable dataset00Excel Bitesize 2 – Creating a workable dataset14954253380740 Contents TOC \o "1-3" \h \z \u Managing data with tables PAGEREF _Toc66115608 \h 31.Make your data into a table PAGEREF _Toc66115609 \h 32.Naming the table PAGEREF _Toc66115610 \h 43.Adding a total row PAGEREF _Toc66115611 \h 44.Adding a function to a cell in the total row PAGEREF _Toc66115612 \h 45.Microsoft 365 ONLY Creating a formula to count unique instances of text in a column PAGEREF _Toc66115613 \h 56.Identifying duplicates PAGEREF _Toc66115614 \h 67.Removing duplicates PAGEREF _Toc66115615 \h 78.Adding a unique reference number PAGEREF _Toc66115616 \h 89.Changing field formats PAGEREF _Toc66115617 \h 910.Find & Replace PAGEREF _Toc66115618 \h 1011.Splitting columns PAGEREF _Toc66115619 \h 1012.Merging columns together (CONCAT) PAGEREF _Toc66115620 \h 1213.Data Validation PAGEREF _Toc66115621 \h 13Data Validation for data format PAGEREF _Toc66115622 \h 13Data Validation with list options PAGEREF _Toc66115623 \h 14Managing data with tablesThe advantages of an Excel table include all of the following:Quick StylesAdd color, banded rows, and header styles with just one click to style your data.Table NamesGive a table a name to make it easier to reference in other formulas.?Cleaner Formulas?Excel?Formulas are much easier to read and write when working in tables.Auto ExpandAdd a new row or column to your data, and the Excel table automatically updates to include the new cells.Filters & SubtotalsAutomatically add filter buttons and subtotals that adapt as you filter your data.Dynamic Pivot tables If you use an Excel Table for the source data of your pivot table, the data range becomes "dynamic". A dynamic range will automatically expand and shrink the table as you add or remove data, so won't have to worry that the pivot table is missing the latest data. When you use a Table for your pivot table, the pivot table will always be in sync with your data.Make your data into a table-12954013138152. Click on the Table command button 002. Click on the Table command button 23469605994401. Click on the Insert tab 001. Click on the Insert tab 28422604368803. A window will open. Confirm the cells to be included in the table and that the data has headers003. A window will open. Confirm the cells to be included in the table and that the data has headersNaming the tableIt is good practice to name the table as it makes life much easier later when working with formulas. Naming the formula is basically giving your data set a name which will enable you to recognise which data set you need to be working with.8801108756652. Rename the table. For example, “Participants”002. Rename the table. For example, “Participants”35566351898651. Click on the table & select the Design tab001. Click on the table & select the Design tab205923022601. Check the Total Row option in the Table Design tab1. Check the Total Row option in the Table Design tabAdding a total row-1012373970571. In the Total row, click on the arrow to select a function to generate a totalThe formula associate with the function will display in the Formula bar1. In the Total row, click on the arrow to select a function to generate a totalThe formula associate with the function will display in the Formula barAdding a function to a cell in the total rowMicrosoft 365 ONLY Creating a formula to count unique instances of text in a columnTo count unique instances of a text in table row or column, you need to combine 2 formulas The COUNTA formula which counts cells which are empty in the column (or range of cells)=CountA([Column name])The UNIQUE formula which counts unique instances of data in a column (or range of cells)=UNIQUE([Column name])Added together the formula looks like this and can be manually added via the formula bar.=COUNTA(UNIQUE([Column name]))38472841447528Type the formula into the Formula Bar. 0Type the formula into the Formula Bar. 23559412255792Click in the total row in the column where you want to add the formula00Click in the total row in the column where you want to add the formulaIdentifying duplicates15373358070852. Click Conditional Formatting002. Click Conditional Formatting120650020072353. Click on the arrow next to Highlight Cells Rules003. Click on the arrow next to Highlight Cells Rules-10637911357481. Highlight the column you want to search for duplicates and then on the Home tab001. Highlight the column you want to search for duplicates and then on the Home tab220157227795304. Click on Duplicate Values004. Click on Duplicate ValuesUse the Highlight duplicates function in Conditional formatting to identify duplicates209551714539370912061946. The Duplicate Values window will open giving you the opportunity if you want to highlight Duplicate or Unique values and what colour you would like to use.006. The Duplicate Values window will open giving you the opportunity if you want to highlight Duplicate or Unique values and what colour you would like to use.-234954381501. Click anywhere on the table to select it2. Click on the Data tab3. Click on the Remove Duplicates command button1. Click anywhere on the table to select it2. Click on the Data tab3. Click on the Remove Duplicates command buttonRemoving duplicates1760761602924. Select the columns you want to remove duplicates from. If you select multiple columns, all columns must contain a duplicate value for the duplication to happen. The whole row containing the duplicate values will be deleted. 04. Select the columns you want to remove duplicates from. If you select multiple columns, all columns must contain a duplicate value for the duplication to happen. The whole row containing the duplicate values will be deleted. 0190505. The number or duplicates removed will be displayed in a window. If you realise you made a mistake, use the Undo button.5. The number or duplicates removed will be displayed in a window. If you realise you made a mistake, use the Undo button.Adding a unique reference number After having removed all duplicates it’s good practice to give all your rows of data a unique reference number, that way however much you sort and filter them you can always return to the original order.-36195450851. Right mouse click on the table to display the menu2. Select Insert & Table Columns to the Left1. Right mouse click on the table to display the menu2. Select Insert & Table Columns to the Left304801981202. Rename the column2. Rename the column19052851151. Using the + cursor, drag the content down the column 2. Click on the arrow to display the menu 3. Select Fill Series to generate sequential reference numbers001. Using the + cursor, drag the content down the column 2. Click on the arrow to display the menu 3. Select Fill Series to generate sequential reference numbers-353849461062. Select the column you want to format 1. Click on the Home tab 3. In the Number group, click in the Field format field and select a format2. Select the column you want to format 1. Click on the Home tab 3. In the Number group, click in the Field format field and select a formatChanging field formatsFind & Replace Find and Replace can be used to bulk correct data. For example, a constantly repeated spelling mistake.20955107952. Click on the Find & Select tab 1. Click on the Home tab 3. Select Replace 4. Enter Find what & Replace with5. Select Replace All to replace all instances2. Click on the Find & Select tab 1. Click on the Home tab 3. Select Replace 4. Enter Find what & Replace with5. Select Replace All to replace all instances215455511055351. Click on the Home tab 1. Click on the Home tab Splitting columns Often there is a need to break down data to make it easier to work with. For example, if you want to sort people in alphabetical order using their surname you will need to make sure their name is split into first name & surname columns.Tip: Create a column to the right of the column you wish to split for the results.127091443591. Click on the Home tab 2. Highlight the column to split 3. Click on the Text to Columns command button4. The Convert Table to Columns wizard will open 5. Select Delimited & then click on Next 1. Click on the Home tab 2. Highlight the column to split 3. Click on the Text to Columns command button4. The Convert Table to Columns wizard will open 5. Select Delimited & then click on Next *space before a name so that count if doesn’t work -bring it back as an exercise in the formulas.A Delimiter is a blank space, comma, or other character or symbol that indicates the beginning or end of a character string, word, or data item1066801130306. Select what delimiter to use to separate the data into columns. In this example the data to split is separated by a space7. The wizard displays how the text will be split006. Select what delimiter to use to separate the data into columns. In this example the data to split is separated by a space7. The wizard displays how the text will be split4183380190508. Select the data format for each column. General automatically converts the column to the most appropriate format. You can override that now or later after the split008. Select the data format for each column. General automatically converts the column to the most appropriate format. You can override that now or later after the splitMerging columns together (CONCAT)Excel has a built in function to merge text strings together. For example, if you have a first name column and a last name column but you want a Full name column where first and last name are joined together.The function, when using a table, is as follows:=CONCAT(Tablename[@[tablecolumnname1]:[tablecolumnname2]])But Excel can build the formula for you:Add an extra column in your table to the right of the two columns you are mergingEnter the = sign and start typing CONCATSelect the CONCAT function suggested by ExcelSelect the first cell in the 2 columns you want to join together. If you want to add a space between the text you will need to use a slightly different technique:Add an extra column in your table to the right of the two columns you are mergingEnter the = sign and start typing CONCATSelect the CONCAT function suggested by ExcelSelect the first cell you want to join, then add to the formula,” “, and then select the next cell you want to join together. See the screen print below.=CONCAT([@tablecolumnname1]," ", [@ tablecolumnname2])Data Validation The Data Validation feature in Microsoft Excel controls what can be input into a cell to ensure accurate and consistent data. Here we’re Ensure Correct data formatLimit data entry options to a listData Validation for data format1905660401. Click on the Data tab3. Click on the Data Validation command button2. Select the column to validate1. Click on the Data tab3. Click on the Data Validation command button2. Select the column to validate1906294821. Specify what type of data is allowed.2. Chose validation method3. Define parameters001. Specify what type of data is allowed.2. Chose validation method3. Define parameters43547511154. An error message will appear if data outside of the parameters is entered. Here the year doesn’t meet the requirement of being post 1980.004. An error message will appear if data outside of the parameters is entered. Here the year doesn’t meet the requirement of being post 1980.-1123957994651. Follow the steps as above to access the data validation options2. Select List3. Enter the data entry list with each item separated by commas4. A selection list drops down for data entry1. Follow the steps as above to access the data validation options2. Select List3. Enter the data entry list with each item separated by commas4. A selection list drops down for data entry-64770275590Data Validation with list optionsAn alternative to typing the list in is to use source data you already have. For that you’ll need to put the cell references of where the data is into the Source field.5949861107995 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download