## New answers tagged microsoft-excel

2

With data in columns A and B like:
In C2 enter the array formula:
=IF(ISERROR(MATCH(A2:A11,B2:B11,0)),A2:A11,"")
and copy down. Then in D2 enter the array formula:
=IF(ROW()-ROW(D$2:D$11)+1>ROWS(C$2:C$11)-COUNTBLANK(C$2:C$11),"",INDIRECT(ADDRESS(SMALL((IF(C$2:C$11<>"",ROW(C$2:C$11),ROW()+ROWS(C$2:C$11))),ROW()-ROW(D$2:D$11)+1),COLUMN(C$2:C$...

1

I've seen this occur when you have Filters turned on. If you turn off the Data Filters, the "My data has headers" option should be available.

0

You can use e.g. this formula in Excel:
=AND(SIGN(B2)=SIGN(C2),SIGN(C2)=SIGN(D2))
It simply does what you need, checks whether all the three numbers has the same sign.

0

Use VLOOKUP:
=VLOOKUP(I6,$C170:$G999,5,FALSE)

0

Microsoft Excel has a plugin called PowerQuery.
PowerQuery allows you to import data from your sheet, and run an 'unpivot' operation.
This is a very handy tool. It will take you a bit of time to set it up and get it working the first time, but once you do it will be useful for years and let you do this operation very quickly.
Here are some details on how ...

1

This will iterate as you drag it down.
The first reference B19 will increase by 17 every ten rows.
The second reference A25 will iterate by one, but will jump 8 every ten rows.
=IF(COUNTIF(Power!A:A,INDEX('Item Breakdowns'!B:B,19+(QUOTIENT(ROW(1:1)-1,10)*17))),INDEX('Item Breakdowns'!A:A,ROW(25:25)+(QUOTIENT(ROW(1:1)-1,10)*7)))

0

Instead of tagging column B cells to identify their column A counterparts, this functionality is built into Excel's custom sort.
Highlight both columns, click 'Sort & Filter' > 'Custom Sort...'. In the popup, you can add different sorting levels to do what I think you're trying to do. Sort first by column A, then add a new level to sort by column B.
...

0

By using SUMIF, Here you go your example
Your Example
you can use this formula to an empty column corresponding to phone numbers column
=SUMIF($B$5:$B$14,E5,$C$5:$C$14)

0

I'll assume you're using a more recent version of Excel. To make a line graph of your data in the way you described, copy the data columns for MPH and RPM side-by-side in that order. Then select both columns. Click 'Insert' > 'line graph'. To add a trend line and formula for your data, right click on the newly made graph and click 'add trendline' in the ...

1

Since you are using the SUM() function, there is one other option. SUM() ignores text values, so you could enter the values in Q7 and Q8 as text. Format the cells as Text and enter the numbers with a leading single quote character. In the screenshot below, Excel shows the green warning triangle for numbers stored as text, but the SUM() function only ...

2

If you know that you might want to exclude certain items from the formula, you can prepare for this as follows:
in R1 through R10 enter 1
use the formula: =SUMPRODUCT((R1:R10)*(Q1:Q10))
Now if you want to exclude Q7 and Q8, just set R7 and R8 to zero.

2

You can't omit these cells from being included in the formula without making changes in the formula.
You can't have your cake and eat it.
I suggest you change the formula to exclude the cell explicitly, like
SUM(Q1:Q10)-sum(q7,q8)
and
SUM(E6:N13)-SUM(P6:AJ13)-sum(q7,q8)

0

If you use Notepad++ to add quotes to the beginning of each line, just open the exported csv file, put your cursor at 1st line, 1st column, then click menu Edit/Column Editor..., in field 'Text to insert', enter ", then the beginning of each line will have a quote, then you can use regular expression to search/replace all the tabs.

0

Click on More Colors at the bottom of your menu and select the color you want. You will see that this color is added to a row of blank color boxes and when you close the dialog box it should remain in your color selection dropdown menu.
There are a number of ways to keep this color available, the most appropriate is to make it part of Excel's default ...

0

On the Page Layout tab, click Colors (in the Themes section), then choose Customize Colors. Change any of the Accent colors to the one you desire, then click Save. The change will be reflected in the options under Theme Colors in that document.
Source: Change a theme and make it the default in Word or Excel

2

At first create a new column with this formula:
=LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))
With this formular you can find out how many zero characters are in the String.
In the second step you have to filter your table:
Click on Start
Click on Filter
Filter the new column to show only lines with a specific number of characters

1

You need to bracket the dates with greater than and less than:
=SUMIFS(Spending!C:C,Spending!D:D,C1,Spending!A:A,">=" & DATE(YEAR(D1),MONTH(D1),1),Spending!A:A,"<=" & EOMONTH(D1,0))
For reference Spending sheet:

0

It is possible to query for an Excel workbook that's on SharePoint directly into a local Excel document. However, certain permissions or authentication may be required. Depending on the year of your Excel (I use Excel 2016), you will access this feature through the "Data" tab. It will give you an option for "New Query" and from the list you will select "...

2

The formula would be
=B1 + C1/(24*60)
As Excel denotes time as a decimal based on 24 hours being 1.

0

So a quick formula, This is assuming your data starts in column A, if not adjust your columns accordingly:
=IF($A1<>"",INDEX($C:$C,ROW()+COLUMN(A:A)),"")
Adjust your columns accordingly on the $A1 and $C:$C. Leave the COLUMN(A:A) alone as we are using it as a counter and not an actual reference to Column A.
This would go in the cell right next to ...

-1

Insert a text box that stretches the length of the cells.
Type a row of ******* similar characters to fill the text box.
You can move the text box down the page as you would a ruler or piece of paper on a hard copy.
Delete when done.

2

Using data from Sheet1,
create a new sheet as Sheet2
For Row 1 in cell A1 insert
=Sheet1!A1 & CHAR(10) & Sheet1!A2 & CHAR(10) & Sheet1!A3
Using CHAR(10) is the equivalent of Pressing Alt + Enter
For Row 2 Cell A2 insert
=INDIRECT("Sheet1!A"&(INT(ROW(A1))*3+1)) & CHAR(10) & INDIRECT("Sheet1!A"&(INT(ROW(A1))*3+...

1

Microsoft has released updates that have caused some downloaded files not to open, most notably HTML with .xls extension. The updates in question are:
Windows Update KB3115262 (Excel 2013)
Windows Update KB3115130 (Excel 2010)
I have found several options that can eliminate the problem.
Option 1
Control Panel > Internet Options
Security Tab > Trusted ...

2

"text" in Excel is the same as "string".
You can use Data > Text to Columns to split up a column. If the data is set up with a delimiter, like a comma, semicolon or tab between fields, use the "delimited" option and specify the delimiter.
If the data is structured with the same character length for each column, use the "fixed width" option and set the ...

1

In Excel Options -> Advanced -> Display -> Uncheck "Show all windows in the Taskbar",
Click "OK" to close the menu.
Then go back and select it again (check the option) and click "OK".
Now Alt+Tab should work fine again

1

According to your comments on clarification of the question - "Yes, I need rows with values >=0.1 from multiple columns at a time.", I understand you want to filter rows whose ALL columns are >=0.1, right?
Ideal is still to use array formula, which is similar to @Etheur's but simpler and more straight:
Suppose your data is at A1:K10, in cell L2, type ...

1

If you need a different method you can use sumproduct like the following:
=SUMPRODUCT((B3:B9="Food")*(C3:C9="Groceries")*D3:D9)
Where B3:B9="Food" will give 1 if True
Also C3:C9="Groceries" will give 1 if True
And Sumproduct will add the products 1*1*values in 2015
To compare Index/Match vs Sumifs click Here

0

Is it possible to make a table in Excel with huge cells - extending over 2/3 pages?
Probably not (unless you upgrade your version of Excel).
Excel 2003 can only display 1024 characters in each cell (even though a cell can contain up to 32,767 characters).
Later versions of Excel removed the limitation, and will display cells containing up to 32,767 ...

0

paste it in google spreadsheet, then again copy and paste it in your excel sheet.
It will show as code/text not html value

1

If sh1.Range("A" & RowNum : "O" & RowNum).interior.color = rgb (127,187,199) is the actual line from your macro, you have a typo
sh1.Range("A" & RowNum : "O" & RowNum) should be sh1.Range("A" & RowNum & ":" & "O" & RowNum)
The colon : needs to be concatenated like everything else, since it's part of the range string

0

If you're looking to filter the table so that a row remains visible if any of its data contains a value >=0.1, the most straightforward way is to just add an additional column off to the right with some logic and then filter on that.
Assuming your data starts in A1, here's an array formula placed in column L that would accomplish this. Since it's an array ...

0

The following formula will work, but only if you change some of your formatting.
Formatting changes needed:
The summations cannot happen within the range of cells you want to calculate. So you can switch your formatting to something like this
]1
Once you have your summations outside the main body, the following formula will work just fine. It would be ...

0

In short, there is no way to do this without modifying your chart layouts that isn't better than specifying far more cells than you will ever use. In my example above, I end at cell J500003. Suppose your charts end at row 470. This is not a problem. Empty cells count as 0 and do not affect the sum. If you make the formula arbitrarily high to start with, you ...

0

The following should work
Open the format toolbar (select graph and hit CTRL+1)
Select a point on the graph
on the format toolbar select the fill and line button (the paint can dumping paint)
select the "marker" button
select the "no fill" radio button under the "fill" list and the "no line" radio button under the "border" list

1

What are you expecting/hoping to get?
A pie chart that’s ¾ (6/8) one color and ¼ (2/8) another?
You can’t give it text like that; you have to give it numbers (6 and 2).
Use COUNTIF or an equivalent function on your text column to count the values.

0

I found a solution using VBA. Here is the Code for anyone interested:
Note: I haven't used VBA in years, so my Code might not be very good. Any Suggestions for Changes are more than welcome.
First I get the Startdate and and the Enddate from named Cells
Dim MAnfang As Long
MAnfang = Range("Monatsanfang").Value2
Dim MEnde As Long
MEnde = Range("...

1

Sure, select the Status column (up to the last row) and insert a Pivot Table. Drag the Status field under Row label, and drag the Status field again under Values as well (you'll get Count of Status).
However, it won't count blanks. For that you should see if you can include more columns in your PivotTable so that you have at least one column that is never ...

0

Data for the treemap chart (new in Excel 2016) must be arranged from broad to specific, left to right, each text value in its own column. Read more here.
The treemap charts are not as detailed as the WinDirStat visualisation, though.

1

There are several possibilities:
.slk, like .rtf, was created by Microsoft for exchanging data. However unlike .rtf, there is no published specification and it cannot handle Unicode.
.dif does have a published specification. Like .slk, it cannot handle Unicode.
.csv files are compatible with basically everything. However unlike .rtf, they only store ...

1

With data in column A, in B1 enter:
=LEFT(A1,3) & TEXT(--MID(A1,4,9999),"000")
and copy down:

3

If you already have the numbers and the 'FL-' text munched together, you need to split them first, the formula would be =VALUE(MID(cell,4,99)); if they are separately accessible already, you can skip that step.
Next step is to format them for three digits: =TEXT(cell,"000").
Third is concatenating the two pieces back together with &.
All together: =...

0

This is only a partial answer to illustrate how to pull the second set of text out.
The following in some other cell on the same row such as b2 will remove the second occurance by replacing the second instance with "" using a substitute formula
=SUBSTITUTE(C2,"Clouds: Gray;","",2)
to get this in A2, you would need to use a paste special value.
however, I ...

0

The SUBSTITUTE function
SUBSTITUTE( text, old_text, new_text, [nth_appearance] )
has a parameter called nth_appearance which reads
Optional. It is the nth appearance of old_text that you wish to replace
So your formula is
=SUBSTITUTE(A1,"Clouds: Gray;",,2)

0

The message will only show up if you try to edit the file while someone is viewing it. If you don't save the changes then no window will pop up. Everybody should be getting the message if there is an instance of it somewhere on the network if changes are attempted to be made. You could possibly work around this by making sure the file is always open ...

0

I had the same problem just before I've typed this. And here is what I've found.
When you press wrap text the cells height is automatically adjusted. Also its height is not calculated as we expected. If there is very little space left on the last row of the text, Excel decides that the cell needs one more row... even if there is not a single letter on it.
...

1

Ideal:
Adds a column as key to be looked-up, the key is concatenation of ID and SYS;
Uses countif to check existence of ID+"A" and ID+"B", puts the result into bitmap - bit0 indicates existence of B, bit1 indicates existence of A, resulting in following map: 0 - None, 1 - B, 2 - A, 3 - Both;
Uses lookup to translate the value 0, 1, 2, 3 into expected text ...

0

The format 0"Y" 00/12"M" (the second column) or 0"Y" ?0/12"M" (the third column) can have result similar to your expectation but not the exactly one. See following examples:
4.01 4Y 00/12M 4Y 0/12M
3.27 3Y 03/12M 3Y 3/12M
2.92 2Y 11/12M 2Y 11/12M
1.99 2Y 00/12M 2Y 0/12M
0.45 0Y 05/12M 0Y 5/12M

Top 50 recent answers are included