The information on this page references the resource Party_Budget_Workbook_2 [XLSX, 11kB]. Download this workbook, if you haven’t already, and use it to work through the information below. This workbook has been formatted according to the instructions on the Document formatting page of this module.
After formatting your document you might like to add some more data. This is often easier using autofill and flash fill techniques, as well as splitting columns of data to make it more manageable.
When using Excel, you may be working with data in a series. You can use AutoFill to save time entering each item in the series into your spreadsheet. Test this by first adding a new sheet in the Party_Budget Excel workbook, by pressing this symbol at the bottom of the screen:
Double-click on the new sheet name to change it. For this example, we’re going to name it Party_Guests.
On the new sheet, enter the numbers 1 and 2 in cells A1 and A2, respectively. Highlight both cells, and hover your mouse over the bottom-right corner of the “2” cell. When the black + symbol shows, click and drag downwards to row 20. The cells will automatically be filled with the numbers 3–20. This works for common sequences (such as days of the week), as well as patterns that Excel detects in your series.
Flash Fill is handy when you want to combine existing data to work with it more efficiently. To try this, type the first and last names of at least five guests you would like to invite to your party in columns B and C of the Party Guest sheet. Example:
Now, in column D, type the first AND last name of the first person you are inviting (e.g. Sarah Williams) in the same row (e.g. row 1). Press Enter, and start doing the same for the second person in your list. As you begin typing the second name you should see the rest of the names automatically filled in for you in the remaining cells; press the Enter again and Excel will fill in the cells for you. It should look something like this:
If it does not automatically fill in the cells for you, click on the next cell you wish to fill, go to the Data tab and select Flash Fill in the Data Tools section to use Flash Fill instead.
Sometimes you may want to do essentially the opposite of the above, and split a single column into multiple columns. For example, you might already have a column containing both first and last names that you want to split into a column of first names and a column of last names, or you may have dates that you want to split into three separate columns for day, month and year. We will do the latter now, but first we will make a copy of the Party_Budget sheet to do it in. To do this:
The Convert Text to Columns Wizard will appear:
In this wizard, you will need to specify things such as the type of data, how it has been separated in the current column, and where you want to place the new data. For this example:
The Party_Budget_Copy spreadsheet will now look like this:
If you are going to continue on to Sorting & filtering return to the original Party_Budget spreadsheet.