# Questions tagged [microsoft-excel]

A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

**0**

votes

**0**answers

36 views

### Excel double click autofill not working on non-empty merged cells

In Excel (latest 2016 Professional Plus version 1811 on Windows 10) you can copy the formula in a selected cell (cells) down by double clicking on the dot in the bottom right corner. This also works ...

**0**

votes

**2**answers

37 views

### Formula to search for an integer value in a range (2d grid)

I have a grid of cells/range/table containing integer values, e.g., A1 to E10.
I would like a formula to search for the first occurrence of the value X and return its location within the A1:E10 range....

**1**

vote

**1**answer

54 views

### How to re-select selected cells in Excel

Let's say I've selected a few cells in the sheet:
In particular: A3, C3, B5, D6, C7, E8, A9, C9, E9
But then I've accidentally clicked away to a different cell (i.e. E1) and the whole selection is ...

**0**

votes

**0**answers

26 views

### Excel Conditional Formating From Adjacent Cells

Basically I am trying to have my columns mimic font and background colors of one entry cell. The sheet is only 6 columns wide.
ie;
A22=date, B22=user input/auto populate, C22=user input (drop down), ...

**0**

votes

**0**answers

30 views

### Automate clicking download CSV button on webpage and saving then importing as worksheet in current workbook

I'm trying to automate a manual process which is as follows:
1. Open webpage (https://trans-logistics.amazon.com/fmc/execution/JA3Nk--CPTs_(24_Hour_Pool)) (don't bother clicking - link only works ...

**-1**

votes

**0**answers

63 views

### Number of months between two dates (first month doesn’t count after the 15th)

I am trying to find a formula for Excel.
Basically I have two dates
and I need to know the number of months between them (inclusive).
But to add complexity, I need a rule in there
that if the ...

**0**

votes

**2**answers

67 views

### Delete rows on different worksheets (No loop)

I'm a beginner is coding, and im trying to figure something out.
I want to delete rows on different worksheets defined in an inputbox.
My code is only working on sheet 1. And not on Sheet 2. Can ...

**0**

votes

**1**answer

32 views

### Compare multiple columns in two sheets to get a value

I have limited working knowledge of Excel VBA macros.
I have two sheets called "Summary" and "Data"
The Summary sheet has fixed rows and columns.
The Data sheet has values in it.
I need to compare ...

**0**

votes

**0**answers

22 views

### Case sensitive formula in excel

I’ve been playing with this formula because I couldn’t get the original syntax to work and it’s nearly doing what I would like. Please can you help me make it case insensitive?
Here is the data and ...

**0**

votes

**1**answer

42 views

### Multiply cells in Excel if a cell has a more recent date

I am currently building an Excel worksheet for managing inventory in a large kitchen and I would like to find the value of the inventory for the most recent day.
Inventory is taken four days a week ...

**0**

votes

**1**answer

31 views

### Excel, Copy sheet with formulas to new workbook, replace existing reference

I have a sheet with many formulas. I am trying to copy them into a new workbook however the formulas reference the previous workbook.
How can I replace the previous workbook reference in these ...

**0**

votes

**1**answer

279 views

### If cell equals a certain value, automatically return whatever is in the cell 2 columns over in the same row

I'm looking for help with creating a formula. If a cell equals a certain value, how do I return whatever is in the cell 2 columns over in the same row? For example, if A=VALUE, Return C from the same ...

**1**

vote

**0**answers

22 views

### Excel VBA VLOOKUP in Colunm

I have a sheet for monthly report. When daily report got, I want a VLOOKUP formula to be entered under the column where the date exist.
I have already set the sheets up with the VLOOKUP formulas ...

**1**

vote

**1**answer

38 views

### Return the largest number of occurences/repetitions/duplicates

If you have a row of values, and some of them are duplicates, how can you return the largest number of repetitions?
In other words, is there a way for cell B2 below to run just one swift command ...

**2**

votes

**1**answer

29 views

### How do I prevent Excel from asking if I'm sure I want to save in ODS format each time?

How do I prevent Excel from asking if I'm sure I want to save in another format each time?
I tried to change one of the options, but could not find one about saving prompts.

**0**

votes

**0**answers

19 views

### Excel pre-defined Styles keep coming back

MS Excel, MS Home and Business 2016.
Looking at the ribbon, Home Tab, "Styles box", on a full width screen.
I see "Conditional Formatting", "Format as Table" and then a bunch of pre-defined Styles ...

**2**

votes

**1**answer

17 views

### Excel COUNTIFS with multiple criteria

I would like to use a COUNTIF formula with two criteria.
This is the formula I have now.
It's obviously wrong, but I think it shows what I want.
=COUNTIF($D$2:$D$1008; P36 & $B$2:$B$1008; P21)

**0**

votes

**1**answer

34 views

### Excel Generate Sequence Using Specified Format

How to generate sequence numbers from 00.0000 to 99.9999 in excel?
E.g.
00.0000
01.0001
01.0002
----
99.9999
Update
Sub test()
[A1:A100000000] = [index((row(1:10000000)-1)/100000,)]
[A1:...

**0**

votes

**2**answers

20 views

### Grouping function in excel

I'm fairly new to Excel and can't find how to formulate my question, so sorry if this has been asked before.
The data I have contains information on literacy, employment and income per hour for ...

**0**

votes

**0**answers

19 views

### Excel Pivot - Group by year and week

I can group data by YEAR. I can also group it by WEEK. However, I can't seem to group it by YEAR and WEEK.
I used https://www.extendoffice.com/documents/excel/1874-excel-pivot-table-group-by-week....

**2**

votes

**2**answers

47 views

### Multiple Search Formula in Excel [duplicate]

I am trying to find a formula which will search one cell for specific words.
I would like the outcome of the formula to be words I have selected.
The example of this I have is if one cell shows the ...

**-1**

votes

**1**answer

53 views

### Excel multiple FIND formula

I am no expert with excel so any help is appreciated!
I am trying to make one cell do multiple searches within another.
I know that to search one cell for a specific word you would use
=FIND(Blue,...

**2**

votes

**1**answer

40 views

### Excel: How to automatically store data gathered from a website scrape?

I have set my workbook to retrieve data from a website which gives prices of stocks.
It refreshes hourly. I then have some cells that make calculations based on the stock prices using a simple ...

**1**

vote

**1**answer

85 views

### What would be the mathematical equivalent of this excel formula? =PERCENTRANK()

I need to create a SIMS (School Information Management System) assessment sheet that does the same calculation as this Excel =PERCENTRANK() function.
Its full components in Excel are:
=PERCENTRANK(...

**0**

votes

**1**answer

39 views

### Excel Transpose Certain JSON Fields

I'm pulling data into Excel via a webhook (JSON). It appears in Power Query Editor like this:
| name | groupName | key | value |
| host1 | group1 | tag1 | val1 |
| host1 | group1 | ...

**2**

votes

**1**answer

279 views

### According to Excel, 4^3^2 = 4096. I this correct?

According to Excel,
4^3^2 = 4096
However, according to what I learnt at school, first I have to compute 3^2 = 9 and then 4^9 = 262144.
Who is right, Excel of my former teacher?

**1**

vote

**1**answer

38 views

### Excel PivotTable Remove Column Labels

How do I remove the column labels so that I can create a PivotTable like the second example screenshot?
| title | group | tagKey | tagValue |
| example1 | groupA | firstTag | foo |
| ...

**0**

votes

**1**answer

35 views

### Excel Freeze Panes

In past versions of Excel, I could freeze panes vertically or horizontally by moving my cursor to the edge of the spreadsheet until a small icon like this appeared:
<-||->
I could then drag the ...

**0**

votes

**0**answers

20 views

### How to display data in columns that contains values when filtered by name

I have employee data in a worksheet. I would like to be able to filter out employee column by name and it will only display the data that is not blank while retaining column titles. For instance if I ...

**1**

vote

**1**answer

20 views

### PowerPivot - How do I establish a relationship between a table's columns to another table?

I'm beginning my first PowerPivot, but I'm starting to think my setup is completely wrong, or maybe PowerPivot isn't the right approach.
I have two tables: A huge table of wages per week for multiple ...

**1**

vote

**0**answers

16 views

### Excel PowerQuery cannot find SharePoint files after a certain date

Background
I have a number of different Excel files which connect to a number of different SharePoint folders for a variety of purposes. Each week, new data is exported from our client systems and ...

**2**

votes

**6**answers

2k views

### Return a value if another cell has one of many values

I'm not sure if the IF function is even the right one to start, but the question is:
I have a cell Y8 and it displays different numbers:
0,5,10,15,20,35,50
I need to type a formula that returns a ...

**-1**

votes

**1**answer

36 views

### Out of order rows in Excel

If I have a sequence of rows such as:
1
2
3
7
8
4
5
6
9
How can I identify the groups of ordered rows like that:
1 1
2 1
3 1
7 2
8 2
4 3
5 3
6 3
9 4
My sequence could ...

**0**

votes

**3**answers

42 views

### How do you return the latest value in an Excel list with blank cells in the middle?

Tried a couple of formulae to try and get the most recent value in a row as per the below but without success - only seems to work when the data has no blanks??

**1**

vote

**0**answers

37 views

### Import and merge 2 CSV files using cmdlets in PowerShell [closed]

I am trying to import and merge columns from 2 CSV file into one using Powershell.
Please look at my code and advise.
I think that it is right, but I am not sure about referencing different columns ...

**0**

votes

**1**answer

64 views

### Need mathematical equivalent of this excel formula PMT() [duplicate]

I need help with the math equivalent for excel formula PMT(). There is an answer @ below thread... BUT nothing is mentioned with regards to the option parameters of PMT() i.e. FV and Type. Can anyone ...

**0**

votes

**0**answers

29 views

### Using COUNTIFS to count times within next 6 hours, next 12 hours, etc

I currently have COUNTIFS set up to count times in specific 6 hour time frames, for instance 12:00-18:00, but would like to change it to 6 hour increments FROM the CURRENT time: 6, 12, 18, and 24.
...

**0**

votes

**3**answers

56 views

### Easier way to calcuate average change?

I have a product that is getting sent out to the field as it is manufactured and used daily, the use rate changes from day to day based on how the job is going.
I am trying to calculate the average ...

**0**

votes

**4**answers

103 views

### Remove text up to (inclusive) second to last period (or `://` if only one period)

I have the following data:
http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com
What I'm trying to do is get the text up to the second to last period. for ...

**-1**

votes

**1**answer

34 views

### Filter time range

We are trying to get a report as to how many tickets where logged out of hours.
Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which ...

**0**

votes

**1**answer

16 views

### How to chart monthly sums with daily data?

So, I have a table a bit like this:
Date Total A B
01/09/2017 497 262 235
02/09/2017 86 39 47
03/09/2017 118 55 63
04/09/2017 812 404 408
05/09/2017 329 155 174
...

**1**

vote

**1**answer

18 views

### Stop Excel 365 from automatically building a query on import

How can I stop Excel 365 from automatically building a query when I import a external data file? I simply want the data in columns without active headers or other formatting. I'm looking for an import ...

**0**

votes

**0**answers

19 views

### How to auto fill a column with same data in blank cells every time the data changes to another sequence

I am needing help figuring out how to auto fill data into blank cells in a column that does not follow any particular pattern. The number of blank cells to be filled will vary. I do not want the ...

**0**

votes

**1**answer

45 views

### how to run a faster Excel or how to free up RAM to avoid Excel freeze or slow response

I frequently need to run operations in big Excel tables (e.g. 100000 rows x 1000 columns - file size >100 MB) and likely due to the file size, when I try to do one calculation across the whole table, ...

**0**

votes

**1**answer

43 views

### Summing up numbers from multiple columns (by Sumproduct?)

I'd like to sum up numbers from different columns which have the same titles, but not all of them have all the dates.
The Pivot tables involved are very complicated so I can't just merge them.
Is ...

**0**

votes

**2**answers

27 views

### Count logical values

I have a column with 5 logical values: 3 trues and 2 falses. I want to count the number of trues in B4:B8 and output it in B10. How can I do this:

**0**

votes

**3**answers

217 views

### If a cell has a certain value XXX, how do I delete the rows that don't have the value XXX?

In the posted example, if for Jane and David I'm only interested in rows with "Total" in column B, how do I remove the rows that do not contain "Total" in column B. I would like to apply this ...

**0**

votes

**1**answer

25 views

### select previous row of particular event in excel

I am current working on a problem.. where i need previous event of applaunch in excel
Input
notification_dismiss
notification_dismiss
notification_dismiss
AppLaunch
...

**-1**

votes

**2**answers

43 views

### excel array formula: split csv content [closed]

Suppose in Excel cell A1 there's content "a,b,c,d,e",
is there a way to write an array formula, so that in B1:B5 will show the split result, i.e. B1 = a, B2 = b, B3 = c, B4 = d, B5 = e?

**0**

votes

**3**answers

43 views

### Select 1st day of each user id in excel

Trying to figure out away to get the expected output from the id and date columns. How do i select 1st days of each user id .. the formula i tried is this IF(A2=A1,(IF(B2>B1,FALSE))) but not getting ...