Tuesday, January 10th, 2012

Pattern fills for your Excel 2007 charts

Excel2007 black and white pattern fills for charts Pattern fills for your Excel 2007 charts

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

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.

Excel black and white pattern fills for charts Pattern fills for your Excel 2007 charts

Labels: , , , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 9:55 am1 Comment links to this post

Wednesday, October 12th, 2011

Excel – get the day or month name from a date

text function in Excel 1 Excel get the day or month name from a date

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.

text function in Excel Excel get the day or month name from a date

Labels: , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 9:13 am1 Comment links to this post

Thursday, October 6th, 2011

Switch between open documents in Excel and Word

Switch between windows excel word Switch between open documents in Excel and Word

Hmmm … I am fussy, I want my cake and I want to eat it too!

I want to have a clean task bar so I don’t want to see lots of files lined up there so I love Windows 7 and its clean task bar. But I find the new panel that opens when I right click an icon on the task bar to be just a little bit too free with information. I really want it to show me a list of currently open files – not everything that I have open or have recently opened. Actually I could live with the information it gives me if I didn’t have to actually use it to switch windows.

So, problem is… how can I switch between open documents in Excel or Word, for example, without having to use the Windows task bar? Solution is to use the Switch Windows button. I add it to the QAT (Quick Access Toolbar) and it totally makes sense to me.

In Excel or Word, click the Customize Quick Access Toolbar button and choose More Commands. From the list which currently shows Popular Commands choose All Commands and scroll to find the Switch Windows button and click Add.

Now it is on the QAT and it will show you all your open files and you can use it to switch between them by just clicking on the one to go to. Repeat the process for both Excel and Word and you’ll be happy – at least until something else bugs you!

Labels: , , , , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 8:58 amNo Comments links to this post

Monday, October 3rd, 2011

Go To a cell in a formula in Excel

Excel go to using Trace precedents Go To a cell in a formula in Excel
Consider this scenario – you have a cell which contains a link to data in another cell on another sheet. The link might be the only thing in the cell or it might be a link in a formula which contains references to data in lots of other cells too.

If you want to go to a particular referenced cell you could read off the cell details – its sheet name and its cell reference and navigate there yourself or you could get smart and have Excel do the work.

To do this, click in the cell containing the reference and choose Formulas > Trace Precedents. When you do this you will see a small sheet icon and an arrow with a black arrow head pointing at the cell. Hold your mouse cursor over the arrow until the mouse cursor turns into a hollow white arrow. Double click and the Go To dialog will open. In it will be references to all the cells in the formula. Click the reference you are interested in going to and the cell reference will be highlighted – click Ok and Excel will take you direct to that cell.

If you have both workbooks open the same process will work to take you to a cell in another workbook if it is referred to in a formula in the current workbook.

Labels: , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 9:45 amNo Comments links to this post

Sunday, June 5th, 2011

Developer Tab in Excel 2010

Display Developer tab on ribbon Excel 20101 Developer Tab in Excel 2010Some things Microsoft does make no sense at all. For this read showing the Developer Tab in Excel 2010. Ok first of all why hide the damn thing. Second of all why change how it is displayed from Excel 2007 to 2010 – yep they did – and yep it makes NO sense to do so.

The Developer tab contains some sweet goodies like form tools which let you put a button on a worksheet to run a macro – but you won’t know you can do this till you show the Developer tab.

Ok… here’s how: Click the File button and click Customize Ribbon. In the left panel is the Developer toolbar but its checkbox is deselected. Click to check it and Voila! you now have a Developer tab.

In Excel 2007, skip; the fuss – there is no Customize option and the option to display the Developer toolbar is in the first panel you see in the Excel Options dialog.

Display Developer tab on ribbon Excel 2007 Developer Tab in Excel 2010

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 7:54 pm1 Comment links to this post

Friday, April 29th, 2011

Remove everything from a cell in Excel 2007/2010

Clear contents and formatting from Excel cell Remove everything from a cell in Excel 2007/2010

In Excel 2003 and earlier you might recall that you could click a cell and choose Edit > Clear and choose to clear its Contents, Comments, Formatting or choose All to remove everything.

In Excel 2007/2010 there is a Clear Contents option on the right click menu but, in the absence of the Edit menu you’ll need to look elsewhere for the other options. On the Home tab of the Ribbon look for the Clear icon – it has an eraser on it and it has a dropdown list from which you can select the desired option.

It’s pretty obvious if you’re using Excel at full screen size but shrunk down it isn’t clear (pun intended) that it is there or what it does. I like to add the Clear All option to the Quick Access Toolbar so it’s easy to find and use. You will find it in the All Commands list and it is called Clear All if you’re looking for it.

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 9:05 amNo Comments links to this post

Wednesday, June 16th, 2010

