Excel tips: Ultimate crash course series
- Part 1 – Copy, cut & paste in Excel
- Part 2 – Conditional formatting in Excel
- Part 3 – Pivot Tables in Excel
- Part 4 – Mathematical Formulas in Excel
- Part 5 – Excel formatting guide for a great looking spreadsheet
- Part 6 – Essential Excel formulas like vlookup
- Part 7 – Filtering your data in Excel
The basicsExcel has some clever features when it comes to inputting text, such as automatic date and time formatting. This is useful and at times, when it’s unwanted, a little annoying. Luckily there are ways to ensure that you control what the cells show. In the next video, we will take a look at how Excel deals with numbers entered into a cell. Text can be entered into cells in Excel although we would never recommend using it as a word processor, like Microsoft Word. When a cell is selected, start typing to enter text into the cell. For general text – words & letters, or a mix of words and numbers, what you type is what you will see. There are also some important points to note when entering dates into a cell: OK, so now you know how to enter text, we’ll put together our first spreadsheet: In this video, we will look at how to enter data into a spreadsheet and how to manipulate column width.
Copy, cut, paste, paste, special
Now the basics are out of the way, let’s start using some simple functionality: copy, paste and paste special.
You will often find yourself needing to copy data when using Excel.
Much of editing in Excel is similar to editing in other Microsoft Office applications. So if you know how to copy and paste text into Word, you know how to do so in Excel. This article will give you a set of simple tips and tricks to copy/paste more effectively into Excel. ‘Copying’ in Microsoft Excel duplicates the contents of all the cells that are selected. These are then held temporarily on the ‘clipboard’, ready to be ‘pasted’ somewhere else. Usually, the content of the clipboard isn’t displayed but held invisibly in the computer’s memory ready for pasting. Being able to copy is very important when dealing with large amounts of data as you’ll often need the same information in multiple locations. Cells that are selected ready for copying appear grey with a bold, dark border. As with other Microsoft Office applications, there are many ways of performing ‘copy’. To keep things simple, we recommend the two most common – one using the keyboard, and another using the mouse:
- Ctrl+C (Hold the Ctrl key down and press C)
- Right click on the selected cells to bring up the menu and select Copy
CutIf you want to move the content to a different cell on your spreadsheet, then you need to use Cut instead of Copy. Here’s how you do that:
PasteOnce you have copied or cut data from a cell, you’ll want to paste it somewhere. ‘Pasting’ transfers the contents of a cell or cells (that were previously copied and held in the clipboard) to the selected cell. Where more than one cell was copied, the cell selected for the paste will be where the top left hand corner of the selection is – the rest of the selection will have the same relative placement as in the original data. As with copying, to keep things simple, we recommend using just one or two of the many ways of pasting – one using the keyboard, and another using the mouse:
- Right click on the selected cells to bring up the menu and selecting Paste
What’s being pastedCtrl+V, or simply ‘Paste’ from a menu, reproduces the contents and formatting of the copied cell. If whole rows or columns were selected, their height/width would be copied too. If the cell has a comment attached, that comment will also be copied.
- The content of the cell is whatever appears in the Formula bar when the cell is selected. This content can be a formula (in which case it is preceded by an = sign) or data.
- The result of the cell is what numbers or text appear in the cell in the worksheet. The result can change when a cell is copy/pasted elsewhere, if the content is a formula. Data cells will always stay fixed when copied – their content and result are identical.
- The formatting of the cell includes:
- The appearance of the cell (fill colour, border, font type and size, orientation of text within it, etc.) and
- The ‘type’ of cell – whether the cell contains numbers, text, or a date (more in our Cell Formatting and Number Formatting units)
Partial pasting and paste specialSometimes it’s useful to paste only some attributes of a copied cell. Excel offers a few options. Three favourites are: Paste Values – By default Excel copies over a formula, but sometimes you just want the result. Paste Values does that for you. The shortcut for Paste Values is Ctrl-Alt-V-V (then hit enter). Paste Values and Number Formats – is the same as Paste Values but it brings over the format of the original cell. This is particularly useful when copying over dates or a currency but you don’t want to copy the formula from the original cell. The shortcut for Paste Values and Number Formats is Ctrl-Alt-V-U (then hit enter). Transpose allows you to flip rows and columns around in seconds. Turn a row of numbers vertical or vice-versa by simply copying and then using Paste Special – Transpose. The shortcut for Transpose is Ctrl-Alt-V-E (then hit enter). Pasting values: Transposing: If you haven’t done so yet, open up Excel and start practicing your copying, cutting and pasting. Knew all that already? In the next post, part 2 of the series, we get straight into the good stuff with Conditional Formatting. See you then! These tips were provided by filtered. AAT students and professional members can access a wide range of Excel resources and training.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.