Best Microsoft Excel Bloggers

Monday, February 22, 2010

Creating and Using a Template

Templates


 
Templates are a useful tool that are often forgotten or overlooked. People use them in Word to create invoices and other standardized forms but never think to apply that standardization to their Excel workbooks.

  • If you use custom formats or find yourself applying a standardized formatting to every Excel document that you create (be it fonts, column widths, number formats )then you should consider creating a template.
  • It can also be useful if you are working with others on a project such as a budget and want to ensure that the setup and formatting are all the same. You can create a template and then distribute it.
The easiest way to create a template is to simply create a worksheet with the formatting, column widths etc. that you want to use as the foundation and then save it.

 
  • Create a worksheet and set it up as you want
  • Go to the Office Button
  • Select Save As
  • Click on the drop-down arrow beside Save As Type
  • Select Excel Template or Macro-Enabled Template if your workbook contains macros
  • Enter a name for the template
  • Click Save
 
 
The default is that the template is saved inside the Template Folder. However, you can save it elsewhere.
 ============================================
If you have saved it in the default Template folder, it is extremely easy to retrieve.
Simply click on the Office ButtonSelect New
Select My templates... from the left column
 

 

 

 
 
 
 
 
 
 
 
Select the template you want and click OK
 

 

 

 

 


Since you saved the file as a Template, after you complete work on it and go to save it, Excel will prompt you to name it something else so that the template is not overwritten.





Friday, February 19, 2010

Fixed Decimal Places


Macro Tip for Fixed Decimal

I have this Fixed Decimal Place tip, which is below,  in my Must Know Excel Tips Tricks and Tools for the CPA.Ebook.  Eric Robinson read my tip and sent me his own. He shared a macro he uses to switch back and forth between no decimal place and  decimal places.  Switching back and forth can be tedious if you are, for example, entering check numbers (no decimal) and check amounts (2 decimal places). I have added it below. Thanks so much  Eric.

Sub ToggleAutoDecimal()
'
' ToggleAutoDecimal Macro
' Macro recorded by Eric Robinson to handle fixed decimal places
'
    Application.FixedDecimal = Not (Application.FixedDecimal)
   

End Sub


Fixed Decimal Place

I was teaching an Excel class a few years back and briefly mentioned the Fixed Decimal Place feature. My goodness, half the class was enthralled with it.  It seemed to be the biggest thing they had seen since sliced bread! (Hmm. I guess I need to come up with a more up to date expression particularly since I buy unsliced bread.)

If you are a numbers person then you too may appreciate this feature. If you need to enter columns of numbers, with a fixed number of decimals, into a worksheet then make your job a lot easier by having Excel enter the decimals for you.  For example,  I set the fixed decimal feature to 2 and then in Cell B2, I typed 1234 and Excel displays it as 12.34. It can be a bit of a time saver.







When you turn the feature on, the default is 2 decimal places but you can change it to whatever you want.

=======================================================================

If you are constantly dealing with large numbers and would like them to be truncated to the same decimal place then you can also use this feature.


In the example to the left, I typed original values in Column A, I then turned on the fixed decimal place feature to 2 decimal places and when I typed in the same numbers, Excel changed them.
So, when I typed 500000 into cell B2, Excel changed it to  5000. In Column C, I changed the fixed decimal place feature to 3 and when I typed in 500,000, it changed it to 500.
You get the idea.  So, this may really excite you and you may find it useful however please be aware that Excel now considers cell C2 to be 500. If  I add C2 + 0, the answer I get is 500 not 500,000 even if you have turned the fixed decimal place feature off.

If you don't want every number in your spreadsheet truncated then you need to type the decimal place in yourself.
For example, if the fixed decimal feature is on and set to 2 decimal places, if I type 200.00 in a cell, Excel will change it to 200 however if I type 200 into a cell, Excel will display it as 2.


Here are the steps in case you want to look at it
 Excel 2003
  • Select the Tools menu.
  • Select Options.
  • Select the Edit tab.
  • Select the Fixed decimal Places checkbox.
  • Select the appropriate number of places.
  • Click OK.

Other Versions:

  • Click  File (Excel 2010-2016) or  Office Button (Excel 2007). 
  • Select Options (at the very bottom of the dialog box).
  • Click on Advanced.

  • Put a checkmark in Automatically insert a decimal point and select the number of Places.
  • Click OK..







Have a great day!

Thursday, February 18, 2010

Deleting Blanks

Often when you import data, you end up with a lot of BLANK ROWS.

Here is an easy way to get rid of them.


Excel 2003 method:

  •  Select your data
  • Select Edit and Go To
  • Click Special…
  • Click OK
  • Select Edit
  • Select Delete...
  • Select Entire Row
  • Click OK

Excel 2007 method:

  •  Select your data
  •  Select Find and Select on the Home Tab
  •  Select Go To Special..
  •  Select Blanks
  •  Click OK
  •  Select the drop-down arrow under Delete in the Cells group on the Home Tab
  • Select Delete Entire Row
  • Click OK

You could also right-click and select Delete instead of going to the Ribbon if you preferred.

Tuesday, February 16, 2010

More Duplicates

I realized that Method 3 only showed you how to identify  numbers. If you have a mix of numbers and text, you may find this example more useful.
In this example, I had values in Column A and Column B and I wanted to identify which were duplicates.
I typed the following formula =IF(ISERROR(MATCH(A1,$B$1:$B$5,0))," ",A1) in Column D and then copied it down. This formula tells Excel to Compare Column A against Column B and to identify any values that match. Excel found that there was a match for may in Column B and there was a match for the value 2 in Column B so those are the only values that display in Column D.

If you do anything else with duplicates, let me know.

I would be glad to share it here.

Tuesday, February 9, 2010

Duplicates- Conditional Formatting

Duplicates

In Excel 2007, it is easy to remove duplicates.
  • Select your data
  • Click on the Data tab
  • Click Remove Duplicates
Easy..Peasy.. but sometimes you might actually want to look at what Excel is removing but Excel only tells you how many duplicates were removed - it doesn't show them to you.
 
You can easily identify duplicates and/or unique values using Excel 2007's Conditional Formatting. 
Using the basics of conditional formatting merely formats the duplicate cells. In this case, it formats both the original and any other instances of it.

 

 
You select your data, and then select Conditional Formatting on the Home Tab.
Select Highlight Cell Rules and then select Duplicates.
A dialog box comes up for you to select a fill/highlight color for the duplicates.
 An alternative method:
You can also select New Rule and then select Format only unique or duplicate values which is an alternative way to get to the same place.
There is actually a lot of 'stuff" on duplicates as it is a problem everyone faces so perhaps I will continue this conversation tomorrow.

Ms. Excel- Resident Excel Geek