## Hot answers tagged microsoft-excel-2010

3

The formula does not have to be that complicated. Keep in mind that conditional formatting formulas can use relative references. When the format is applied to another cell, the references will be relative to the current cell.
Select cell M10 and enter the conditional format using this formula:
=OR(M10<J10,M10<K10,M10<L10)
Note that there are no ...

3

You have to type the first 2 or 3 numbers in and then select all of them and then drag down. eg. Type 2 4 and then drag and you will get 2 4 6 8 10 etc...

2

Type your “1”.
Drag the square (I believe that it’s called the “fill handle”)
with the right mouse button.
When you release the button, you’ll get this menu:
Select “Series…”. You’ll get this dialog box:
Set “Step value” to 2, and there you go:
...

2

So you have
=COUNTIFS('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],">="&B1,'S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],"<="&B2)
Which is basically count how many rows in the table are both greater than or equal to B1 AND less than or equal to B2. This would be the same as:
=SUMPRODUCT(('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI ...

2

Open Excel, open your workbook, hit Alt-F11, start coding your VBA. :)
I'd suggest editing the Worksheet's 'Change' method and assign a name to 'ActiveSheet.Name'.
ie: ActiveSheet.Name = "YourName"
That way any time the sheet is changed, the name (which is what shows on the tab) will update to "Your name".
If you want to make it more dynamic, then ...

2

Provided you can always count on ) (right parenthesis and space) being a delimiter in your data, you can use find and replace to change those two characters to an unused character such as ~. Then use Text to Columns with ~ as the delimiter.
If that option is unavailable, you can use the LEFT, RIGHT, and FIND functions to extract the right information.

2

Vlookup does exactly this. Put this formula in B1 on sheet1 -
=Vlookup(A1,Sheet2!$A:$B,2,False)

2

First change the extension of docx to zip (or open the file otherwise). The charts are inside the word\charts folder. The source Excel files are inside the word\embeddings, and can be converted to CSV.

2

I would use the Power Query Add-In for Excel to tackle this. It can handle appending results from a SQL query to an existing table.
http://office.microsoft.com/en-au/excel-help/append-queries-HA104149760.aspx?CTT=5&origin=HA103993872
If you store the data in the Excel Data Model format (not in an Excel Table), the data is highly compressed - there are ...

1

I think this may depend on what shared file store you are using.
Certainly 2010 does do what you have suggested happened for 2003. If one person has the file open, the second is told that it is open and offered read-only. They should also be offered that they are notified when the original user closes the file. When that happens, Excel offers to reload the ...

1

Open an empty spreadsheet and insert an image.
Save the spreadsheet and for example we name it spreadsheet1.xlsx
Close spreadsheet1.xlsx
Open another spreadsheet, save it and name it spreadsheet2.xlsx
Now, open spreadsheet1.xlsx
Press the save button before copying/moving the tab/sheet.
Copy or move the sheet/tab on spreadsheet1.xlsx that contains the image ...

1

There is a weird bug in Excel. I'll explain here how to avoid (not fix, just avoid) it, and maybe it will fixed soon by MS Office programmers. Maybe it even WAS fixed in Excel 2013, I did not open it yet.
So, this is the problem.
The maximum length of the text cell is 32767, and it's OK.
The maximum length of the number cell is 255, and it's OK.
But if ...

1

The way Excel handles split functionality and unique or distinct functions is a little unwieldy: split is done via "text to columns", like in MS Word, and unique/distinct is done by advanced filtering "unique values only" into a new column.
Instead you could try fixing this in Google Sheets with the following formula:
Cell A1: Smith, Miller, Patty, Smith, ...

1

I would convert your range to a Table (Insert Ribbon, Table). In that format, the cell references in formulas will tend to be structured references, using the column name (from the heading row).
http://office.microsoft.com/en-au/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx
I noticed you mentioned row names, so you may have to ...

1

Absolutely! Use names. Go to cell $GX19, press CTRL-F3, choose add a named range, and type Income as the name. Whenever you refer to GX19 from now on it will use the named range instead. You'll have to replace your existing forumulae, I'd do this one by one just by standard search and replace in formulae (CTRL-H).

1

