## 34 Q & A posts found in May

### How do you change the what order lines go on a graph in excel?

I have an excel graph and i want to move one line in front of the other. How do I do that?

### Answer

In Excel 2007

- Select the chart

- Go to Design tab > Select Data > then select the series you want to move and click on the up and down arrow in that window to move it up or down

In Excel2003

- Select the Chart

- Go to Chart > Source Data

- In Series tab, move it up and down as required

If you are talking about the 3D chart you have, then that can be made the same way since Excel is using that order to draw the lines

VBAXLMan

### Cluess! I need help getting my barcode scanner Qw2500 to work.?

I have the QS2500 RS-232 scanner. It seems to be working, lighting up, beeping, but it is not working in excel, or inflow program. Help!!! thanks

### Answer

Try it on another port

Try it on another computer

Try it on another OS

Reinstall its own software (from manufacturer website)

After doing all that, you can tell if the problem is in the scanner itself or in the software

VBAXLMan

### Is there a way to send batch emails from a list in Excel through Outlook at different times?

I am curious to know if there is such code or function that can send batch emails from a list in Excel with a personalized message through Outlook for delivery at different times.

### Answer

This is the file that does that

maybe except for the "delivery time" thing

http://www.vbaxlman.com/Files/?ID=4S374y…

You can customized it if you like

VBAXLMan

### Why do i see the lock icons on my ms office files (excel)? How do i get rid of them?

I've a Windows 7 machine.

### Answer

It is not Office issue, it is a Windows issue

You have that drive encrypted and that is why your files have lock on them

Means you cannot access them if you are not the owner

or something like that

VBAXLMan

### Can we use excel worksheet to evaluate a multiple choice question answer sheet?

for eg: to a question, 1) Prime Minister of UK is

a) David Cameron b) Nick Klegg c) Gordon Brown d) None of the above.

how to use logical formulas in EXCEL for candidates answering as a/b/c/d and to assign 1 mark for correct answer and 0 for incorrect

### Answer

You need to put the answers in one row or one column

Then use the COUNTIF formula to calculate how many A's there

Something like this:

=COUNTIF(3:3,"A")

Assuming your answers a,b,c,d are wherever in row 3

VBAXLMan

### Excel question currency question?

Basically what i want to do is take colum m multiply it by 1.42 and show the results in colum N in the cell ajoining the original amount this make sence?

### Answer

If you have the first value in cell M3

then in Cell N3, paste this

=M3*1.42

Then copy N3 down until the end

VBAXLMan

### How do I enter data in Excel that will update in multiple worksheet?

In Excel 2003, I'm trying to create 2 different reports based on the same set of data.

How do I set it up so that for example, in worksheet one, I enter data, but the data will update in the same fields in other worksheets.

### Answer

Also try to copy then Paste Special > As Link

VBAXLMan

### How would I automatically sort the selected cell in excel 2007.?

First in aphabetical order by office location and then by asscending order. What are the steps that this can be done.

### Answer

There is a file that sort any table automatically using formulas

Once you have it, you can customize the sort easily

where is it? where is it?

Ah, here it is

http://www.vbaxlman.com/Files/?ID=HlCOfq…

VBAXLMan

### Excel spreadsheet formulas?

If I was to make an monthly budget with excel, how would I use 4 different formulas for calculating total expenses, income, etc. I am confused on how to get different answers using different formulas>?

### Answer

You need to do those 4 formulas in 4 different cells

If this is what I understood from your question

otherwise, you might need to explain more

VBAXLMan years of Excel experience

### In Microsoft Office Excel, how do I get the sheet tabs to show at the bottom?

### Answer

That was for Excel2003 and earlier

For Excel2007, do this

- Click on the Office Logo at the top left corner

- click on Options

- Go to "Advanced"

- Scroll down until you reach "Display options for this workbook"

- Make sure that you have the "Show sheet tabs" is checked

VBAXLMan

### What is the formula in Excel for a check box when clicked gives time and date in another cell?

I apologize if I am not explaining myself right.

I am working on a spreadsheet for tracking the work that's been done. I would like to use check boxes in a column and on another column for the current date and time when the box in a cell is selected. What is the formula and do you have an example to help me out, please?

For example the check box are in column V and I want the current time to be in the W column.

Thank you so much for helping!!!!

### Answer

I can see what you are trying to do

The problem is that you can not (easily) do multiple checkboxes in a column and connect them to corresponding cells

I recommend you use the below shortcut keys:

CTRL + ;

To insert current date as constant

CTRL + Shift + ;

To insert current time in the selected cell as constant

This way, you will not have an updated version of Date or Time, means once you insert them ,they will not change.

VBAXLMan

### In Excel 2007, how do I make specific number values correspond to specific text values?

I'm trying to create a template for entering social science questionnaires, and SPSS has spoiled me. When I start entering the data, I want to able to type numbers and then have the correct text fill the box. For example, one question concerns race. I want to be able to type "1" and have "caucasian" automatically appear in the box. However, I want to RETAIN the number values so that I can easily run statistical analyses. Can Excel do this? Someone please help!