Problems with Excel formulas?

excel evaluate formula Problems with Excel formulas?If you are having difficulty understanding how a formula is calculating in Excel – perhaps because it appears to give you the wrong results - you can step through it to see how it is working.

To do this, select the cell containing the formula and choose Tools > Formula Auditing > Evaluate Formula – in Excel 2007 find the Evaluate Formula option on the Formulas tab.

Click Evaluate and each time you do this, a portion of the formula will be evaluated and you can see it at work.

Use the Step In and Step Out options to see the actual values in place of any appropriate cell references.

This step by step processing should show you what is happening in your formula allowing you to troubleshoot any difficulties with it.

Labels: , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 7:04 am2 Comments links to this post

Thursday, November 5th, 2009

Excel 2007- In Cell Dropdown List

list 750253 Excel 2007 In Cell Dropdown List
When you need to enter data from a small subset of entries into a range in Excel 2007 you can do it more easily using a custom designed dropdown list.

To configure a dropdown list in a cell type the list of items to use in a single column in a spare sheet in the workbook.

Select these cells and choose the Formulas tab Define Name, type DataForList as the name in the dialog, set the scope to Workbook and click Ok.

Switch to the sheet where you want to add your dropdown list to some cells, select the cells that should display a list of data to choose from and choose Data tab > Data Validation > Settings tab.

From the Allow: dropdown list choose List and, in the Source area, type =DataForList and click Ok.

Now, whenever you click a cell in this range you’ll see a dropdown list appear from which you can choose a list entry for that cell.

If you’re using Excel 2003, here is a link to an earlier post explaining how to do this in Excel 2003:

Automatic cell entries in Excel 2003
http://www.projectwoman.com/labels/validation.html

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Labels: , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 8:18 pmNo Comments links to this post

Friday, October 23rd, 2009

Excel 2007 – Check for Duplicates

excel duplicates 733472 Excel 2007 Check for Duplicates
The new Excel 2007 has far superior tools for finding and removing duplicate entries in a list. Thankfully – because this has been a nightmare in earlier versions.

To find and remove duplicates from a list of data in Excel 2007 first format the area as a table by selecting it and, from the Home tab, choose Format as Table.

Click on any cell in the table and choose Table Tools > Design tab on the Ribbon.

Select Remove Duplicates to display the Remove Duplicates dialog. In this dialog are the Column headings for your data and all are selected by default. To remove the duplicate data from your worksheet leave all the column headings selected and click Ok.

If you want to remove rows where only certain data matches, leave the column headings for those particular rows selected and deselect the column headings for those columns which may have data that differs from one row to another. Now click Ok.

It is sensible to save your worksheet before running this Remove Duplicates option just in case you delete data by accident. If this happens and if you haven’t closed the file, you can recover it using the Undo button.

If you are using an earlier version of Excel, here are links to earlier relevant posts:
Excel – finding duplicates
http://www.projectwoman.com/2007/02/excel-finding-duplicates.html
Check for duplicates in an Excel list
http://www.projectwoman.com/2007/01/check-for-duplicates-in-excel-list-1.html

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Labels: , , ,

Categories:Uncategorized

posted by Helen Bradley @ 6:59 pmNo Comments links to this post

Wednesday, July 29th, 2009

Excel macro – Format By Contents

You can do so much with Excel macros – they can be so powerful.

Here is a macro that formats a cell depending on its contents when you type something in it.

If you type a number, or a formula that returns a number, it is formatted one way, if you type a date it is formatted another way and if you type a word it is formatted a different way.

The macro uses the OnEntry event which fires whenever something is entered into a cell. If you attach the macro to an Auto_Open macro you’ll ensure it is run whenever the workbook is opened.

To create the macro, choose Tools > Macro > Visual Basic Editor and, choose Insert > Module to add a module to the current worksheet. Type the code into the dialog.

Sub Auto_Open()
ActiveSheet.OnEntry = “formatCell”
End Sub

Sub formatCell()
If IsNumeric(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 46
ElseIf IsDate(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 10
ActiveCell.Font.ColorIndex = 50
Else
ActiveCell.Font.Name = “Times New Roman”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 5
End If
End Sub

Sub Auto_Close()
ActiveSheet.OnEntry = “”
End Sub

Back in Excel choose Tools > Macro > Auto_Open to run the macro the first time to test it. Provided you have Excel configured to run macros, it will run automatically every time you open the workbook in future.

To learn more about Auto_open, AutoOpen and other fun macro naming conventions in VBA, visit this blog post:

What’s in a name? Auto_Open or AutoOpen What’s in a name? Auto_Open or AutoOpen
http://www.projectwoman.com/labels/Auto_Open.html

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Labels: , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 7:12 pmNo Comments links to this post

Page 1 of 512345