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

Thu
Nov 17
2011

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


Answer

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
Fri
May 7
2010

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


Answer

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
="11-"&"4"
or even like this
="11-12"


VBAXLMan

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

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?


Answer

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

VBAXLMan

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

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 .


Answer

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

Good luck

VBAXLMan

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

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??

thanks


Answer

I can't see a way to go over that

Sorry!

VBAXLMan

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

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.


Answer

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


VBAXLMan

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

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?


Answer

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

Forget it

Sorry!

VBAXLMan

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

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..


Answer

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

VBAXLMan

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

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.,


Answer

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

http://MrExcel.com
http://www.File1.net

VBAXLMan

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

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.


Answer

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



VBAXLMan

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

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?

Thanks


Answer

Yes
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
Mon
Apr 6
2009

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


Answer

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

VBAXLMan

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

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?


Answer

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

VBAXLMan

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

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 diddlesjnr@adam.com.au


Answer

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
Thu
Jan 18
2007

How do you conditional format a hyperlink in excel?

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


Answer

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
Tue
Dec 19
2006

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.


Answer

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
Sat
Apr 22
2006

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?


Answer

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
Fri
Apr 7
2006

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) johnsonokunade@gmail.com and (2) firstjos@yahoo.com

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


Answer

Try Download.com 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. http://www.download.com

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

Tags

History