Paste with number formatting and formulas intact

On the 7th of January we mentioned we would discuss each of the options from the Clipboard paste options illustrated here. The option we are going to explain today is indicated with the arrow.

 

This icon represents pasting just the formula and number formatting without any other formatting.

To begin, we will create a very simple worksheet with column A totaled in cell A7 using the formula =Sum(A1:A6) as shown here:

 

 

We formatted cell A7 as currency, with a yellow background, and a thick border. We have selected cell D7 so the figure will show the formatting as an indication of where we are going to paste the results. Next we reselected cell A7 and then copied it to the clipboard. To copy the cell you can use the keyboard shortcut Ctrl-C.

When we paste using the option discussed before (), we will get the results with the formula and number formatting intact as shown next.

As you can see, the currency formatting is there but the background color and the thick border is not.

Paste with formula or value without formatting

On the 7th of January we mentioned we would discuss each of the options from the Clipboard paste options illustrated here. The option we are going to explain today is indicated with the arrow.

This icon represents pasting just the formula or value in the cell without any formatting.

To begin, we will create a very simple worksheet with column A totaled in cell A7 using the formula =Sum(A1:A6) as shown here:

 

 

We formatted cell A7 as currency, with a yellow background, and a thick border. We have selected cell D7 so the figure will show the formatting as an indication of where we are going to paste the results. Next we reselected cell A7 and then copied it to the clipboard. To copy the cell you can use the keyboard shortcut Ctrl-C.

When we paste using the option discussed before (), we will get the results with the formula but no formatting as shown next.

As you can see, a total is now in cell A7 but the background color, thick border, and currency formatting is not.

First paste option

On the 7th of January we mentioned we would discuss each of the options from the Clipboard paste options illustrated here:

We will being with the first option (top row, far left) and give you a brief rundown of how each of these options will paste a formula from the clipboard. To begin, we will create a very simple worksheet with column A totaled in cell A7 using the formula =Sum(A1:A5) as shown here:

 

 

We formatted cell A7 as currency, with a yellow background, and a thick border. We have selected cell D7 so the figure will show the formatting as an indication of where we are going to paste the results. Next we will reselected cell A7 and then copied it to the clipboard. To copy the cell you can use the keyboard shortcut Ctrl-C.

When we paste using the option discussed before (), we will get a straight paste, from one cell to the other, copying the formatting and the formula. When we paste into cell D7, we get the total of column D with the formatting of the cell intact as shown here:

Notice the formula can be seen in the formula bar specifying that the total is for column D. This is the same result as if you pasted using the Ctrl-V shortcut.

Pasting options in Excel

When pasting from one location to another in Excel, you have a number of different options which are reached among other ways by pulling down the list on the Paste command on the Home tab as shown here:

The pull-down list options are illustrated in this captured figure:

 

Over the next few days we will explore each of these options.

Excel Shortcut keys for copying and moving

I write for Professor Geek eBooks and, as such, we will do our best to help you out with shortcut keys for Excel, Word, and Windows (most keys work the same in all Microsoft programs and Windows). Some of our installments will be simple, some more complicated and will require extensive explanations. Our first installment is about keys used to copy and paste. 

 When you have selected something in a Microsoft program or generally anyone else's program, you can copy what you have selected onto the clipboard by press Ctrl-C (hold the control key down and press C). Once it's on the clipboard you can positions the cursor some other place in the current program or in some other program and then press Ctrl-V to paste what was captured into the new position. For example, if you select a paragraph in Word, or several cells in Excel and then press Ctrl-C, a copy of the selection will be placed on Windows clipboard. Then when you move to some other position in the same program or a different program and press Ctrl-V, the selection will appear in the new location.

 If you want to have the selection only in the new location  (move it instead of copy it), use Ctrl-X instead of Ctrl-C and the selection will be placed on the clipboard and deleted from the current location.

My first Professor Geek eBook is out on Amazon

My first Professor Geek eBook is out on Amazon. It is called "Professor Geek Explains Excel's Ribbon Interface" and it covers all the commands on Excel's Ribbon Interface. This blog will cover some things the book discusses and new things that have been discovered. The introduction to the book on Amazon is as follows:

     Microsoft Excel is one of the most powerful and widely used programs in the business world. If you have yet to experience the power of this important tool or simply want to learn more about Excel, this is the book for you. It is a book for anyone who has never used Excel, those who haven’t used a version of Excel with the Ribbon Interface, and for anyone else who has ever had trouble finding something they know Excel does but is not sure where to find it in Excel’s Ribbon interface.
Excel is very good at calculations and putting data in charts and graphs, but it is much more versatile than simply being a tool for number crunching. You can use Excel to manipulate all types of data both text (words) and numbers. You can arrange data in different orders to better analyze it and you can filter data to isolate the most important items. You can add graphics (images and photos), animations, and sound to your worksheets to make them more interesting to you and your users. You can use macros to automate repetitive tasks. These and many other tasks can be performed with the thousands of commands on the Ribbon Interface and you need to be thoroughly versed in the functions found on the Ribbon in order to get the most out of Excel.
     Many online sources and other books about Excel frequently give the steps to perform without illustrating how the task is to be done. This book, on the other hand, has over eight hundred images that leave nothing to the imagination as they show you precisely what you will see and what you need to do each step of the way.
     Because this in an eBook rather than a regular printed book, it has several advantages over an ordinary book. If a passage in the eBook references another portion of the eBook, there will be a hyperlink to that other portion so you can go there instantly. This eBook also has a large, specially created index with over 1500 entries that are hyperlinked back to the passage where the topics are explained or introduced. Prior to the index, five appendices (B through F) subdivide the index into groups to make it even easier for you to find exactly what you are looking for. For example, Appendix E contains a hyperlinked list of all the commands explained in the eBook. Appendix A is a bit different in that it contains a list of all of the captions of the numbered figures in the eBook so you can easily find the figure you want. In addition, most of the index entries have an explanation to help you decide which index entry you want to use. For example, instead of simply listing the entry Greater Than and giving you two locations where Greater Than can be found, there are two separate entries for Greater Than, which are:

Greater Than… option (Highlight Cell Rules)

Greater Than dialog box

     The first entry tells you that the Greater Than… option is in the section about Highlighting Cell Rules while the second entry refers you to the Greater Than dialog box. As mentioned, the entries will be hyperlinks that will jump directly to the information about the topics rather than simply giving you page numbers as a printed book would do.