### Answer

Yes, it can

This called VLOOKUP function, it is very popular and widely used

Here is a lesson on how to do that

http://www.vbaxlman.com/Lessons/?ID=b1vi…

VBAXLMan

### My excel is not working on my 2003 professional edition of microsoft. How do I get it to work again?

I just got rid of a virus and tried to upload microsoft updates. Now excel doesn't work. Every time I try to open it, it tells me that it is installing the new version. Then it says the network is unavailable and something about PRO11.MSI.

### Answer

It is obvious, you will need to reinstall Office2003 again

You need to have the CD for it, the same original CD you installed it the first time, to do Installation repair.

When you insert the CD, you will have several options one of them is Reinstall Office to fix some errors.

VBAXLMan

### How can you make an excel workbook, with about 1500 formulas that total from 3 other workbooks?

The auto fill does not work because it is pulling in information from 3 separate workbooks. Is there a quicker way then building all the formulas separately.

### Answer

It looks like you have custom external links,

something like this

http://www.vbaxlman.com/Lessons/?ID=fjam…

This lesson will show you how to get values from several sheets

And also this one to show you how to generate external links

http://www.vbaxlman.com/Lessons/?ID=pbkw…

VBAXLMan

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

### Answer

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

VBAXLMan

### How do I get my PDA to scan bar codes into pocket excel while I'm scanning into another program?

I use a Dell Axim x5 with a scanner attached to scan the bar codes of books in order to find out their prices on the net. I would like to get a listing of each bar code scanned in pocket excel without having to scan each bar code twice.

### Answer

Usually scan bar devices got the scanned bar info into one application

So you need to have any book scanned twice in order to make it in both applications

If you want it to read the bars into Excel only, then just open Excel, and start read the books

You might need to press enter after each read

VBAXLMan

### How to send email to multiple people from an excel file?

I have a an excel file with over 50 email ID's and I want to email them all together. Is there any way of doing it with out adding them induvidally?

### Answer

Here is a free file

http://www.vbaxlman.com/Files/?ID=4S374y…

You will need an email client (like Outlook) already installed to do that

Enjoy it

VBAXLMan

### How can I insert intervals of numbers, not discreet elements into Excel?

I need to sketch the sin, cos functions' graph in Excel. I have a problem with a domain. I don't want to manually type myriad values - even then, the graph is sharp, not curved, and inaccurate. Is there a possibility that I simply set an interval, infinite, but bounded set, as a domain? If yes, how do you do it?

### Answer

You need as many points as you can to do the exact curved sin

to answer your question, do the following:

- Do the first number you want in a cell, say you do 5 in cell E4

- Now do the second number with the intervals into the second say.

So if you want to have the numbers as 5, 15, 25 (with 10 interval), put 15 in cell E5

- Now select both cells

- Drag the selection from the black square at the right bottom corner down to whatever range you want.

Or, you can also do:

- Put 5 in cell E4

- Select some range of cells including cell E4 (Cells E4 to E20)

- Go to:

Edit > Series > Fill (if you have Excel2003 or earlier)

Home > Fill > Series (below SUM sign)

And select the interval and stop value

VBAXLMan

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

### How do you label extra information on Excel line graphs?

So if I already made a line graph and then the instructions say that I need to label the point at which certain things happened in time along the line, how do I do that on Excel. (without doing it by hand)

Thanks :)

### Answer

What do you mean, "by hand" ?

You can do the following:

Show labels for all data points

Select the point you want to label

Click on it again (not double click), just click on it again after few seconds

Now you can modify that point's label

VBAXLMan

### How do I generate small groups from a large Excel file?

I have a large Excel file (~22,000 entries). These entries span across multiple columns (e.g. name, address, phone number, etc.). I would like to break this down into groups of 5, 10, or 20.

### Answer

Use Filter (Auto Filter, or Advanced filter)

If this will not do it for you, then you might need to consider Subtotal

If both didn't do it, a macro will sure do

mail me here some details and will be glad to do it for you

VBAXLMan

### How do I transfer a schedule from Excel to Outlook?

I have schedules that I need to transfer from Microsoft Excel to Outlook. Is there way to do it without typing everything in? Is there a way I can just put it in all at once?

### Answer

Did you try the Import from Outlook?

It should do it

Here is a tip

Do an export to Excel file for your already existence schedule in outlook, then go modyfy that file (or create one like it) then import it to outlook

Hope this will help

VBAXLMan

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

### I can not type arabic in MS word 2003?

I can't type arabic in word 2003, I can type in excel and other programs but in word I have to press each space bar to type the arabic.

### Answer

Word has two sets of keyboard keys to type (vs one set in all other applications)

I guess you already know that

Right CTRL+Shift will make it Arabic on most applications, in Word will make it write Right-to-Left

Instead, use

