You are here:
Excel/Questions Answered by Expert Craig
| Subject | Date Asked |
| excel | 11/17/2009 |
| Q: what in the function of name tab in insert menu in ms excel A: Here is a macro that will do it based on the value in cell A1: Private Sub ... | |
| Conditionnal formatting regarding date | 11/13/2009 |
| Q: Good day Craig, How do I make a cell turn grey if the date in a cell is either a Saturday or Sunday? A: You will need to add a second column (that you can hide) that references the date and assigns it a ... | |
| Renaming folders & links in Excel | 11/12/2009 |
| Q: We are going through a process of renaming and moving folders. We have a folder with excessive links ... A: Yes, renaming the folder will mess up the links, as will moving the folder. You would then need to ... | |
| Excel help plz | 11/10/2009 |
| Q: I have two columns of data, the first column is a list of all the numbers in my address book, "all ... A: This looks like a job for the vlookup formula. You can use the fx button to search for it, or start ... | |
| LISTS IN EXCEL | 11/10/2009 |
| Q: I need to compare two mailing lists typed in excel. Sheet 1 has the old version and sheet 2 has the ... A: This looks like a job for the vlookup formula. You can use the fx button to search for it, or start ... | |
| Vlookup and Match | 11/9/2009 |
| Q: I have a list as follows:- Column A B C Reg Branch ... A: If you know how to do vlookup, you are halfway there. All you need to do is add a column to each ... | |
| Aging Report | 10/30/2009 |
| Q: I am trying to create an aging report for my boss but have never seen one. Can you provide me with a ... A: You will probably be able to find some more elaborate versions just by googling "excel aging report ... | |
| Formatting | 10/28/2009 |
| Q: hey, i have set up an employee hours form in excel. i have the hours counting and giving an total ... A: Sorry this took so long to respond. You may have already received an answer. If not, can you send ... | |
| How to link copied sheets back to original workbook | 10/26/2009 |
| Q: We have a budget workbook that contains each of our departments on a separate spreadsheet. All of ... A: Here are a couple strategies I can think of. First, make two additional copies of the original ... | |
| How do i get a formula to automatically repeat | 10/25/2009 |
| Q: hoping you can help me with this one. I am self taught in excel and have made several spreadsheets ... A: I'm not aware of a way to make that happen, but, something I do is set my conditional formatting to ... | |
| Work sheet | 10/20/2009 |
| Q: I want to enter a sentence in 50 worksheet in a same workbook. Can u help me out plzz... A: If I understand correctly, you want to type a sentence one, but have it show up in 50 wksts all at ... | |
| equal sign problem in excel | 10/14/2009 |
| Q: When I use an equal sign (=) to make one cell equal another, I get the cell address of the target ... A: I'm not quite sure I understand the question, but let's see. Say cell A1 is currently 115. In cell ... | |
| Inserting page breaks with subtotal in Excel 2007 | 10/12/2009 |
| Q: I am trying to print a separate page for each subgroup in an Excel 2007 spreadsheet. Since there are ... A: Are you using subtotals? If you are, you should be able to go to Data/Subtotal and check the box ... | |
| Extracting names | 10/9/2009 |
| Q: I have question please. I have a worksheet with 2 spreadsheet tabs. In column A on the first sheet ... A: I just had a similar question, so I am going to cut and paste and hopefully you will be able to ... | |
| Regarding Excel 2007 | 10/9/2009 |
| Q: I have a problem in Excel 2007, I have two sheets with some data. I have to filter those data which ... A: I had my formula a little bit off, =VLOOKUP(A2,sheet1!$A$1:$A$3000,1,FALSE) I took out the ... | |
| Regarding Excel 2007 | 10/8/2009 |
| Q: I have a problem in Excel 2007, I have two sheets with some data. I have to filter those data which ... A: I'd say this appears to be a solution for the VLOOKUP Function. Assuming you have a unique ID field ... | |
| Stacking Sheets | 10/6/2009 |
| Q: I have over 100 sheets in my excel worksheet and it is inconvenient to scroll back and forth through ... A: Here is a method if you are familiar with macros: You can use the following code in the VB code for ... | |
| workseet specifications | 10/5/2009 |
| Q: I am using Excel 2007. However, the number of rows it is defaulting to is 65,550. How do I get it ... A: I think you can fix this by changing your default saving format. Click the Office button and then ... | |
| Excel Formula | 10/3/2009 |
| Q: I am trying to find the correct formula to convert a time, for example 9:00 am, to simply the number ... A: Converting time can be a little tricky. All time/dates in excel are calculated as a value from ... | |
| Sorting Date Ranges in Excel | 10/2/2009 |
| Q: I am fairly new to using Excel. I am using Excel 2003 in my office. I have worksheet with a column ... A: The easiest way I can think of to do this is to use the formula "LEFT." You are going to need to ... | |
| Excel conditions | 10/1/2009 |
| Q: Good Morning! I am not use to using excel and I can't seem to get it to do what I want it to. So ... A: Try this: =IF((E7*0.25)>250000,E7*0.25,250000) I changed the % to decimals and removed the ... | |
| Excel date formulae | 10/1/2009 |
| Q: I want to figure out how many weeks are left between today's date and for example Friday the 18th of ... A: If I am understanding your question correctly, it should be fairly straightforward. In one cell ... | |
| Custom cell format with custom validation | 7/30/2009 |
| Q: I hope you can help. I work in a hospital and have a spreadsheet (in Excel 2003) to record whether ... A: I'm not sure how to make validation do it, but you could format the cells to your custom date and ... | |
| using ss as a tickler file | 7/30/2009 |
| Q: I'm new to using spreadsheets, and I've had no formal training. This may be very simple to do, or ... A: This is a two parter. First, you need a cell on your spreadsheet that automatically updates to the ... | |
| macros in Excel | 7/29/2009 |
| Q: how can you set a command button on the Excel tool bar which can function as a macro and also how ... A: In reading your question, I am assuming you have created a macro for converting the number and now ... | |
| data in one sheet, display in another | 7/16/2009 |
| Q: My question to you is: I have a sheet with a lot of data from different companies. Companies are ... A: This is a multi-part solution that uses data validation and vlookup. 1. On sheet1, highlight your ... | |
| how to insert dynamic time | 6/30/2009 |
| Q: i need your expertise. i want to insert a dynamic time in cell a1. im using msexcel 2003. i've tried ... A: You can update the NOW() formula using the F9 function key to recalculate. What I would do though ... | |
| Handling missing data - Excel 2007 | 6/29/2009 |
| Q: I have huge columns of sensor measurement data (> 175,000 lines per column and 150 columns. It is a ... A: I'm not sure what basic statistics you are peforming, but typically, if you left these cells ... | |
| COUNTIF -- multiple conditions | 6/29/2009 |
| Q: One column t4:t104 need to count how many cells have numbers that are >0 and <10. I do not need ... A: This is an array formula that will do the counting, eventhough it appears to be summing. When you ... | |
| Excel Formula | 6/28/2009 |
| Q: I really do appreciate it and my question is simple so hopefully wont take to much of your time. I ... A: These are my favorite kinds of problems, Hopefully this does what you want: ... | |
| time calculation | 6/27/2009 |
| Q: Greetings Craig, Thank You for being there . I am using excel 97. I would like to know how to ... A: The formula for current time is: =NOW() and you can choose whatever date/time format you want. To ... | |
| Macro To Compact a Table with blank cells | 5/10/2009 |
| Q: My objective of the macro is to compact a range deleting the blank cells. For Ex: consider * as ... A: I'm not sure exactly how to modify your macro to speed it up, or suggest a different one. One thing ... | |
| To Update Pivot Table From Source File Located on The Network | 5/7/2009 |
| Q: I am working on an automated training calendar and need your help. What I have is: I have an excel ... A: All you should need to do is create your pivot table on a separate tab (which should be the default) ... | |
| Calculating ongoing 3 and 12 month averages | 3/9/2009 |
| Q: I have a column of monthly electric usage (in kwh) over the last two or three years and want to ... A: Let's assume that "Month 1" is cell A1 and 473.7 is cell C3. Your formula in cell C3 would be ... | |
| Tranpose Formulas | 2/20/2009 |
| Q: I have a question about transposing a row of formulas (which involves values from another tab within ... A: Unfortunately, I don't have any great answers for you. The transpose option is only good for ... | |
| Excel security features | 2/18/2009 |
| Q: Critical or sensitive data is often used for analysis in an Excel spreadsheet. What security ... A: There are a few different options, depending on your needs. You can password protect a file so that ... | |
| Formula in Excel | 2/18/2009 |
| Q: I have a workbook that I use for my weekly deposits. Sheet 1 is basically my summary page. Sheet 2 ... A: I would use a pivot table. In the detail list, make sure you have headers for each column and then ... | |
| cost of sales vs. selling price | 2/6/2009 |
| Q: i have two worksheets- the first is an Sales Invoice Journal and the second is a Cost of Goods ... A: If I understand your explanation, it looks like you need to create a unique identifier for each ... | |
| Excell DATE functions | 2/2/2009 |
| Q: I run a Bar at our local club. There is a book (Excell) where we handle debt and receive payments. ... A: A short macro with an assigned shortcut key should do the trick for you. The following macro will ... | |
| IFSUM with two of three criteria | 1/21/2009 |
| Q: I have simple spreadsheet. Column A has a list of countries, column B has population figures, and ... A: Does each item in column C contain only one value? One solution to summarize what you've got is to ... | |
| excel | 1/21/2009 |
| Q: . I have 4 xls. All 4 of them contain clinets. The one has 11.020 clients the other 11.670 and 6780. ... A: This is not a macro, but should be fairly easy and does not require a whole lot of work. It is the ... | |
| Counting Days in a Month | 1/12/2009 |
| Q: I'd like to use the count function '=count(e10:e11)' to automatically count the number of days in a ... A: Can you describe more of what you are trying to do? I'm not aware of a single formula that will do ... | |
| Need to convert a Number to seconds | 1/10/2009 |
| Q: I have got this excel sheet with time entered in the following format '12.32' (i.e. 12 minutes and ... A: Here is a macro that I recorded to do that. Basically, what it does is takes the 12.32 and uses ... | |
| Excel formula | 1/8/2009 |
| Q: I have a spread shhet with a list of dollar amounts and dates. In another part of the spreadsheet I ... A: Have you used a pivot table before? Make sure you have headers for your dollar and date columns. ... | |
| Excel Help | 1/6/2009 |
| Q: What I'm trying to do seems simple but it's been so long I can't remember the formula ... A: I don't quite understand the mechanics of where you are adding the 50 to get a value of 850 for a 9, ... | |
| Change Color of Cell (Date Dependent) | 12/15/2008 |
| Q: I have w/c dates along the top of a spreadsheet in row 1, and I would like the specific week we are ... A: I'm still trying to sort out a solution. So far, I can get it to highlight the two weeks closest to ... | |
| Formula off by $1 | 12/14/2008 |
| Q: I have a formula that is off by $1 occasionally. Can you tell me how to fix it so it will always ... A: Did you copy the formula for all three calculations? I did forget to divide by 100 in the formula, ... | |
| calculating a due date | 12/14/2008 |
| Q: I'm trying to create a data collection tracking log. I need to be able to calculate assessment due ... A: I hope I understand your question. So if you enter a date of a particular item, you want to be able ... | |
| Replacing file name with entry in another cell | 12/13/2008 |
| Q: I am trying to draw data from different files based on what is selected from a drop-down list. The ... A: I don't think there is an easier way to do that, unless you were able to put all your data from 01 ... | |
| Pivot table data update in 2007 | 12/11/2008 |
| Q: Once I create pivot table in Excel 2007, I cannot modify it to add new columns or increase the data ... A: I haven't used Excel 2007 much, but I think it is the same for previous versions. Here are a few ... | |
| Formula off by $1 | 12/11/2008 |
| Q: I have a formula that is off by $1 occasionally. Can you tell me how to fix it so it will always ... A: It looks like you are running in to round errors. Probably the easiest way to fix this is to ... | |
| Excel | 12/10/2008 |
| Q: I have a worksheet with a column of amounts and then another column of codes for those amounts. I ... A: I hope I am understanding the question correctly. You have several codes tied to several amounts ... | |
| Excel conditional formatting | 12/9/2008 |
| Q: I am creating a 'counter' using excel spreadsheet. I want to know how to make the font in certain ... A: Yes, it can be done. Format/Conditional Formatting Instead of accepting the default "cell value ... | |
| COUNTIF with 2 conditions | 12/8/2008 |
| Q: looking forward to your assistance. I would like to create a formula that will tally up using 2 ... A: I'm not sure if there is a formula that will do exactly what you want, but here is a fairly ... | |
| excel and strings | 12/7/2008 |
| Q: I created a students reports of monthly test. In a month two tests will be conducted. If the marks ... A: I am hoping that I am interpreting correctly. You enter a score in cell B2 for example as either ... | |
| Date functions | 12/3/2008 |
| Q: Using the =now() or =today() function, I would like to get the next couple of days. That I know how ... A: If looks like your best bet is to include the "weekday" function, which assigns any date a number ... | |
| Excel - Compare columns for differences | 11/30/2008 |
| Q: Hopefully this is an easy one for you. I have two columns of alphanumeric data that I want to ... A: This should be doable via the vlookup function. Say you have column A items 1 to 250 and B items 1 ... | |
| Pivot Table Display settings | 11/29/2008 |
| Q: I have no previous experience of working with pivot tables,one of my fields displays the word ... A: If I understand correctly, you should be able to go to the gray box heading the list that contains ... | |
| vacation aging table | 11/25/2008 |
| Q: I am looking for help in writing a vacation aging table. Employees accrue vacation at payday - ... A: This one took me a bit of thinking, but I think I've got a solution. You need to add a NET column. ... | |
| update date | 11/24/2008 |
| Q: I have a template in excel which I would like it to remember the date of last update. People uses ... A: Vba code is not my strong suit, but here is a suggestion. Record a macro that enters the formula ... | |
| Negative coulm | 11/24/2008 |
| Q: I have 3 columns in excel one of which I would like to have every cell in that column be permanently ... A: I am not aware of a way to set it to be negative to start with, but you can change them all to ... | |
| moving info from rows down into column | 11/18/2008 |
| Q: i have an excel data base that comes off a website, i need to get it into an excel mailing list. ... A: Depending on the complexity of the information, you can transpose this information. Highlight your ... | |
| Calculating Percentage Increase | 11/18/2008 |
| Q: I'm looking to calculate the percentage increase between an opening and ending balance with 'cash ... A: I'm not sure this is going to completely solve your problem, as I don't think I completely ... | |
| Excel 2000 files transferred to Excel 2003 | 10/31/2008 |
| Q: My computer was recently updated from Windows 2000 to Windows XP, which also included a change to ... A: This is not my area of expertise, but here are a couple of possibilities: 1. MAIL MERGE Go through ... | |
| EXCELL CHECK BOX | 10/31/2008 |
| Q: I am trying to use a check box in excel 97 to toggle a formula on and off in a specific cell. this ... A: Sorry for taking so long to get back to you, but I couldn't remember what the trick was. If you use ... | |
| IF and AND syntax | 10/29/2008 |
| Q: I'm trying to do a nested if with an AND AND statement. ... A: Try this: =IF(AND(A2<>"",B2<>""),A2,IF(AND(AND(A2<>"",B2=""),A2<>"",C2<>""),A2,"")) I removed ,"" ... | |
| Date Formula | 10/22/2008 |
| Q: I am creating a report tracker Each report has 3 different due dates dependent on a close out date ... A: Assume your COD is in cell B11. (modify formula as necessary) ... | |
| Cell Widths | 10/18/2008 |
| Q: Is it possible to have cell a1, b2, c3, d4 etc one width while cell a2, b2, c2, d4 etc another ... A: The short answer is...no. You could try inserting an additional column between each and use that ... | |
| Excel automatic sort | 10/15/2008 |
| Q: Is there a way to automatically sort a list of numbers each time new data is entered without using ... A: You've got me a little confused with what you are wanting to sort. Are you sorting total scores, or ... | |
| In excel need take total hours and divide by number. | 10/14/2008 |
| Q: At this time the following is formated with a formula but doesn't deliver the correct end result. ... A: Hopefully, this will help you get to the answer that you want. Basically, what is happening is ... | |
| Locating Duplicates in Excel | 10/10/2008 |
| Q: i'm trying to find a way to locate duplicate cells (values) in a column between a column in one ... A: I'm not aware of anyway to automatically highlight either of these, but you can use the vlookup ... | |
| Using Count in Excel | 10/8/2008 |
| Q: Sorry for the basic question, but I am really new to excel. I need to count the number of times a ... A: You actually don't need to have column 'D' sorted in any order. Assuming your column is 500 numbers ... | |
| Excel Formula | 10/6/2008 |
| Q: I have a list of ID's in column A and another list of ID's in column B. I have 3 worksheets like ... A: This sounds like a job for the vlookup function. In column C of the first worksheet, you can look ... | |
| excel | 10/5/2008 |
| Q: can i convert numbers ie: 1 /2 /3 /4 etc to Alpha prefix A1 A2 A3 A4 A5 B1 B2 B3 ETC WITHOUT ... A: A simple formula would do this pretty easily, if I am understanding correctly. Presume column A has ... | |
| Excel Formula | 10/4/2008 |
| Q: I need help with an excel formula – detailed below For example Amount is 1,200,000.00 – I need to ... A: Assuming your example amount is in cell A1, your formula would look like this: ... | |
| Double dollar signs | 10/3/2008 |
| Q: I am working with a spreadsheet where I have Brazillian Reals ($R) and dollars. When I selected a ... A: I tried to reproduce this problem from your description, but was unable to do so. (I didn't have any ... | |
| ABOUT COPYIN AND SEARCHING AND PASTING. | 10/2/2008 |
| Q: I am one of the excel user. And I have a hard time in fixing the macro. Esentially, my goal is to ... A: Without being able to see your your file, it seems to me that this would be much better accomplished ... | |
| Copying a formula but showing no figure. | 9/22/2008 |
| Q: I recently asked a question that you kindly helped me on. Now with the formula i have ... A: Two different possibilities, If Then, or Conditional Formatting If Then: ... | |
| Date Cell Formating | 9/18/2008 |
| Q: I can't figure out how to set a cell with a date and time (mm/dd/yy hh:mm AM:PM) format to wrap. It ... A: This may not be exactly what you are looking for, but you can do a bit of a manual workaround. If ... | |
| Linking a word document to an Excel cell | 9/17/2008 |
| Q: I've been tasked by work to create a contracts database in excel (most of the staff can't use access ... A: I think I can answer the first question, but not the second. Have you tried using a hyperlink? ... | |
| Excel Problem | 9/17/2008 |
| Q: I am currently working on a consulting timesheet which basically gets created from our billing ... A: This is likely going to take a couple back and forth questions and answers and since I only take a ... | |
| RE: transf/copy cell content | 9/15/2008 |
| Q: sent a question for the first time a question and i got your answer. I'm so excited and I really ... A: I'm not sure that you'd be able to make it go to a certain cell in sheet1 without a lot of VBA ... | |
| how to do averages > 0 with selected cells | 9/12/2008 |
| Q: I was wondering what formulas could I use to create an average in certain cells. Example: I have ... A: There are a couple work arounds to get you what you want. First, you can average the range say ... | |
| New to Macros - Need Help | 9/7/2008 |
| Q: I am very new to this world of Excel Macros so do not know how to write macros. I need to write a ... A: This could be done fairly easily without a macro. First, highlight cells a2:a21 and choose ... | |
| Conditional Formatting in Excel 2007 | 9/4/2008 |
| Q: I am of medium ability in Excel and know I can do the following (without Macros etc?) but just can't ... A: I will answer this for Excel 2003, as I haven't started with 2007 just yet, but hopefully this ... | |
| Pivot table - data refresh error | 9/3/2008 |
| Q: I have a pivot table to keep track of attendees to various conferences based on date. (People call ... A: I haven't seen that error before, but, if you could send a screen shot of what your data table looks ... | |
| importing xmls file to excel | 8/29/2008 |
| Q: hope you can help. I'm importing .xmls file (a table with rows and columns with headings. I have ... A: I'm not quite understanding all that you are trying to do, but one strategy, if you are trying to ... | |
| Paste Special > Formulas replaced data values, too. | 8/28/2008 |
| Q: Craig, I introduce "Paste Special" to students with a simple error in monthly income statments ... A: I don't have a good anwer for you, but rather than not answering it at all, I am letting you know ... | |
| Excel - Cumulative value | 8/25/2008 |
| Q: I have an excel sheet which is having 4 columns in the cell, A8,B8,C8,D8. A8 contains the ... A: Sorry it took so long to get back to you. In order to make this work, you need to have an ... | |
| Bonus Sheet | 8/23/2008 |
| Q: I would like to construct a spreadsheet that will calculate bonus payments to staff, the columns ... A: Sorry it took so long. You can set up a CountIf formula to count the totals of each letter per row ... | |
| Sumproduct problem | 8/21/2008 |
| Q: i hope you can help. I am trying to create a spreadsheet to evaluate events by region. The events ... A: It is not my intention to insult your intelligence, but occasionally (at least in my own work) it is ... | |
| PROTECTING FROM EDITING | 8/20/2008 |
| Q: i have a worksheet in which start time and end time is calculated.. it is a sharing file used by ... A: This can be done with a short little macro like the one below. First though, you will want to open ... | |
| Auto mailing option | 8/20/2008 |
| Q: is it possible to have an auto mailing option in MS excel. I have a spreadsheet which would be ... A: I'm not aware of any easy way to make excel do this by itself. You could certainly do it via macro. ... | |
| Count if | 8/18/2008 |
| Q: I have a sheet that has a column listing of specific dates (e.g. multiple different dates entries ... A: Here is an example I borrowed from MrExcel.com This is the link and the text: ... | |
| Sorting Linked Data | 8/7/2008 |
| Q: I have data in one worksheet linked to another worksheet within the same workbook. When I sort the ... A: This sounds like you may have absolute references in your links. Example: =sheet1!$A$1 If this is ... | |
| Multiple columns and rows SUM | 8/7/2008 |
| Q: Currently i on a reports but the data entry into the reports is so troublesome and tiring. I need to ... A: The colon : symbol is used to describe "through" in excel. So sum a10 through d10 would be: ... | |
| conditional formatting containing txt | 8/6/2008 |
| Q: If I highlight A1:A5 and want it all filled purple if A5 contains the word "Resigned" within it ... A: Hightlight A1:A2 and go to Format/Conditional Formatting. Switch the drop down to Formula Is and ... | |
| Compare data on 2 different worksheets, put matching data in 3rd worksheet | 8/5/2008 |
| Q: How do I compare the names in column D in the USA Cities worksheet with the names in column B of ... A: This may be a little convoluted, but hopefully should get you the answer you want. On your new ... | |
| formatting excell | 8/4/2008 |
| Q: I want to make a football win loss percentage spread sheet. How do I format the cell to get the win ... A: A B C D Team W L % Seahawks 3 1 ... | |
| linking drop down lists | 8/4/2008 |
| Q: right I have a 2 column table with stock items and prices I want to create a drop down list so that ... A: This should work with data validation and vlookup functions. 1. Highlight your list of stock names ... | |
| need help | 8/2/2008 |
| Q: following is my question let data of sheet 1 Columns A B C D E 1 ... A: As long as the values in column A, sheet 2 are all unique, you can use the Vlookup function. In ... | |
| match,copy,paste | 7/31/2008 |
| Q: I've been trying to figure out a way to make my life easier on excel and have had no luck. I have ... A: This sounds like a job for the Vlookup function, which will work as long as the cost codes are ... | |
| Summary Data | 7/30/2008 |
| Q: I have a workbook that contains several different pages that each Producer is supposed to update. ... A: Do you use macros? I had a very similar problem recently and ran across this macro that combines ... | |
| VLOOKUP More Than 1 Value | 7/28/2008 |
| Q: I need to lookup agents names, but sometimes they appear twice. Is there a way to make the formula ... A: Vlookup always returns the first value it finds, so if you are only wanting the highest value for a ... | |
| Excel - Combine Customer Names | 7/26/2008 |
| Q: I have two Excel sheets and both have customer names but different data. Both the sheets have been ... A: Without your common identifier, this is not easy. Probably the best solution I could suggest is to ... | |
| Excel Running Balance | 7/24/2008 |
| Q: I have a spreadsheet keeping track of 3 loan payments I have from school. I want to have a cell ... A: The easiest way to do this is to set up a running balance column. For instance, cell ... | |
| Combining Excel Workbooks | 7/23/2008 |
| Q: I have three different workbooks that I want to combine into one. I tried to right-click on the tab ... A: The short answer is yes, I think so, but it will take a bit of work to make it happen. First, the ... | |
| summing data | 7/22/2008 |
| Q: I am processing large reversing entry spread sheets. I have to manually go through the sheets with ... A: It would help to see an example, but I think you might also be able to accomplish this using a ... | |
| Copy Data to another sheet | 7/21/2008 |
| Q: Here's my situation. I enter data into a monthly worksheet and then make a report based on data in ... A: I think this will work for you. Lets say you've got a sheet called "Data" and another called ... | |
| Between! | 7/18/2008 |
| Q: I am trying to conditionally format cells based on three conditions which relate to other cells. ... A: Conditional formatting using a formula based on the value of another cell is limited to one formula ... | |
| addings numbers in time format | 7/17/2008 |
| Q: Craig, how've you been? using Excel 2003, I want to add minutes to a time that appears as, say, ... A: You are correct in understanding that format is critical to properly calculating and displaying time ... | |
| Pareto analysis issue | 7/16/2008 |
| Q: Im having an issue making a Pareto Analysis Chart in excel, I work for a company that has a quality ... A: I think this is what I'm interpreting: you have code frequency 457 3 1032 5 9947 8 ... | |
| excel qestion | 6/16/2008 |
| Q: Hai, i want to calculate percentage in excel, but if the percentage result is less than $500. I want ... A: It looks like an if/then type formula would work best for you. If condition A is true, then X, ... | |
| Counting characters per line in excel | 5/21/2008 |
| Q: I have a cell in excel that has 5 lines of text and I would like to count how many characters there ... A: I'm not aware of any formulas that will count characters in each separate line of an individual ... | |
| Selecting varying cells to total | 4/21/2008 |
| Q: Craig, I use Excel to track invoices for my business. My employees are paid on a commission basis, ... A: You should be able to use the SumIf function at the bottom of the commissions (on the line below ... | |
| Pivot Tables | 4/10/2008 |
| Q: I'm currently having an issue with an existing pivot table. It is pulling through all the correct ... A: I don't have any suggestions based on your description, but I use pivot tables daily and would be ... | |
| drop down box | 4/9/2008 |
| Q: I am trying to make a drop down box in one cell, but when the user clicks on his selection it ... A: This looks like it should be doable using data validation (for drop downs) and vlookup. The drop ... | |
| capturing data on multiple worksheets | 3/25/2008 |
| Q: all I want to do is take the sums from worksheet one and two and put them on worksheet three where I ... A: If I am understanding correctly, you should be able to go to worksheet 3 and click in the cell where ... | |
| Excel - Excluding certain dates when creating a chart | 3/24/2008 |
| Q: Craig, I am trying to create a chart and I cannot figure out how to exclude certain dates. I only ... A: You should be able to right click in each of your charts and choose chart options and select the ... | |
| Excel Formula Percent | 3/24/2008 |
| Q: I am trying to calculate the percent of winnings each player on my pool team will get by the ... A: This sounds like a math question. In order to allocate this I have two different suggestions: 1. ... | |
| kind of what if analysis in excel | 3/23/2008 |
| Q: I ma trying to create a troubleshooting guide in excel. Here I would have a set of questions, ... A: Have you used IF-THEN statements before? If not, let me know and I can try to help you set some up. ... | |
| function in excel | 3/22/2008 |
| Q: I am trying to get an Excel spreadsheet to automatically post our accountability percentage through ... A: The value of the check box is true or false, checked or unchecked. There is typically a cell that ... | |
| Excel Hyperlinks | 3/21/2008 |
| Q: I have hyperlinks for each cell in column A on sheet 1 to each cell in column A on sheet 2. I have ... A: Unfortunately, Hyperlinks appear to be static references (they always point to the same location). ... | |
| Adding another column headers and Row headers | 3/4/2008 |
| Q: How to add an extra column headers and Row headers in excel report using Vb code A: The easiest way to do this is to record a simple macro that does what the things that you want and ... | |
| excel- Vlookup + indirect | 3/3/2008 |
| Q: i have 10 sheets in a work book, last 2 are master entries I am using ... A: Sorry it took so long to get back to you. The easiest is to change your range to a much larger ... | |
| Identifying Cell Address | 2/27/2008 |
| Q: I have a 9 x 9 range of numbers. I know I can find the maximum number with the function Max(a1:i9). ... A: I'm not aware of a way to return the cell reference, but I do know how to highlight the cell so you ... | |
| selection of similar entities | 2/26/2008 |
| Q: I have a excel file with around 1000 names. I would like to select all 4 letter names or 5 letter ... A: Your best bet is to set up a second column with the formula for counting the number of characters in ... | |
| if/match/sum: complicated formula array | 2/25/2008 |
| Q: I have a spreadsheet in which I use the following formula: =SUM(('Jan Data'!V3:V65535="D.L. ... A: I would think this might be easier using a pivot table. I may not be fully understanding your task, ... | |
| active cell copies | 2/21/2008 |
| Q: I created a macro for 1250 tables. However, I would like the outcome to be stated in a table ranging ... A: You should be able to do that using the relative cell reference when recording your macro, which ... | |
| Excel | 2/20/2008 |
| Q: Can you round up time? A: The short answer is yes. One way to do that is to format your cell using the time options. ... | |
| increase cell format | 2/14/2008 |
| Q: I try to create a file using excel that include 40 worksheet. Every cell is different cell format. ... A: I don't quite understand your question. Excel has a limit of 256 columns and 65,000+ rows, but the ... | |
| Macro with multiple conditions | 2/13/2008 |
| Q: I have a database of roughly 130 parts. There are five different conditions that determine which ... A: Is K55 a part number, or a excel cell reference? The details of the vlookup for the following ... | |
| how to update time in a column ? | 2/13/2008 |
| Q: so i want to insert current time in A1 when i insert something in B1. how can i do it?? A: There is a formula for current time =NOW() This can be updated to real time by hitting F9 That ... | |
| Formatting a cell to handle BOTH dates and numbers | 2/12/2008 |
| Q: I want to format a cell so it can handle both dates and numbers. Basically, I want the cell to ... A: I'm sorry to say, that there is not a way to work around this for what you are specifically asking. ... | |
| vlookup | 2/12/2008 |
| Q: I have a table and I need to return a result using 2 columns. I have read about matching and ... A: There may be a better way, but my first thought was to use the concatenate function to combine your ... | |
| Macro with multiple conditions | 2/11/2008 |
| Q: I have a database of roughly 130 parts. There are five different conditions that determine which ... A: I would use a combination of vlookup and concatenate to generate your parts. This will require some ... | |
| calculation of average | 2/6/2008 |
| Q: there is a dark triangle appearing in the upper left of the boxes of my spreadsheet. it appears to ... A: Typically, the dark green triangle represents excel noticing that that particular box is not the ... | |
| Formula meeting certain conditions | 2/5/2008 |
| Q: I am trying to create a spreedsheet to keep track of different lengths of my I-joist inventory. I ... A: You should be able to combine the series and length using the "Concatenate" Function. Create ... | |
| Formulas | 2/5/2008 |
| Q: I am working on a family diet chart for fun to keep track of weight loss or gain. One of the ... A: What about modifying the end to add an additional condition, (=IF(ISBLANK(C11),"",IF(C11<0,0,1)) ... | |
| Picklist... The choice get a differente answer | 2/4/2008 |
| Q: !! I have a Picklist with two differents values (A or B) and depending on the choix (for ex I ... A: If I understand correctly, it looks like an IF...THEN statement will work for you. If your picklist ... | |
| cash flow projections | 2/1/2008 |
| Q: I am building a spreadsheet to calculate monthly cashflow. Income is every 2 weeks, so I have the ... A: This one was fun. You were on the right track, but needed to add a few more conditions that took ... | |
| Create an excess report | 2/1/2008 |
| Q: Craig, I'm new to all this but trying to learn now. Appreciate if you could help me on this one. ... A: You will want to start with the countif function that counts the total number of Xs, Ys, Zs from ... | |
| excel program? | 1/29/2008 |
| Q: I have a shop and I have a lot of consignors whom i pay a percentage monthly for whatever they have ... A: This sounds like something I could definitely do for you and would be interested in making it work, ... | |
| Adding columns together | 1/29/2008 |
| Q: I am using Excel 2007. Forgive me I am a new user and I don't know a lot of the lingo involved with ... A: I think IF/THEN would be helpful for you. =if(c1>0,a1&"> "&b1&"> "&c1,a1&"> "&b1) This would ... | |
| splitting dollars & cents | 1/27/2008 |
| Q: I am trying to set up a spreadsheet where I am taking a percent of a dollar amount. I would like to ... A: Have you tried text to columns? Highlight your column with the money and choose Data/Text to ... | |
| Formula meeting certain conditions | 1/25/2008 |
| Q: I am trying to create a spreedsheet to keep track of different lengths of my I-joist inventory. I ... A: This looks like a combination of items would help you. My first suggestion is that this would work ... | |
| Conditional Formatting | 1/24/2008 |
| Q: I have a table with contacts in it. In the first column I enter C for cofirmed when I have ... A: You can get the whole row to highlight using the "Formula Is" instead of "Cell Value Is" from the ... | |
| drop down box | 1/24/2008 |
| Q: I am trying set up an worksheet that will allow me to search data through the entire column via a ... A: I think what you are talking about is filtering. Highlight the entire data set so that the top row ... | |
| Paste data to filtered column | 1/23/2008 |
| Q: I have two columns with many rows in sheet1. The header for column A is group and column B is name. ... A: I understand your problem and have experienced it myself on a number of occasions. Unfortunately, I ... | |
| COUNTIF | 1/22/2008 |
| Q: do you know if there is a way of using the 'date' function within this formula? ... A: The CountIf function is pretty picky, only constant values or text. My best advice is to create an ... | |
| Problem with writing interest formula for budget | 1/20/2008 |
| Q: I am in the process of trying to make a projection chart so I can set a budget for myself. My ... A: It looks to me like you've got things figured out appropriately, so it may be an issue of rounding ... | |
| Excel Formula | 1/17/2008 |
| Q: I want to look up values in a table and I want Excel to: 1. Count the values if there are multiple ... A: If I am understanding correctly, you want to look at a table and know how many values there are in ... | |
| Auto Colour cells | 1/17/2008 |
| Q: I want to import info into a spreadsheet, once that info is in, have all cells with text that begins ... A: Sorry it took so long to respond, but I woke up this morning with some inspiration. Conditional ... | |
| Charting various sets of dates | 1/14/2008 |
| Q: I have data that spans over a year but I only have data for 3 months during this time and it is not ... A: I'm not aware of any way to hide the missing dates, but a couple suggestions are: Use bar charts and ... | |
| time in excel | 1/13/2008 |
| Q: I would like to be able to click in a cell and the time appears A: Have you used the =NOW() function? This puts the current date and time in your desired cell, which ... | |
| problem in excel macros | 1/12/2008 |
| Q: i want to design a form using macros containg 1 tabkey and 3 optional button , 1.if optional ... A: Creating buttons to creat macros is not too difficult, but I don't quite understand what you are ... | |
| Conjoin | 1/11/2008 |
| Q: In Column A1 to A10 we have Sun, Moon, Mars, Mercury, Jupiter, Venus, Saturn, Abc, Pqr, Xyz. In ... A: I will be sending you my proposed solution, but I would prefer to keep all communication through ... | |
| Conditions for formulas to kick in | 1/10/2008 |
| Q: I hope that you can help me and that I can explain what I need. What I am trying to achieve is quite ... A: This sounds like an IF, THEN statement would work. Say C29 is the cell that needs to hit 45% before ... | |
| excel | 1/8/2008 |
| Q: I am having trouble figuring the formula to show Markup percentage not just percentage of items. We ... A: So if the cost price is $1.00 (cell A1) and the Suggested retail price is $1.50 (cell B1) the markup ... | |
| If Then statement in Excel | 1/3/2008 |
| Q: I am attempting to create a if/then formula =IF(or(G4=0, G4=1, G4=2, G4<2),"110","95","80","80") ... A: There are a couple different solutions, depending on what the entries will be like. You can format ... | |
| separating first name and surname according to capital letter | 12/20/2007 |
| Q: I was wondering if there's built-in function or macro in Excel that allows me to separate a full ... A: I'm not aware of any function that would do that. You could use Text-to-Columns with the delimiter ... | |
| if statement problem | 12/19/2007 |
| Q: I have a list of data in excel. I want a separate cell to look at the list, find the first blank ... A: You could create a separate column with a simple IF THEN formula. Say in your example, employees is ... | |
| Run macro on click event | 12/19/2007 |
| Q: i am Mahdi Hasan from Bangladesh. How do i run a macro when i click on a button in a excel work ... A: If I am understanding correctly, you need to assign your button to a macro. Correct? ... | |
| Embedding jpg's in Excel workbooks | 12/18/2007 |
| Q: Is there a way to embed a jpg or pdf file into an xls file so that an icon appears on the page and ... A: I'm not sure how to do exactly what you ask, unless you create a hyperlink (which is not an icon, ... | |
| Excel 2003 - Highlight cells in a column that are not values x,y,z | 12/17/2007 |
| Q: I need to be able to format one column (ie Lead Source) so that if the value in that column is not ... A: You should be able to use conditional formatting using OR in your formula should let you do an ... | |
| Excel | 12/15/2007 |
| Q: I have a ton of formulas and such and I dont want people I email it to being able to mess around ... A: I think your best bet is to password protect the sheet, but if you have cells that you want to be ... | |
| Paste the formular in difference columns | 12/13/2007 |
| Q: I have the table like below; A B C D E F 1 ... A: Unfortunately, Excel will not let you copy non-contiguous selections into non-contiguous results. ... | |
| additions of monthly data | 12/12/2007 |
| Q: Could you please help me out with vba codes to transform monthly data to quarterly. ANSWER: Hi ... A: I would use something like this: If condition Then statement1 [ ElseIf statement2 ] [ Else ... | |
| Excel Formula | 12/12/2007 |
| Q: I have a table of data and I would like help in writing a formular. The table looks like this: ... A: This is a bit too complex to type in text. I'd be happy to send you the spreadsheet I designed to ... | |
| additions of monthly data | 12/11/2007 |
| Q: Could you please help me out with vba codes to transform monthly data to quarterly. A: This is really most easily accomplished by using a pivot table. Once you have your data set up in ... | |
| reference cell | 12/10/2007 |
| Q: I'm trying to set up a cell which jump to a particular Worksheet in another Excel file, but even if ... A: I think you want to use a hyperlink instead. Attached is the Excel Help section on Hyperlinks. Let ... | |
| Referencing a Cell after a Blank Cell | 12/7/2007 |
| Q: I have a set of data about 4,000 rows long. I want to be able to reference the data in columns B ... A: Hi Todd, this is a sample of the formula that should get you what you want. ... | |
| Mail Merge numbers from Excel to Publisher | 12/7/2007 |
| Q: I'm having the hardest time trying to mil merge a simple number (currency) into Publisher. Even ... A: It is not Excel, but Publisher that is the problem. Here is a link to the Microsoft website on how ... | |
| Formula | 12/6/2007 |
| Q: This is a receipt for a question you recently asked at allexperts.com of kishore Bhupati. You ... A: This one is going to require a follow up from you. From what I can tell, #1 and #4 can be combined ... | |
| I need your help | 12/5/2007 |
| Q: Can you please help me to get the Excel macro . We need reterive each and every serial numbers ... A: It depends on what the data looks like on the website. If it was built as part of a table (on the ... | |
| Compounding interest.... | 12/4/2007 |
| Q: Help! We need a spreadsheet formula which mimics what's happening to our bank loan on a DAILY basis. ... A: That looks correct to me, although, I think most banks actually calculate the daily using 360 days ... | |
| column to row | 12/4/2007 |
| Q: I wanted to know how to transfer prices on a column to a row, but combining the indirect address ... A: Your best bet is two add a third column that uses absolute references of the second column and then ... | |
| Transferring data to/from worksheets | 12/3/2007 |
| Q: When we get a new account, there are several sheets (forms) of information we need to fill out in ... A: Yes, absolutely. In worksheet one (Sheet1), say the name is in cell A2. In Sheet2, you need to ... | |
| 1. Comparing and editing 2 spreadsheets - 2. Exporting MS Outlook Data to Excel | 12/1/2007 |
| Q: Question#1. I use MS Office 2003. Is there a procedure, Excel formula or software to compare 2 ... A: Provided your two spreadsheets are all in the same order and you are looking for things like changed ... | |
| Sumif help request | 12/1/2007 |
| Q: i kindly need your help in the following: i have a sumif formula as ... A: Based on your description, try using an absolute reference. ... | |
| conditional formatting | 11/30/2007 |
| Q: Currently i can do this but have to manually go and enter the cell each time the formula in the ... A: You're using conditional formatting, correct? I think all you need to do is remove the absolute ... | |
| help! | 11/30/2007 |
| Q: . at work I'm making an Excel spreadsheet to import onto a webpage... One of my columns has a ... A: You are right, that would suck to have to do that manually. There are a couple of different ways to ... | |
| Cell Shading | 11/29/2007 |
| Q: I wish to use a specific background color for some cells, e.g. 232/252/181 typed in as RGB values, ... A: This is not as easy as Word, but still not too difficult. Tools/Options/Color Choose the current ... | |
| counting changes on column | 11/27/2007 |
| Q: I am pretty new to Excel and I came across a problem, I was wondering if someone could help me. I ... A: I'm not aware of any quick solutions or formulas to your question. I'd start with some sort of IF ... | |
| Counting in separate columns | 11/26/2007 |
| Q: I have a file for my company that determines the number of times a student eats lunch. In one ... A: A pivot table would be your best bet, but if you're not familiar with those, try this: Assuming the ... | |
| Excel Format (CSV) | 11/20/2007 |
| Q: I am trying to convert a Microsoft Excel Workbook format (xls) into a CSV without losing four zeros ... A: I think you would need to format the numbers as text prior to saving as CSV. You could also add a ... | |
| Excel pivot table question | 11/19/2007 |
| Q: I am trying to create a pivot table by consolidating data from multiple sheets in a document and ... A: I would add a column to your consolidated data that identifies which sheet each row of data ... | |
| Insert Time when a cell is filled with data, but don't update the time | 11/17/2007 |
| Q: Ok, i need A1 to have the time automatically inserted when B1 has something put in it (words). I can ... A: Once the time is inserted, you could edit/copy/paste special/values. You might also try setting up a ... | |
| Excel Dates - grouping by month/year | 11/15/2007 |
| Q: I have tried grouping in my pivot table and excel won't let me - there are no blank cells, so go ... A: I'm not sure which grouping function you are using, so hopefully this is different than what you are ... | |
| reverse a number | 11/15/2007 |
| Q: How can I make a set of numbers in a cell be the reverse in another. (ie. A1 = 06528.... in B1 I ... A: There is no easy way to do this. The only thing I can think of is to take the original number and ... | |
| Excel Voting Buttons | 11/5/2007 |
| Q: I am creating a survey for a project i am doing. I dont know how to insert voting buttons into the ... A: I'm not exactly sure what you are trying to do, but to add a button, you should just need to go to ... | |
| Moving data by months | 11/5/2007 |
| Q: I insert a sheet and it is named the date of today. Theses sheets are collecting data. I need to be ... A: I would suggest using one sheet to collect all data (for a month at least, maybe even more). Add a ... | |
| Drop down menus to select multiple rows | 11/4/2007 |
| Q: I am looking to use a drop down menu to enter a supplier name, address and contact number into a ... A: I think your best bet would be to use either the Vlookup, or Hlookup functions. You may be able to ... | |
| Count cell with conditions | 11/3/2007 |
| Q: Take a look at the data below. Column A Column B Row A Apple 100 Row B ... A: I intended to figure this out for you, but I didn't have time before I went on vacation and am ... | |
| AOL & Excel attachments | 11/2/2007 |
| Q: AOL will not allow an attachment to be opened if it is an Excel or Word document unless it is opened ... A: I am not familiar with AOL either, but in my Groupwise email environment at work, there are some who ... | |
| Alternatives for Viewing Long Data Sets in Rows | 11/1/2007 |
| Q: I work for an executive that has built a spreadsheet in which the rows tend to be rather long and ... A: Have you used the Wrap Text feature before? Format/Cells/Alignment (Check the box that says wrap ... | |
| sum every date change | 10/28/2007 |
| Q: I have a spreadsheet with 2 columns column A has dates from older to younger columnn B has text ... A: This looks like a job for a pivot table. Add a column header to each column, Date, Description, ... | |
| Count cell with conditions | 10/27/2007 |
| Q: Take a look at the data below. Column A Column B Row A Apple 100 Row B ... A: My suggestions would be to add column C with this formula: =IF(A1="Apple",COUNTIF(B1,100),) This ... | |
| Sorting merged cells | 10/22/2007 |
| Q: In a new workbook do this:- put a 1 in cell A1 put a 2 in cell A2 now merge cells B1 and C1 and ... A: You are correct, "cannot sort merged cells" is a more appropriate error message. I'm not sure what ... | |
| XCEL sort question | 10/19/2007 |
| Q: Greetings, I'm trying to sort an entire spreadsheet by the value of one particular column. When I ... A: Your best bet is to highlight the whole range (area) of your data, to include all rows and columns ... | |
| If then statement with multiple worksheets | 10/18/2007 |
| Q: I have a workbook with 27 separate worksheets. Each worksheet contains data of when I need release ... A: I'd say this would be most efficient for you to set up as a pivot table. Combine the data from all ... | |
| Creating formula | 10/17/2007 |
| Q: I need to display the percentage of completed steps vs attempted steps in two columns. I am brain ... A: I need a bit more information. Is one column # of completed steps and the other column # of ... | |
| summing the value in a column | 10/16/2007 |
| Q: How are you doing? Sorry to bother you but I need help with this equation. Here is the equation ... A: I would add an additional column that identifies whether or not AY and BS meet the criteria. BT3 is ... | |
| IF functions/formulas in excel | 10/15/2007 |
| Q: I am working on a project that where the business buys overstocked books for bookstores. Basically, ... A: This is an interesting problem and I don't have an answer off the top of my head, but I would like ... | |
| Excel function | 10/13/2007 |
| Q: I am interested to know, how i can change number format EX: 140 to 100 190 to 200 999 to 1000 ... A: I think you could create a second column that divides by 100 with rounding and then multiplies by ... | |
| NameBox Causes Excel to Crash | 10/12/2007 |
| Q: I have a problem that has only recently occurred. I have created a multipage spreadsheet that is ... A: I think I would need to see the spreadsheet to see if I could figure it out. I would suggest ... | |
| Conditional Formatting | 10/11/2007 |
| Q: I have a spreadsheet with a column containing due dates, column L. I want this cell to highlight ... A: What I understand you to be saying is that column L has due dates that may be tomorrow, or may be 8 ... | |
| Lookup list | 10/10/2007 |
| Q: I'd like to be able to determine if the data in a cell matches any of the items in a list. If A1 = ... A: It appears that you should be using the vlookup function. Lookup_value A1 Table_Array ... | |
| read only easy | 10/9/2007 |
| Q: Is there a way I can easily open files in excel as read only? (That is without going to to the open ... A: I am not aware of a quick way to toggle, but I might suggest that instead of cutting and pasting ... | |
| excel colour change by number value | 10/9/2007 |
| Q: i work in a special school and have to make a profile for each pupil based on performance, i have ... A: You are in luck. There is a property called conditional formatting. Highlight the range that you ... | |
| Find and Replace | 10/8/2007 |
| Q: Is it possible to change the border colour (from the standard black) of the cell results found when ... A: This is sort of half an answer. You can change the border color when you replace a value, by ... | |
| Countif formulae | 10/8/2007 |
| Q: can i use countif for multiple conditions. and if yes pleas e show me an example. I want to count a ... A: I believe you best bet would be to CONCATENATE columns A and B to column C and then use the COUNTIF ... | |
| Excel combo Box | 10/5/2007 |
| Q: I have a particular column which lists the possible entries by a combo box, which I have made ... A: I'm not quite sure what you mean when you refer to a combo box (is this the same thing as a dropdown ... | |
| Excel | 10/4/2007 |
| Q: I use the apostrophe in excel cells to keep formulas and other formats from popping up on the cell. ... A: Yes, I believe that its purpose is to show text of what is entered in the cell. Another good use ... | |
| Perentages | 10/1/2007 |
| Q: saw your post on an Excel expert site and was hoping I could get a hand. Basically what I’m ... A: This is a series of IF THEN statements that will be made easier if you add a column (say column A) ... | |
| Using Filters in Excel | 9/26/2007 |
| Q: I have a spreadsheet containing a few columns. I want to be able to filter by the data in column A & ... A: I'm not aware of any way to limit your filters to specific columns. Sometimes you may find it ... | |
| LINKS | 9/25/2007 |
| Q: How do I link to a MASTER spreadsheet and keep the formats (color, font, etc)? ANSWER: Hi Chris, ... A: I don't recall that you can do sorting with both sheets highlighted. On the second sheet, it does ... | |
| returning a value | 9/25/2007 |
| Q: i want to return a value that reads hit if cell reference is between .95 and 1.05 otherwise it ... A: This is an IF THEN statement inside another IF THEN statement. If a number is greater than .95, ... | |
| Conditional number generation and forms | 9/24/2007 |
| Q: good Day! I work for the Quality Department and am still quite new to excel. I would extremely ... A: I apologize for not getting to this sooner, but I just haven't had time to look in to your request. ... | |
| LINKS | 9/24/2007 |
| Q: How do I link to a MASTER spreadsheet and keep the formats (color, font, etc)? A: Without more information, I can offer a few different suggestions. 1. Copy the MASTER spreadsheet ... | |
| Calculating monthly expenditures from running yearly total | 9/23/2007 |
| Q: So my explanation of my question may be long, but hopefully your background will help to already ... A: This looks like a prime candidate for a pivot table. This will give you a refreshable summary that ... | |
| Freezing panes across tabs | 9/20/2007 |
| Q: I would like to know how I can freeze multiple panes, as in across tabs. Let's say I have 20 tabs in ... A: Unfortunately, I think you're going to have to do them individually. You can do all sorts of things ... | |
| sub-table in the same sheet | 9/19/2007 |
| Q: This is a tough one - Each row (record) in my Excel sheet is a job description. Some of the jobs ... A: Have you ever used a pivot table? This looks like the perfect candidate. Set up a worksheet that ... | |
| Conditional Formatting | 9/18/2007 |
| Q: I work at a hospital and have set up a spreadsheet for the registrar to enter arrival time-then I ... A: I'm not sure how to make the sheet recalculate continually, so if you have that figured out, my ... | |
| Count Formulas | 9/17/2007 |
| Q: If i have a range of Age How do i Count # of Age in a bracket IE age greater than 70 less than 80 ... A: This took me awhile to remember, but you want to use the sumproduct formula. Say your range of ages ... | |
| Conditional Formatting | 9/14/2007 |
| Q: What i'm trying to do is apply conditional formatting to the first cell in any given row in my ... A: This is a two part solution, depending on whether or not your first cell already has a defined ... | |
| excel 2000 formating | 9/14/2007 |
| Q: I am wanting to format/edit 2 seperate workbooks simultaneously. What I do in one, I would like to ... A: I am not aware of anyway to edit two workbooks simultaneously, but you can edit multiple worksheets ... | |
| Excel - date format | 9/13/2007 |
| Q: Craig - when using the ten-key, I want to enter the numerals 82507 for the date 08/25/2007. What do ... A: Unfortunately, excel has quirks for reading dates. It will assume that your 82507 is the number of ... | |
| Merging Spreadsheets into one document (tabs) | 9/12/2007 |
| Q: I have 4 separate excel spreadsheets saved as different documents. I would like to combine them all ... A: It's been awhile since I've used version '97, but I think you should be able to right-click on the ... | |
| vLOOKUP | 9/11/2007 |
| Q: if i record a macro to do vlookup for example 1000 trades and the next day the volume goes up to ... A: I'm sure there are other ways, but the way I get around this is (in my macro) to copy a column of ... | |
| Excel Pivot tables | 9/11/2007 |
| Q: Please, how can I fill blanks in a multi-level pivot table e.g. ID Gender Response 1 Male A ... A: This is more of a half-solution. Unfortunately, you can't have an active pivot table fill those ... | |
| linking worksheets | 9/11/2007 |
| Q: I have a spreadsheet which has roughly 16 columns: some with text and some with numerical data. My ... A: I would say your best bet is to use conditional formatting. Highlight all of sheet2(or at least the ... | |
| linking worksheets | 9/10/2007 |
| Q: I have a spreadsheet which has roughly 16 columns: some with text and some with numerical data. My ... A: Yes to the part about paste en masse. After you have done this for your first worksheet, sheet2 you ... | |
| linking worksheets | 9/10/2007 |
| Q: I have a spreadsheet which has roughly 16 columns: some with text and some with numerical data. My ... A: What I would do is create a copy of sheet1 and then link all of the cells from sheet1 to sheet2. In ... | |
| Tracking Changes in Excel 2000 | 9/10/2007 |
| Q: Is there a way that I can track changes in Excel across multiple worksheets in the same Excel file? ... A: You can highlight multiple tabs by holding down the Ctrl key (for specific tabs) or the Shift key ... | |
| Scatter Plots | 9/10/2007 |
| Q: I have created a scatter plot chart and some of the information is on top of each other because of ... A: I'm not sure there is a dynamic/interactive way to zoom in on a chart like you are hoping. The best ... | |
| Pivot Tables Fiscal yr vs Calendar yr sorting | 9/9/2007 |
| Q: My office works on a fiscal year - Oct thru Sep. How do I get the months to sort Oct06, Nov06, ... A: Pivot tables are my favorite. Two ways to make this work. One is that you can manually slide the ... | |
| wrap text | 9/7/2007 |
| Q: I am trying to wrap text for an entire column (column I). When I select the entire column, I go to ... A: This may not be the answer you are looking for, or you've already tried it, but... Have you tried ... | |
| Look up a specified value/text in a list, then return the result : YES or NO | 9/7/2007 |
| Q: I have to column of text: Column A & B. I want to check each row of column B containing a part of ... A: Here is your formula (to be placed in cell C2) assuming AAA_Lan is in cell B2: ... | |
| Rotate column | 9/7/2007 |
| Q: How do you rotate a column 180 degrees in excel? We have a time series that we need upside/down. Can ... A: I'd say your best bet is to add a column next to it where you order your numbers 1,2,3,4 etc. This ... | |
| Excel if command | 9/6/2007 |
| Q: How can I multiply an amount column(two columns) by (-1) based on the data in another column? ... A: Here is another approach if you have some cells that may contain "buy" as part of the cell, like ... | |
| Excel if command | 9/5/2007 |
| Q: How can I multiply an amount column(two columns) by (-1) based on the data in another column? ... A: =if($A$1="buy",A3*(-1),A3) Applying the $ in front of A and 1 make each of those references static. ... | |
| Inserting blank cells in Excel | 9/3/2007 |
| Q: I'm hoping you can help... I have a simple spreadsheet with 2 columns of data called x (ColumnA) and ... A: I think the challenge is not in the "" for the blank cell, but not being able to plot a blank value. ... | |
| Concatenate A1:E1, Set "Comma" and "And" | 9/3/2007 |
| Q: A1 = Jack B1 = Jill C1 = Robert D1 = Juan E1 = Monesh I have used concatenate function ... A: I don't have an answer for you just yet, but this question intrigues me. Unfortunately, since I ... | |
| summary | 9/1/2007 |
| Q: Craig: I have a sheet1 with student ID numbers in column A and dates of the student's absence in ... A: Have you tried using a pivot table? As long as your sheet1 has column headers for student ID, ... | |
| How to calculate a formula with addition and division | 8/31/2007 |
| Q: My question is in regard to calculating a formula. Example: field 1 = 300.00 field 2= 200.00 field ... A: I assume you know how to click and drag to copy a formula down or across? It sounds like it may be a ... | |
| Excel Custome Views | 8/30/2007 |
| Q: How can I create a custom view that will apply to all the work sheets. ie I want to hide some ... A: Are you familiar with macros? This one should be fairly simple and a good way to get your feet wet. ... | |
| automatic time into a cell based on another cell having a date input | 8/29/2007 |
| Q: I would really like to automate recording the start and stop time for tasks completed at work, as ... A: You're not dim, macros are probably the most complex part of excel, but once you learn them, they ... | |
| automatic time into a cell based on another cell having a date input | 8/29/2007 |
| Q: I would really like to automate recording the start and stop time for tasks completed at work, as ... A: Here is a macro using Ctrl + j (macros must have a letter as the hotkey) that uses the =NOW() ... | |
| combination and calculation | 8/27/2007 |
| Q: "I have a question dealing with combinations, I have three products and have about 100 accessories ... A: It looks like your best bet would be to use a combination of data validation (to create your pull ... | |
| VLOOKUP in Excel | 8/27/2007 |
| Q: Can you tell me how to set the look up area on the "codes" worksheet when using the following ... A: The "codes" worksheet needs to have the unique ID (vlookup) field as the far left column of the look ... | |
| merge data into excel spreadsheet | 8/22/2007 |
| Q: Is there any way to merge or import data into an excel spreadsheet (data would come from excel ... A: I'd say your best bet is to use the vlookup function. The only thing you need to do is make sure ... | |
| training | 8/19/2007 |
| Q: Sorry for such a general question. I own a small business in Columbus, OH. Where can I receive the ... A: I'm not sure exactly what you mean by Business Software, but if you are looking specifically for ... | |
| COMPLICATED EXCEL FORMULA | 8/19/2007 |
| Q: EXCEL: I’ve searched everywhere to figure this out and would greatly appreciate it if this could be ... A: I understand the idea behind what you're trying to do, and I think adjusting your IF/THEN formula ... | |
| Next sheet and 10 rows down | 8/18/2007 |
| Q: On Sheet1 I have data in rows: COL1 /COL2.. x /etc.. y /etc.. z /etc.. . . On sheet2 I ... A: My suggestion to accomplish this would be to design your calculations on sheet 2 in a horizontal ... | |
| excel 2003 auto-populate multiple columns based on text input | 8/17/2007 |
| Q: how are you? I am using Microsoft Office Professional 2003 with Windows XP, and need help with ... A: I would suggest first creating a separate tab that has two columns, one with the potential input and ... | |
| highlighting adjacent cell | 8/16/2007 |
| Q: I'm trying to create a excel worksheet that highlights the maximum value as well as the text in the ... A: You already figured out the hard part. In your column two, your conditional formatting needs to ... | |
| Excel Formula - calculate Budget to Actual to Plan figures | 8/15/2007 |
| Q: I am looking for a formula or formulas to analyze several data points that relate to forecasts ... A: This may also be a little too simple, but what about calculating the trend as a weighted average? ... | |
| Excel Formula - calculate Budget to Actual to Plan figures | 8/15/2007 |
| Q: I am looking for a formula or formulas to analyze several data points that relate to forecasts ... A: I can think of a couple possibilities, but more clarification may be necessary. I presume you are ... | |
| excel formula | 8/15/2007 |
| Q: Is there a way to make a formula that would only add the value in a cell if a value is in a ... A: Yes, this is the SUMIF function. In your example, the RANGE would be b1:b3. the CRITERIA would be ... | |
| listing by date/total | 8/15/2007 |
| Q: I am tracking calls coming into a business and want to report on how many calls are coming into each ... A: This would be easiest by setting up a simple pivot table. Set up your columns to be DATE and LINE # ... | |
| track hours worked each day. | 8/15/2007 |
| Q: My wife asked me to help set up a spreadsheet that would record the hours she worked every day. ... A: I would set this spreadsheet up with each new day across the top M T W TH F and the times down the ... | |
| Graphs from Pivot Tables | 8/13/2007 |
| Q: I work with groundwater monitoring data - results from multiple wells each monitored for multiple ... A: I'm not sure why the graph functionality is reduced, but I too find it annoying. As for solutions: ... | |
| Creating a custom number format | 8/13/2007 |
| Q: ..I'm trying to format a cell so that any time I type a number in the cell, it will display as ... A: The use of the quote punctuation means excel is expecting a text entry followed by an end quote like ... | |
| Excel's Auto Filter Limitation | 8/13/2007 |
| Q: Craig, It's not a big secret that Excel's Auto Filter has a limitation of 1000 unique items. ... A: Yes, you can trust the B filter. You could also test this by running a COUNTA formula on each of ... | |
| pivot Table - Excel | 8/13/2007 |
| Q: How do I lock/protect a pivot table in Excel 2000? I tried with 'protect sheet', but it doesn't ... A: I borrowed this solution from the microsoft.com page as it looks like your best solution is to ... | |
| Inventory sheet | 8/12/2007 |
| Q: If I have an inventory on sheet 1 and a recipe on sheet 2. Let’s say that each recipe requires 2 lbs ... A: I'm not exactly sure how you would be selecting your recipes, but I do have a fairly simple ... | |
| Excel formula | 8/10/2007 |
| Q: I have two tables of data for customers that I need to combine in one table with a single row for ... A: This solution viability depends on the number of customers you are talking about. If it is not that ... | |
| Purchase orders | 8/10/2007 |
| Q: At work we use purchase orders when buying stuff which we have designed in Excel 2003. What we would ... A: I don't really have an answer for you at this time without a little more information. This also ... | |
| reduce number of rows/columns in excel sheet | 8/10/2007 |
| Q: is there anyway i can reduce the number of rows and columns in an excel sheet? thank you A: I'm not quite sure I understand the question. Are you trying to reduce rows/columns for printing ... | |
| Filters bug me | 8/9/2007 |
| Q: I have a data table with ~2000 rows in it and the AutoFilter does NOT let me select each and every ... A: Unfortunately the AutoFilter has a limit of 1000 unique selections, so this is why you are not ... | |
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Answers by Expert:
- Nathan Head
- Chad Welch
- Philip Buckley
- Tushar Sakhalkar
- Bhaskar
- Amal
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bob Umlas
- Edward H. Mori
- Jessica L.
- Jan Karel Pieterse
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- Craig
- Adelaide carvalho
- Miguel Zapico
- JP
- Douglas M. Smith
- Richard Rost
- Ken Marron
- Victor Lan
- Bill
- Anne Troy
- Claire Blinman
Craig
Top Expert on this page
Expertise
I can answer most general questions, including formatting, shortcuts, pivot tables etc. I have just started using macros, so vba is not my strong suit. I also enjoy explaining the solution at the questioners level of understanding. I am an accountant by trade, so that is my primary area of expertise, but I am willing to tackle just about anything.
Experience
10 years
Education/Credentials
BA - Business '96, CPA '98
©2009 About.com, a part of The New York Times Company. All rights reserved.