All Microsoft Microsoft Excel certification exam dumps, study guide, training courses are prepared by industry experts. Microsoft Microsoft Excel certification practice test questions and answers, exam dumps, study guide and training courses help candidates to study and pass hassle-free!

2.1 Insert Data in Cells and Ranges

2.1.1 Replace Data

As you become more familiar with Excel, your worksheets and workbooks will increase in size, and the amount of data you're storing will also increase. You need a means to easily navigate throughout your worksheets and workbooks to find information of interest.

You can search your worksheet for every occurrence of a value or string reference by using the Find tool, which is available on the Home ribbon in the Find and Select group here. The Find and Replace dialogue box has an array of options that you can display or hide depending on what you want to find. If we hide the options, you can see that you simply type whatever it is that you're looking for into the "Find What" box and then specify if you want to find all occurrences or just the next one. If you specify "Find of," then the cell addresses of the matches will be displayed in a list like this.

You can then navigate to the addresses by clicking on the listed items. The Options button provides a range of additional things you can specify, such as searching within the current worksheet or searching within the entire workbook. You can search by rows or columns. If you specify rows, then the search will be from left to right across each worksheet or workbook, starting at the top row. If you specify by column, the search will go down each column, starting from the leftmost one. The look-in option allows you to specify values, only underlying formulas, or any comments attached to a cell. The "match" case means that you can specify that the search should exactly match the upper- and lower-case characters in the search specified.

So if you have this checked, then Dave in all lowercase letters won't be found if you specify capital Dave as the search. If you're searching for text data, then you can use the match entire cell contents option to only find exactly what you type into the Find What box. In other words, if I type Dave with a capital D and the cell has Dave Murphy, it won't make a match; it must be exact. The next useful option on the Home Find and Select menu is the Replace option. Here you can specify that you want to replace what is found in the search with a new set of characters. For example, I could specify that if Dave is found, then replace it with David. Then you can optimise all matches or just find them first, and then you can replace them one by one in order to make sure that you're happy with the replacement.

2.1.2 Cut, Copy or Paste Data

All Microsoft products allow you to copy existing data simply by selecting the data you want, right-clicking on the mouse, and selecting the Copy command. Nothing appears to happen. But in actual fact, the data has been copied into the memory of your computer and can be accessed by using the Paste command. If you want to make a copy and then paste it somewhere else in the worksheet, simply click on the first row in the new position and select Paste from the right mouse button. The information you copied will remain in the computer's memory until you copy something else. So I can paste the information repeatedly if I wish.

Now, I can copy the contents of one cell and paste those contents into multiple cells all at once. So, for example, I can copy the contents of this cell, and if I want that to appear in the range of cells from D 10 to D 16, then I just select that range of cells and then paste. This is true of anything that you copy. It doesn't have to be text; it could be a formula. Also, the cell references will be automatically adjusted. You can also copy between worksheets.

So I can copy the data here, go to the Maintenance Schedule worksheet, and paste the information there. The copy command doesn't affect the original data; it remains where it is. There is, however, another command, the Cutcommand, which also puts the selected information into memory on the computer. But in this case, the original information is removed from the worksheet.

You can, of course, still paste the data anywhere that you like, but that's the important difference between copying and cutting. In effect, if you use the Cut command, you're removing the original data from one position to another. Notice that when you use Copy or Cut, the selected cells are indicated by the highlighted border, which is highlighted and moving. If you want to deselect this, just click on the escape key. Otherwise, this highlighting disappears the next time you enter any information into the worksheet. Notice also that these commands are available on the ribbon from the Home tab and the Clipboard group.

The clipboard is also the name given to the area of memory that is used to store information that has been cut or copied. The paste command is not selective. It pays exactly what you cut or copied into the specified area. Paste Special, on the other hand, provides you with many more options and is the subject of the next lecture. Rather than using cut and paste, you can move data around the worksheet using the mouse. Just place the cursor anywhere on the edge of the cell you want to move, and you can see that the cursor changed shape. Now, if you hold down the left mouse button, you can drag the data anywhere you want.

When you released the button, the data was moved to the new location—drag and drop. Finally, you should learn to use the keyboard shortcuts for these commands because, as you become more experienced with Excel, you'll use the cut, copy, and paste commands many times over. So it's a good idea to learn the shortcuts for these commands: control plus cup causes the cup to happen, control plus X causes the cut to happen, and control plus C causes a pace to happen. It's well worth your while to learn these shortcuts.

2.1.3 Paste Data by using Special Paste Options

