ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

18 Q & A posts tagged with format

Nov 17

Thinner bars than default settings in excel?

I have selected the thinnest bar available in Excel. Are there ways to download a custom width that is slightly thinner that would be noticeable on a print out? I have to do some graphing and the thinnest bars are still slightly too thick.. I dont want to use dashed lines either


As far as I understood you
You can modify the width of these bars by going Right click then selecting Format
These options can do that for you

If not, then try to change the type into 3D, then you can modify gaps too

good luck

Thursday 11/17/2011 9:28:23 PM
May 7

How do I stop excel auto formatting cells into dates when I restart excel?

In other words I don't always want to have to go and change my settings back to what I want


That is one of Excel big bugs
Once you enter 1-4 or 11-12, Excel converts it into date

To go over that, you need to do one of the following:
1- Insert as many spaces as you can, between/after/before your number
2- Insert the single quote ' as the first character in that cell
3- Add some text before/after/between the numbers, may be like this G1-4
4- do a formula that generates exact number, like this
or even like this


Friday 5/7/2010 12:53:58 PM
May 5

Google spreadsheet format: how to make it look like a questionnaire and not like an excel file?

i wrote a spreadsheet on google which looked like a questionnaire (with options, multiple choice etc.) but when i saved it the original format disappeared and instead i got what looks like an excel file. How can i switch to the original format without re-writing it?


When you say "saved it" you meant export it to your desktop, right?

If so, then try to save it as another format, may be OpenOffice Calc, or any other format
Google was meant to be online and accessed from other locations

If not, then you need to report this as a bug to Google


Wednesday 5/5/2010 5:22:15 AM
Apr 18

Does anyone knows the command to put the data from excel into the R programme?

Does anyone knows the command to put the data from Excel into the R programme ( R programme is a statistical programme) I will be really thankful .


You need to export the data you have into CSV file format
Most statistical and database applications can read CSV formats

Good luck


Sunday 4/18/2010 10:24:46 PM
Apr 3

Excel Data Bars. How to change its orientation?

I want to use Conditional Formatting > Data Bars in excel 2007.
The challenging part is that I want the Data Bar to grow from Right to Left in the cell.
By default in excel they go from Left to Right in the cell and I can not find a way to change it.

Is it possible to change the the orientation? If so, How to??



I can't see a way to go over that



Saturday 4/3/2010 1:34:04 PM
Apr 26

In Microsoft Excel, how come when i click the arrow next to "Fill Color", It doesnt show More Fill Colors?

It just shows like 10 colors that are dumb, and says above it no fill.
I want to put a non-retarded color.


Excel cells can not be colorful as you expect them

It has been known that the colors in Excel cells are restricted to some number

In Excel2007 though, there are much more colors

Try to do CTRL+1 to open the Format > Cells dialog to get more fill color options


Sunday 4/26/2009 12:28:40 PM
Apr 18

Can you format an Excel cell with a formula and without using a macro or the Conditional Format menu?

Can you enter a formula directly into a cell to change the format of the cell, mainly the font color or the background shade color?


No formula there to change the format of another cell (If not macros nor Validation)

Forget it



Saturday 4/18/2009 12:52:04 AM
Apr 13

Is it posible to change the colour of a cell by using functions in MS-Excel ?

By Using functions in Ms Excel can we change the colour in a cell.
for example: if A1=1 then RED colour, A2=2, Then YELLOW colour..


In Excel2003, go to
- Select the cell
- Format > Conditional formatting
- Enter the values and the format for each condition (maximum 3 conditions)
- Press Ok

In Excel2007
- Home > Conditional Formatting > Highlight cells rules > More rules
- Select the values and the format for each
- You can do up to 256 conditions

Enjoy it


Monday 4/13/2009 3:04:36 AM
Apr 9

I want to know the Ms-excel formulas. to use excel.?

I want to know different formulas in excel like vlookup,hlookup,pivot table,conditioning formats & links ect.,


There are a lot of websites that can help you
try these


Thursday 4/9/2009 12:16:34 AM
Apr 7

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1

Enjoy it


Tuesday 4/7/2009 9:01:21 AM
Apr 7

Is There A Function On Microsoft Excel Which Allows Me To Highlight Duplicate Records?

What I need to do, is to paste 2 different corporate structures into one spreadhseet and then delete the duplicates. Is there a function on microsoft excel which ahighlights any duplicate records in column A of excel?



If you are using Excel2007, the Conditional Formatting in the Home tab has duplicates, go to
Home > Conditional Formatting > Highlight cell rules > Duplicate values

If you are in Excel2003, then you may need to use a function plus the conditional formatting like this
Use the COUNTIF function in an empty column to show 1 if duplicated, 0 if not like this
=IF( COUNTIF( A:A, A1)>0,1,0)
Then apply conditional formatting to that column to color if the cell value equals 1

Enjoy it

VBAXLMan is here to fill your Excel needs

Tuesday 4/7/2009 5:22:21 AM
Apr 6

I am subtracting 2 cells i a excel sheet. Keep getting wrong answer in the same cell off by .01 cent format ok?