I would edit the formula in the first (non-heading) row of the table. I would select each cell reference and then select the relevant column - that will replace the cell reference with a column reference.
This is a bit painful to do, although there are nice visual guides of which reference points where.
Once you have done this in one row, that change will ...

1

If the date you mention is always the first of the month, then this one will also work:
=INT((WEEKDAY(A1-1)+EOMONTH(A1,0)-A1)/7)

1

If you want to make it accurate you can try out the formula
=(YEAR(E2)-YEAR(D2))*12+MONTH(E2)-MONTH(D2)-(DAY(E2)<DAY(D2))
the formula should be self explanatory except the last part -(DAY(E2)<DAY(D2)), which subtracts one month if it is not a complete month.

1

You can create a custom list of values. Go to the Excel Options > Advanced and scroll down to find the button Edit Custom Lists.
Enter the list values into the box for new lists, each value on a new row. You can also import lists from a text file. In the other box you can see that the month names are also custom lists that come as default Excel settings. ...

1

Create a dummy sheet.
Link it to the Data Set sheet as follows:
Click in cell A1, and either
type ='Data Set'!A1, or
type =, click on the “Data Set” tab, and click in cell A1 on that sheet
and then type Enter or click on the checkmark to the left of the Formula Bar.
Click in the Name Box (to the left of the Formula Bar)
and type a range that covers ...

1

You can use the DATEDIF function to calculate this. Assuming the dates were in the A and B columns the function would be entered as follows:
= DATEDIF (A1, B1, "D")
A1 - first date you want to compare.
B1 - second date you want to compare.
"D" - displays result in days. other formats are Y, M, YM, YD, and MD.
Make sure you format columns A and B with ...

1

DATEDIF is a depricated and undocumented function and may cause problems in use.
Luckily there is a very simple way to do this:
1 - Create a column next to your two columns and add to each cell this formula:
=[End Date]-[Start Date]
2 - Sum up the column

1

Usually, you would use a VLOOKUP, but the index and the values to be pulled are in the wrong order, in that case, use INDEX and MATCH:
=INDEX(A:A, MATCH(C1, B:B, 0))
Find the row in which C1 appears in column B, then from column A, take the value in the row that you just found.
So, for the first one, it looks for Y in column B, finds in in row 2, then ...

1

If you're formula is referring to adjacent cells and and they are consistent then you should just be able to copy and paste the formula and it will adjust the formula itself as you move across the columns. ie. your first equation would be in cell J1 referring to cells J & K. Copy that equation and paste into cell K1 and the equation will adjust to refer ...

1

(Note: Instructions and screenshot from Excel 2013 but should apply to Excel 2010)
Let's assume the cell with your formula is C4 and the "two other cells" are C5 and C6.
Enter your formula in cell C4
With cell C4 selected, go to Conditional Formatting -> New rule...
Select Use a formula to determine which cells to format
Enter the formula as shown below
...

1

When pasting the data into the spreadsheet containing the COUNTIFS formula, right click on the cell where you want to paste and select "Paste Special..." as in the screenshot below.
In the settings box, select "Values" and click OK (screenshot below). This will paste the copied values without changing the formatting of the destination cells.
If you are ...

1

On the right end of the home ribbon there is button labelled "Find & Select" which opens a dropdown. The bottom option of the dropdown is "Selection Pane" which opens a sidebar listing all the comments, shapes etc for the whole file, so you can locate and delete anything you don't need.
(I'm using Excel 2013 but I think it is the same in 2010).
...

1

Here is one solution; discuss:
X Y1 Y2
4015188 7361762
4020374 9581526
4022289 10649655
4023878 11223132
4025512 12035416
4028600 13408580
4030353 14233259
689673 10918805
691097 11241203
691632 11510570
692105 11779503
692555 12044321
693475 ...

1

To see the whole group of cells that are linked into a single array formula:
Click a cell that contains an array formula that calculates multiple results.
On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.
In the Go To Special dialog box, click Current array.
This is directly copied from ...

1

You could use the match function to see if the number is in your list. If the match function doesn't find an exact match it returns a n/a. Using this fact we can flag cells as either being in the list or not. Once you have a flag filter the rows and delete all the ones that are in your list.
=IF(ISNA(MATCH(A2,$F$2:$F$3,0)),"Not in List","In list")

Only top voted, non community-wiki answers of a minimum length are eligible