This also applies to Office 2007 and 2010. Based on my original post Feb 2004 relating to Office 2003 and prior.
Most people know how to use copy and paste in Office. Or do they? Right click a selected item(s) and copy, then right click the destination and paste.
That is definitely the slow way. Keyboard people know about Ctrl+C and Ctrl+V for Copy and Paste. (or CTRL+Insert / Shift +Insert)
New Office, (2007, 2010, 2013) uses the Paste button in the ribbon to provide access (though there is still keyboard access with ALT+E+S).
Once you have something in the clipboard with the copy command, clicking the little arrow below Paste Icon in the ribbon gives you a lot more options. Each office application is slightly different as to what you get.
Word has less options than Excel. Powerpoint and Outlook, Live Writer and Publisher etc. all use this feature differently. However each of them allow you to strip metadata (formatting etc.) from the actual text and just paste the text. This extremely useful when copying text from a web page, PDF file or some other heavily formatted document.
And clicking the Paste Special link at the bottom brings up the traditional dialog box.
Ill take you through the main ones for Excel.
You can also combine options from each section as per the example below.
The IF function is a very useful logical tool. I have written an explanation on the IF Function before so I wont repeat that here. There are a number of other posts also that incorporate the use of the IF Function.
I use it a lot to compare lists of data. today I had been sent some data in a spread sheet that contained email addresses. I had to create a mail out to those email addresses and when i did that I received a number of bounce backs for incorrect email addresses. I then received a second email from the original person containing a revised spread sheet with the corrections to email addresses. given that there were a large number of addresses and only a few changes I didn’t want to painstakingly work my way through the list looking for changes. Instead I copied the new list beside the old list and then wrote an if statement to compare the original with the revised. It is very simple and uses a 1 or 0 result. You could use anything there including TRUE or FALSE or “OK” and “ERROR” etc. Then a simple filter showed me the incorrect ones and the fixes i needed to apply in my mailing list.
Obviously I have obfuscated the email addresses in this example for privacy reasons.
The actual IF statement is very simple. In Cell C1 I wrote =IF(B2=A2,0,1). Filling it down is as simple as double-clicking the right hand bottom corner of the cell.
Then I added a simple filter to hide the correct results and only display those with differences.
I use this technique many times a week in comparing data lists.
While looking at previous posts to choose a different function for this weeks post I noticed that I have never written a post specifically about the most commonly used function in Excel, the SUM function.
So today’s post remedies that. Note this post is based on Excel 2010. Most of this is still relevant in Excel 2003 and Excel 2007 is largely the same as 2010. The main difference is the ribbon references in Excel 2007/2010 vs the toolbar in 2003.
The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.
The SUM function syntax has the following arguments:
number1 Required. The first number argument that you want to add.
number2,,... Optional. Number arguments 2 to 255 that you want to add.
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.
As the title says this just an introduction. Its aimed at non power users who have recently moved to Office 2010 or 2007 from Excel 2003.
The main reason for this article is to demonstrate the basic use of the new table features and the ribbon features available to users.
I am going to start with showing you how to make a table from existing data and demonstrate a few of the ribbon features. I wont be going into anything too complicated in this post. Depending on feedback I might write some more specific detailed feature articles on table components and using tables later including converting tables back to ranges..
So how do we make a table – its actually very simple. In the following screenshots you will see a fairly typical spreadsheet with some information in it. I will show you how to turn that into a table.
Select any cell in the table.
From the Home Tab on the Ribbon click on the Format as Table Button.