Page 2 / 20
For large data sets, we start with perhaps the most basic operations: browsing and editing the data.
In large data sets, it is almost necessary to use various shortcuts to select and edit data, because it is too cumbersome to make selections with the mouse alone. So first we practice moving and selections by making formatting in the data. At the same time, we will go through some date formats.
Guide
### Format of the data
A typical list in Excel contains information in such a way that each record is on its own row. The record rows are usually preceded by a header row.
When dealing with large data, it's a good idea to keep it so to say clean: don't add blank rows or subtotals. When the data is coherent, we can use the tools in Excel to visualize it.
All the tasks of the Data analysis package are done in the Data_processing.xlsx workbook. Download it for yourself.
⬇ Data_processing.xlsx XLSX · Data_processing.xlsx The Date column is formatted using Short Date
The Price column is formatted using the Number
Formatting only affects the data cells, not the headers.
Task: Select all cells containing dates with shortcut keys
In this exercise, we will select all cells containing dates, so that they can be treated as a whole in the next task.
Do the following:
Go to cell B1
Scroll down and check how many rows there are in the data
Go back to the top
Go to cell B2 (i.e. the first cell containing a date)
Select cells downwards so that you have painted all cells containing dates
Nice
Fast ways to move and select
When the amount of data is large, it is important, among other things, how easily areas are moved around or painted. You can practice the following keystrokes in the Sales spreadsheet of the worksheet.
Try these key combinations in the workbook now to help you move and select more smoothly. The shortcuts can feel a bit unnecessary if you haven't used them before. However, they are worth a try: You'll find that you can really speed up your work when dealing with long documents.
Guide
### Shortcut keys
`Ctrl+Home`
Moves to the top of the sheet
`Ctrl+End`
Moves to the last cell in the sheet where there is or has been data.
`Home`
Moves to the beginning of the row
`Ctrl+
Moves upwards through the data until an empty cell is encountered. Moves up from an empty cell until it reaches a cell with data.
`Ctrl+
Moves down through the data until an empty cell is encountered. Moves down from an empty cell until it reaches a cell with data.
`Ctrl+
Moves to the right in the middle of the data until an empty cell is encountered. Moves from an empty cell to the right until a cell with data is encountered.
`Ctrl+
Moves left through the data until an empty cell is encountered. Moves from an empty cell to the left until a cell with data is encountered.
The same keys can also be used to select sections by additionally using the Shift key. For example, when in a non-empty cell, Shift+Ctrl+<i class="fas fa-arrow-down"></i> will select cells down from it until an empty cell is encountered.
Task: Format the dates
Format the date fields in the Sales sheet of the exercise file.
You should have all cells containing dates selected. Format them to use the Short Date
Finally, change the width of the column to fit.
Task: Add the thousands separators
Format the Price column data to have thousands separators.
Nice
Paint only the numbers, not the whole column.
This topic was covered in Basics of Spreadsheets, so you should already be familiar with it. You can, of course, go over the topic to recall it.
The shortcut key for the thousands separator formatting is Shift+Ctrl+1.
Guide
### Shortcut keys
`Shift+Ctrl+1`
Enable the **Number ** formatting so that numbers use the thousands separator and there are exactly two digits to the right of the decimal separator.
Great! You have now made a start. Now we have learned to edit the big data a bit. Next, we will look at how we can add data to a large dataset.
Are you ready to move forward?