Special provides a much greater level of control over what exactly you want to paste. After you've copied or cut some data in your worksheets, the standard page command will paste the cells you selected with a cut and paste into the target cells, including data, formulas, formatting, and any comments.

However, in some cases, you may not want to paste all of this information at the same time. The Paste Special menu displays all available options for pasting the selected cell contents to a target cell range. This menu enables you to selectively choose to paste only the data, values, formulas, formatting, comments, or a combination of these, so you have a lot more control over deciding what it is you want to paste.

Once you've copied some data, you can access the Paste Special options by positioning the cursor where you want to paste the data and then by clicking the mouse. Right-clicking on the mouse You can also get it at the Paste Special menu by selecting it from the Paste command in the Clipboard group. As I move the cursor over each option, you can see the abbreviated explanation for each of the options. Alternatively, I can invoke the Paste Special dialogue box here; these options are exactly the same as those available on the right mouse button.

You can see that you can choose to be selective in what you paste. For example, you could decide only to paste the values in your copied data and not any formulas that may have been used to generate that data. Alternatively, you could choose to only paste the formulas and the results of executing those formulas. For example, if I enter a simple addition formula such as this and then copy it, if I specify values only in the Pastel option, you can see in the formula bar that the original formula was not pasted, only the resulting value. If I change the format of the data in this cell, let's increase the text size and make it bold. Now I can copy it as usual, but specify that I only want to paste the formatting like so.

Now anything I put into this cell will have the same format as the original. It's worthwhile to notice that the "Paste Special" dialogue box is categorized. The Paste section specifies what components of the data on the clipboard are pasted to the target cells. We've seen some examples of these commands already. The operation section specifies how any data in the target cells will be treated. For example, if multiplied is selected, then any existing data will be multiplied by the data pasted into the target cells.

So, for example, say I have the number 10 in this cell and 20 in this cell. Now I copied the ten and used Paste Special to specify that I wanted to use the multiply operation. When I paste, the result is 200 because ten multiplied by 20 is 200. Skip blanks if selected. This will avoid overwriting any existing data in the target cells if there is no data from the clipboard to be put into that cell. For example, if I copy the data in B1 and B12, you can see that there are blanks. Now I'll paste special data into one and select skip blanks.

You can see that the blanks in the Copy data do not override the data in the Paste range transpose. If selected, this will rotate the data. So if I select a range of cells and copy them, now I go to this cell and use Paste Special with transpose-selected. As you can see, the data has been moved from a vertical column to a horizontal row. As a result, the data in the clipboard that was previously listed down the column will now be listed across the row in the target cells. Similarly, data in a role that is listed across sales will be listed down sales.

2.1.4 Fill Cells by using Auto Fill

Can't recognise patterns of data as you reenter the information into your worksheet. For example, if I enter January and February into these two cells, then I can use Autofill to complete the other months of the year automatically without having to type the information in manually. I just selected the first two cells and went to the right-hand corner of the second one.

You can see that the cursor shape changes at this position. Now, when I hold down the left mouse button and drag to the right, you can see that Excel is showing me the data that will enter if I let go of the button. So I'll drag until I get to December and then let go. That's Autofill. It's a very useful utility.

This will work for any sequential pattern of data. It doesn't always work, but it can be very useful and save you time and effort. As another example, suppose I'm making plans for 2017, 2018, and 2025. Again, Autopilot will detect a pattern, just like if I'm using formulas, Excel can also detect patterns. Assume I put 20 in this cell. In the adjacent one, I put in a simple formula that is equal to this cell multiplied by 2. Now I can autofill across the worksheet by replicating the formula. Autofill doesn't work with text data generally, except for special cases such as names of months or days of the week, as there are usually no sequential patterns in a piece of text. Flash Fill is a slightly different feature that serves as an illustration.

So if you look at the information entered here, we have people's surname, middle name, and first name listed. If I type in their full name into this first row on the list, I can use Flashfield to complete the others and save me a lot of time. I just go down into the next cell, and all you have to do is type the first letter of the next person's name.

You can see that Excel is picking up the pattern and will automatically complete the information without you having to type it in. Notice that Excel is showing what it thinks you want, but you need to confirm that's correct by pressing the Enter key. You can also get at the flash field command from the Data tab and the Data Tools group. Once again, if I select the first cell in the pattern and click on Flash Fill, the rest of the data is completed for me automatically.

Microsoft Excel certification practice test questions and answers, training course, study guide are uploaded in ETE files format by real users. Study and pass Microsoft Microsoft Excel certification exam dumps & practice test questions and answers are the best available resource to help students pass at the first attempt.