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

11 Q & A posts tagged with Options

May 8

How to create a fixed x-axis in Excel 2007 charts?

I need to make an Excel 2007 line chart with a fixed x-axis that starts at 0 with tic marks every 10 values up to 100. My data values fall within this range, but I don't want the data points to determine the x-axis. Can someone tell me how to do this?


Right click on that X-axis, and click "Format Axis"
Then, make sure you check the options that said "Fixed", instead of "Auto"

Doing that will make it fixed to the specified values, even when the line goes out


Saturday 5/8/2010 7:10:13 PM
May 2

Excel help? 10 points?…

1. Add the numbers in the Student Number column as labels. (Example: '1203)

What does this question mean and how would I add the labels?


Labels by default are enabled, means you can use them right away
however, labels are usually the first column (or first row) of a table

It is confusing that you want to ADD labels
may be you need to enable them, if they are disabled
Go to Excel Options > General tab
And search for the "Use labels in formulas" check box, make sure it is clicked

After you enable them, you can use them in formula, just like
=SUM(Student Number)
to sum all the numbers

let me know if it worked or not


Sunday 5/2/2010 5:33:27 PM
Apr 26

How to made a 'formulae spreadsheet'.?

Does anyone know how to print a excel (2007) document the displays the formulas used to create the numbers.


First you need to show the formulas
- Click the OFfice Logo at the top left corner
- Excel Options > Advanced
- In the "Display Options for this worksheet" select "Show formulas in cells instead of thier calculated results"
- Ok
Now you can print it with CTRL+P as usual

Good lock


Sunday 4/26/2009 4:03:30 AM
Apr 25

I am using Microsoft Excel 2003.?

I am wanting to make a list of words so that by typing in the first letter it will automatically fill in the whole word. I do not want to have to type the list of words first (there are alot of them!!), but for this to be a setting which i can apply to different sheets. I do not want the validation option of a drop down list (there are just too many words i need to auto fill) Is this possible?
Please help! thank you!


Options 1:
If you have these words in one column, once you try to enter a word, Excel will automatically list the words that starts with the same letter

Option 2:
If you have all these words listed in a column also, right click on the empty cell (below it) and select "Pick from list"

Option 3:
Data Validate (which you already refused to do) but, you can make it Auto-updated, so when you add a word to your list, it will automatically be in the populated list…

Option 4:
Use some macros to do that

Option 4 requires some work and sample files, reply me here if you are interested


Saturday 4/25/2009 7:05:09 AM
Apr 25

How can i enter my country date in excel instead of english date?


You need to set the date in Control Panel to your country code to do that

Control Panel > Regional Options

Once you do this, it is automatically changed in Excel to your way


Saturday 4/25/2009 4:46:46 AM
Apr 23

I need an MS Excel Formula that generates the total time taken from a list of times.?


I have a list of times (clock times) and I need to get the total time (duration).

5:30 am
5:45 am
6:15 am
6:30 am

The total duration time spent should be 1 hour and 15 mins or 75min.

Can I get an excel formula that does the above?


I am not sure if I got you correct here, but let me try this...

Option 1:
Now, if you have that list starting from cell A1 down
Paste this in B1
=MINUTE(A1)+HOUR( A1)*60&"min"
Then paste this down to get the others

That is one possibility answer to your question

Option 2:
Another one is this
=HOUR( A1)&" hours and "&minute(A1)&" min"

Option 3:
Or this

Let me know


Thursday 4/23/2009 11:41:34 PM
Apr 22

Ms excel help.. .. finding the coefficient of correlation, r^2?

ok so i need help finding the r^2 of linear and nonlinear regressions, to compare which ones fit better......... using excel.

eg. if power reg. curve is 0.998 and quad. curve is 0.992 ... then id choose power reg,

i know how to do it on a graphing calculator... but i need it for an assignment......

so how do i find it for a curve?

p.s. ive got excel 2003.....


Once you got a graph
- Right click on the line
- Select "Add Trendline"
- Select the type of that trendline
- Go to "Options" tab
- Click on "Show R2 equation"

That is all


Wednesday 4/22/2009 6:17:33 PM
Apr 21

How to disable save as option in excel & Word?

How to disable save as option in excel & Word, to protect the file from copying. But the Respective person should have the option available.


In Excel 2003, put this VBA code in "Workbook" object after you press ALT+F11
Private Sub Workbook_Open()
End Sub

Then Add "Module" and paste this in
Sub disblesaveas()
CommandBars("File").Controls("Save As...").Enabled = False
End Sub

Then close the file and open it again

If you have Excel 2007, (You can also do this in Excel2003) do this
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then Cancel = True
End Sub

Good luck


Tuesday 4/21/2009 8:50:19 AM
Jan 21

In the new excel (2007) when you type in a cell, it comes up like normal text (so you can highlight it etc)...?

but I want it like the old version, so that you cant highlight it etc. apart from in the formula bar, does anyone know how to do that thanks


Go to Excel Options by clicking the Office logo (at the top left) and press "Excel Options"

Now, in Advanced > Edit Options
Make sure the checkbox "Allow edit directly in cell" is unchecked

Enjoy my profile, VBAXLMan is back

Wednesday 1/21/2009 2:49:20 PM
Jan 21

Excel 2007 document protection & locked cells?


I've created an excel document that numerous users will be editing, however I need for only certain users to be able to edit certain cells.

I currently am using the track changes method, however it appears that there are ways around this.

Does anybody know if there is a formula or a function I can use to only allow a user to edit a certain cell if the username = joe.bloggs for example? Excel can clearly look up the username as it does so in the "track changes" but its how do i use this information to protect my cells?

Many Thanks,


The Standard way is to give the password to modify the file (Save As > Tools > General Options) to those people only allowing them to edit these cells, while others can not

The other way is to lock these cells with password (Review > Protect Sheet) and give those the password

In addition to that you will always have the macro way, but that requires more info from you

Enjoy my profile, I am the VBAXLMan here

Wednesday 1/21/2009 6:55:16 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