Example answer should be $10.20, answer I keep getting is $10.19. All my formatting is o k, all formulas are o k, can only get right answer when I format to 1 Decimal place instead of 2


The number that Excel shows in a cell is not all the time the same number that is calculated
for example
If you have 10.48 in a cell, and formatted that cell with 1 decimal place, then it shows as 10.5
However the number is taken into calculation is not 10.5, but it is 10.48

That may be what you are facing in your example

You can do that:
1- Go to Tools > Options (In Excel2003)
Logo > Excel options (in Excel2007) and search for the setting that says "Precision as displayed"
This will make Excel treat every number (in the current worksheet) as it is displayed, and by then you will see correct values


Monday 4/6/2009 2:33:01 PM
Apr 4

How to solve this problem with Excel conditional formatting?

I have a table on an Excel spreadsheet, which table contains only numbers. I want to color a "path" in this table using the following algorithm:

1) Make current the upper left cell and color it.

2) Examine the cell to the right of the current one and the cell beneath the current one. Make current the one that contains the smaller of the two numbers and color it. If the two numbers are equal, make current and color the cell beneath the current one.

3) Repeat 2) until the bottom or the right edge of the table is reached, whichever occurs first.

Can this be done using only the means of Excel's conditional formatting (i.e., no VBA) and how? That is, what formulas for conditional formatting to use?


Conditional formatting will "FORMAT" the cell and not change the cell content

To change cell content (or value) you will need a function

Based on your request, IF function would do the trick for you
Now, if your first cell is A1, then paste this into it
=IF( OR(A2="",B1=""),"", A2<B1, A1, IF( A2>B1, B1, A2))

Then fill cell A1 down

Now, Since the Conditional Formatting is to "FORMAT" cells, I don't see where you need it here

let me know


Saturday 4/4/2009 11:14:45 AM
Jan 20

Why does a formula remain visible in a cell on an excel worksheet?

I have many formulas in my excel spreadsheet and when I want to extend the search a few more rows, and change the number in the formula bar, the formula appears and then remains visible in the cell. Please email me if you have a solution on


Perhaps you have the format for that cell as text
Excel does that if you:
1- Have no equal "=" sign as the FIRST character
2- Format that cell as text

VBAXLMan is back

Tuesday 1/20/2009 8:09:30 PM
Jan 18

How do you conditional format a hyperlink in excel?

trying to hide links where data doesnt exist yet with white font


You will need to use HYPERLINK function, (Not Insert Hyperlink)
along with IF function.
It is somthing like that
=IF( A1=1, "", HYPERLINK( "destination"))
So when cell A1 equals 1, then this cell will show nothing, but when A1 equals anything but 1, this cell will show the hyperlink you specify

Enjoy my profile, I am the VBAXLMan

Thursday 1/18/2007 7:59:31 AM
Dec 19

Excel Spreadsheet opens up blank?

Coworker wanted me to double check her work and when I open the spreadsheet it comes up blank (just gray) not even showing the columns and rows. But looking at full screen I can see it. All other excel spreadsheets are fine.


There are alot of tricks in Excel to do this, I just need you to check these one after another
1- Go to (Window > Unhide) and see if there are items shown in the dialog that appears
2- Go to (Tools > Options > Window options) and make sure you select (Row and column headers, gridlines, horizontal scroll bar, vertical scroll bar)
3- got to (Format > Sheet > Unhide) and check the hidden sheets

PS: If excel asked for password, then the file is protected, you will need it

Enjoy my profile, I am the VBAXLMan

Tuesday 12/19/2006 2:56:20 PM
Apr 22

Getting xp boot cd to format?

i am having problems with my XP cd.. half way through the setup the storm turned my comp off.. now i cant boot up using my cd because it was a copied version and i have to be logged in to be able to install it.. is there a way i could use a install cd that booted up and installed. i already have my bootlist having cd boot frist... anything i can download?


If you are formatting, you don't need to log in.
All you need is to boot from CD and - following instructions of WinXP setup - install new windows.

But If you are upgrading, installing WinXP from WinXP, I am afraid you need to format it.

There are alot of tools to download, but all will not fix your program, they will recommand you to format.

Good luck

Saturday 4/22/2006 1:08:40 PM
Apr 7

Free Softwarre to Restore my Notebook Hardisk?

Hello please help if u can
My Notebook was stolen and recently found by the Police Authorities - YES the system was ok but all the hard disk contents had been formatted.

Now - I do a lot of Database development on DBF which I had planned to convert to Alpha 5 - but the way it is now i do not know how to go about it considering the fact that all my data are locked up in the formatted hard Disk

Can anyone give me a software that will enable me unformat my hard disk? I have tried on the NET but all the ones i got could not handle 80GB on NTFS - and besides - they all asked for money

Please if u have a software that works - do me a kind favour and forward same to this email address - I will show my appreciation please

My email addresses are (1) and (2)

In addition - i am available on Yahoo Messenger for chat on firstjos - thanks a lot


Try to download FinalData2.
This one is powerfull, I used it myself to recover my 160GB NTFS partitions and files.

I will send you the key by mail if you want

Belive me it worth every penny.

Friday 4/7/2006 2:17:34 AM