Right ALT+Shift to type in Arabic, even in the same direction.

If this is not the case, then I didn't understand the question well

VBAXLMan

### How would I create the type of graph shown in the link below?

I have data in excel for the daily minimum and maximum temperatures and daily rainfall for a full year.

Also, what is the name of this style of graph?

### Answer

Use the regular column chart

It is called Histogram, but Excel will not recognize that name, use Column chart

Assuming you have the data listed by date.

VBAXLMan

### How to import into MS Word ,data from two worksheets from Workbook of MS Excel file ?

I have MS-excel Data with a customer sheet with address details on one sheet & Items bought by a customer on other sheet,by using mail merge I am able to generate a letter for each customer by using customer sheet,I want to include details of items bought(from that items sheet).

### Answer

I suggest you having a third worksheet that list the customers and their buying details

Then mail merge it

You might need to use formulas to do so, like VLOOKUP, MATCH, INDEX, IF, COUNTA, etc

use the customerID to grap these info

VBAXLMan

### How to make connections in excel.?

I want to make a list on Excel, it is a list of money i use. I want to write a number (Expense) and press Enter and then get another number (Remainings). For example: I have 1000 in one square, then i write 600 in another one this is money i have used, and then I want Excel to calculate for me and write 400 in the last and third square! Does anybody understand this and know how to do it?

### Answer

Yes sure

You need to do the following:

1- Put the first amount (1000 in your case) in cell D2

2- In D3, paste this

=D2-C3

3- Now, put 600 in C3

From now on, put any other amount in column C starting from C3

So when you do an amount in C4, you just need to copy cell D3 to D4 and so on

Meaning anytime you add another amount, you just need to copy the corresponding cell in column D from the cell above

Does that make sense?

Let me know

VBAXLMan

### Need help putting quotes around my text in excel spreadsheet?

Hi,

I have an excel spreadsheet with different words in all the cells. I need to put quotes around each word. I have tried using a macro but I am new to it and so far have not been able to get it to not just paste the exact same word I used to record the macro. Every cell is a different word. I need the macro to ignore the word in the cell and just put quotes around each individual word. Can anyone help with this??? I appreciate it!

### Answer

The char code for the quotes is 34

Meaning, if you use Chr(34) in VBA code

or

=CHAR(34) in Excel formulas, you will get the quote

Maybe something like this, in formula bar

=CHAR(34)&C1&CHAR(34)

or

Range("A1").value= Chr(34) & Range("A1").value & Chr(34)

Hope this will help

VBAXLMan

### Any microsoft excel experts here? need excel formula please help?

i know i asked this before but i never got a formula and i am still looking

i am looking for an excel formula in which i can add exactly one month to the month thats on the left of it on the previous row.

for example

Date Date

12/27/10 1/27/10

the second one i wish to show up automatically,

i know that it would be something like this:

=DATE(YEAR(C13), MONTH(C13)+1, DAY(C13))

but i am looking for a formula for the entire column so that i do not have to add the formula to each individual cell

thank you in advance

### Answer

Just Copy and Paste that cell (with formula) to the cells below

VBAXLMan

### Can someone help me with a lookup table in Excel 2007?

I have to "create a lookup table that will help determine the letter grade for each student. The percent levels should be entered in the first column; the corresponding letter grades entered in the second column. (Assume a typical grading scale where 90%=A, 80%=B, etc.) Name the lookup table Grade_table. Arrange the % levels and corresponding grades from lowest to highest. Please help!!!

### Answer

You need to use the VLOOKUP formula

In addition to Name feature

First you need to do the table, somewhere in any free area (say G1 to H10) put the percentage in G, corresponding Grade letter in H

Then, select that range, and go to Formula > Name, type in the name you have "Grade_table"

Then in cell A1, put any grade, say 76

In B1, paste this

=VLOOKUP(A1, Grade_table,2,TRUE)

You need to sort the Grade_table ascending by column G

VBAXLMan

### Excel help? 10 points?

http://i181.photobucket.com/albums/x93/x…

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?

### Answer

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

VBAXLMan

### I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?

### Answer

Did you try the formula LEN

LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this

=LEN(C1)

Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

### Trouble with sorting numbers on excel?

I'm trying to sort grade numbers on an excel worksheet, it was working for some columns and now I'm trying it again and it's not working. I'm highlighting all the numbers in the column and going up to Date, then sort. Any help?

### Answer

Try not to select the cells

Instead click on one of the cells, and not all of them, then do sort

What is Excel telling you when you click on "Sort"?

Excel should suggest the sorting options based on your selection

Again, clicking one cell when you do Sort, can remove a lot of pain

VBAXLMan

### Why does it come up with "#VALUE!" when i subtract 2 cells in excel? and how do i correct it?

How do i get it, to come up with the actual answer. Thank you! :)

### Answer

Another way is using the N function

Like this

=N(A1)-N(A2)

N will convert the text (that represent a number) into a number

VBAXLMan