Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
0answers
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
2answers
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
1answer
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
0answers
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
0answers
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
0answers
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
6answers
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
1answer
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
3answers
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
0answers
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
1answer
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
0answers
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
3answers
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
4answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
3answers
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
1answer
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
2answers
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
3answers
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 ...