Friday, July 20th, 2012
Here are five cool tips, tricks and keystrokes to help your day go faster in Excel:
Display cell formulas and not results
If you want to see the cell in your worksheet display formulas rather than the results of those formulas then you can do it one of two ways.
Use the keyboard shortcut Ctrl + ~ to toggle formula display on and off
You can also use Formulas > Show Formulas
Start a new line
When you need to add a line break to a cell to start a new line of text press Alt + Enter in the cell. If you just want to wrap a long piece of text in a cell right click the cell and choose Format > Alignment tab > Wrap Text.
Copy the contents of the cell above
To copy the contents of the cell above into the current cell press Control + ‘.
Moving around super fast and super smart
To move from one sheet in a workbook to the next (or in reverse), press Control + PgDn and Control + PgUp. To move to the next open workbook press Control + Tab or Control + Shift + Tab.
Super quick mouse free SUM formula
Skip taking the mouse to your Ribbon to add a SUM function and do it with a simple keystroke instead. Type Alt + = and Excel adds the SUM function automatically to the current cell. Doesn’t get much easier than that!
Helen Bradley
Labels: cool, copy formula, Excel 2007, Excel 2010, excell, format, Helen Bradley, keystrokes, line break, new line, shortcut key, show formulas, sum function, techniques, tips, tricks, Wrap text
Categories:office
posted by Helen Bradley @ 7:34 am9 Comments links to this post
Wednesday, May 2nd, 2012
Each of the Office applications allows you to configure a look for a document, spreadsheet, publication or presentation and then save it as a template so that you can reuse it anytime.
Templates in PowerPoint 2010
In PowerPoint you’ll create your presentation and then save it using File > Save As and select from the Save As Type list choose PowerPoint Template (*.potx). Give your presentation template a name and click Save.
In future you can locate this presentation by choosing File > New > My Templates and click on the template to use it as the basis of a new document.
Templates in Word 2010
In Microsoft Word choose File > Save As and from the Save As Type dropdown list choose Word Template (*.dotx). In the top left of the dialog click the Templates option to make sure that the template will be stored in the correct location and give the template a name.
In future choose File > New > My Templates and select the template to use.
Templates in Excel 2010
In Microsoft Excel, you can save the look of your document so that it can be used as the basis of a new document by choosing File > Save as and choose Excel Template (*.xltx ) as the file type. Type a name and save the template file.
In future you can base a new worksheet on this template by choosing File > New > My Templates and select it from the Personal Templates list.
Templates in Publisher 2010
In Publisher you’ll choose File > Save As and then select Publisher Template (*.pub). Type a name for the template and click Save.
In future you can access this template by choosing File > New > My Templates, select the template and click Create. You may need to close and reopen your software before the new templates are available.
Helen Bradley
Labels: Excel, Excel 2010, Helen Bradley, how to, PowerPoint, PowerPoint 2010, publisher, Publisher 2010, save as a template, templates, tips, Word, Word 2010
Categories:Uncategorized
posted by Helen Bradley @ 8:00 am1 Comment links to this post
Monday, April 30th, 2012
You can save a chart as a picture from inside Excel so you can use it elsewhere such as in a report or on the web.
To do this you will use a workaround and the simplest way to do it is to size the chart on the worksheet so it is a good size. Choose File > Save As, select the location to save the file in and from the Save As Type dropdown list choose Web Page (*.htm;*.html), type a name and click Save.
This converts the worksheet to an html file and because html files cannot contain images the chart is saved as a separate png image file and linked to the html file.
You can find your chart in the folder that you saved the html file into. So, if your file was called sales.htm then the images will be in a folder called sales_files. Of course, you’ll also need to save your Excel worksheet if you want to save the data and chart to work on in Excel in future.
Helen Bradley
Labels: Excel 2010, gif, graph, Helen Bradley, png, save chart as image
Categories:Uncategorized
posted by Helen Bradley @ 8:00 amNo Comments links to this post
Tuesday, April 24th, 2012
In Word 2010 you can set keyboard shortcuts for ribbon buttons using the program options. Choose File > Options and click Customize Ribbon. Click the Customize button at the foot of the dialog – this is available in Word 2010 but not in Excel 2010 curiously.
Click the tab that you’re interested in and locate the button on the tab that you want to customize. For example, if you choose the Home tab and click Bold as the command you’ll see that there are already keyboard shortcuts associated with that command.
Many other commands do not have keyboard shortcuts associated with them and if you want to add them you can do so.
For example, there is no keyboard shortcut for the Font Color Picker. Select Home tab and then Font Color Picker and press a new shortcut key. For example the shortcut Alt + Ctrl + Shift + C is not currently assigned.
Click in the box and press that keyboard combination. If you want to use that for the Font Color Picker, you can do so by clicking Assign. This assigns this keystroke to the Font Color Picker tool.
To test this, click Close and then Ok. Now when you select a piece of text you can apply the currently selected font color to it by pressing Ctrl + Alt + Shift + C.
Helen Bradley
Labels: customise, customize, Excel 2010, Helen Bradley, key shortcuts, keyboard assignments, Keyboard Shortcuts, keystroke short cuts, short cut key, Word 2010
Categories:Uncategorized
posted by Helen Bradley @ 8:00 am1 Comment links to this post
Thursday, April 12th, 2012
The Quick Access Toolbar or QAT runs across the top left edge of the Word 2007 and 2010 window. It also appears in other ribbon compatible programs like Excel 2007 & 2010, PowerPoint 2007 & 2010.
The QAT is a handy place to put icons that you use all the time. It can be customized through this Quick Access Toolbar option.
Click this icon to show the QAT editing options. Click Show Below the Ribbon to place the Quick Access Toolbar below the ribbon – I think most people will find its current position acceptable but if you want to move it that’s how to place it elsewhere.
Choose More Commands to add more commands to the Ribbon. From the Choose Commands From list you can select commands to view. These include Popular Commands, Commands Not In The Ribbon, in other words commands that are available in Microsoft Word but for which you have no other easy way of accessing, All Commands or Macros. The remainder of the dialog gives you access to the individual tabs in Word so that you can get access to icons listed there.
Some options you may want to add to the Quick Access Toolbar include the Close/Close All Button, Quick Print and I like to add Switch Windows which is available from the All Commands list. Other tools that you use frequently can be added to the Quick Access Toolbar making them instantly accessible.
You should note that you can set the features for all documents or for just an individual document so that you can, for example, set a different toolbar for a specific document. When you choose this option the specific document will get all the tools on the standard quick access toolbar plus those that you’ve added to just its toolbar.
Helen Bradley
Labels: Excel 2007, Excel 2010, Helen Bradley, mini toolbar, OneNote 2010, Outlook 2010, PowerPoint 2007, PowerPoint 2010, Qat, quick access toolbar, Ribbon, Word 2007, Word 2010
Categories:Uncategorized
posted by Helen Bradley @ 8:00 am1 Comment links to this post
Tuesday, February 21st, 2012
In some instances you may want to sum a column of numbers depending on the contents of that column. For example, if you want to total the sales figures in the range B3:B35 but only where those values are greater than 10000, this SUMIF formula will do the work:
=SUMIF(B3:B35,”>10000″)
The Sumif function takes first the range to sum and then the condition to match. In this case you will be summing all values in column B from rows 3 to 35 inclusive which contain values greater than 10000.
The SUMIF function can also sum a different range than that used for the test. Here it sums all values in the range D2:D19 where the corresponding values in the range C2:C19 are Produce.
=SUMIF(C2:C19,”=produce”,D2:D19)
Helen Bradley
Labels: conditional sum, Excel 2010, sumif
Categories:Uncategorized
posted by Helen Bradley @ 7:14 amNo Comments links to this post
Thursday, February 16th, 2012
One of the handiest calculation tools in Excel isn’t a function and instead it appears automatically on the Status Bar.
Select a series of numbers and in the Status Bar you will see, by default, the Sum of those numbers.
Right click the Sum and you can select from other calculations such as Min, Max, Count Items, Count and Average. These calculations are useful when you need to quickly check a calculation. It doesn’t get any more convenient than this.
Helen Bradley
Labels: average, Excel 2010, formulas, functions, status bar, sum
Categories:Uncategorized
posted by Helen Bradley @ 8:55 am4 Comments links to this post
Tuesday, January 10th, 2012
In Excel 2003 and now in Excel 2010 , there are pattern fills which you can use to fill chart bars so your charts print just great in black and white.
Unfortunately the same feature was removed from Excel 2007 – wtf? I have no clue why but it was but it has to be a very silly thing to have done.
If you are using Excel 2007 and you need to use pattern fills with a chart you are out of luck – well not really – you just need to read the rest of this tip because I can tell you how to put the fills back into Excel 2007.
To begin, download this handy add-in: http://officeblogs.net/excel/PatternUI.zip
Update: This link is no longer live so patternui.zip is not longer available. You can find an add-in here (with instructions which achieves the same thing) courtesy of Andy Pope.
The zip file contains a single file patternUI.xlam which you need to extract and place somewhere you will find it easily and where it won’t get deleted by accident. You could make an Excel add-ins folder for it, for example.
Once you’ve done this, open Excel 2007 and choose the Office button > Excel Options > Add-ins and from the Manage dropdown list, select Excel Add-ins and click Go. This opens the old Add-ins dialog from earlier versions of Excel. Click Browse and locate the .xlam file that you just unzipped and placed somewhere safe. Select it and click Ok. Ensure that the PatternUI option appears in the Add-ins available list and that it is checked and click Ok.
Now create an Excel chart. Once you have you chart, click on the data series to fill with a pattern – if you have a single series plotted then select just one of the columns at a time. Select the Chart Tools > Format tab and notice that you now have an option called Patterns available. Click the Patterns option and select a pattern to apply to the currently selected chart series or column. Click on each series or column in turn and apply a pattern to it. When you are done, you can print your chart as usual.
Installed add-ins are managed automatically by Excel so you will find that the add-in will still be there and accessible next time you use Excel.
If you are using Excel 2010 you don’t need this add-in as the pattern fills are back where they should have been all the time.
Helen Bradley
Labels: add-in, black and white, charts, download, Excel 2007, Excel 2010, free, pattern fills
Categories:Uncategorized
posted by Helen Bradley @ 9:55 am36 Comments links to this post
Wednesday, December 28th, 2011
Ok.. so you want to use an Excel formula and you know it is, say, SUMIF that you want to use, but what data does it need and where?
Before you go searching through help or cranking up your browser, let Excel do the work for you. Just type =SUMIF in a cell and press Control + Shift + A and Excel will give you the list of data required. It’s dead simple and it saves heaps of time.
Helen Bradley
Labels: arguments, Excel 2010, formula, function, quick find
Categories:Uncategorized
posted by Helen Bradley @ 10:37 am2 Comments links to this post
Wednesday, October 12th, 2011
Often you will want to extract a month or day of the week from an Excel date. This is extraordinarily easy to do using the text function.
To get the name of the day of the week from a date in, for example, cell A1 type this into another cell:
= TEXT(A1,”dddd”)
This will give you the full day name spelled out such as Monday or Tuesday.
If you want a three character name use:
= TEXT(A1,”ddd”)
The same basic formula can be used to get the month of the year from a date. Use this to get the month name spelled out in full:
= TEXT(A1,”mmmm”)
Use this to get the month of the year spelled out in three characters:
= TEXT(A1,”mmm”)
and this for a single letter month:
= TEXT(A1,”mmmmm”)
This formula can be easily constructed and copied down a column of dates to extract just the information you want very quickly and easily.
The Excel help file has some information about the different formats you can use to extract data using the TEXT function.
Helen Bradley
Labels: day of the week from a date, Excel 2007, Excel 2010, functions, month name from a date
Categories:Uncategorized
posted by Helen Bradley @ 9:13 am10 Comments links to this post
