Questions tagged [microsoft-excel-365]
Use this tag for questions specifically about the 365 version of Microsoft Excel; otherwise use [microsoft-excel].
221
questions
2
votes
1
answer
26
views
How to select a repeating pattern of non-adjacent cells in Microsoft Excel?
I have an MS Excel spreadsheet having 603 records, each record having 27 rows and 4 columns for a total of 16,281 rows. My task is to create a line chart of the data (shock number from 1 to 603) in ...
- 23
0
votes
0
answers
31
views
How do I fix Excel instability after using PowerQuery?
I've been working on some VBA code to import and parse PDF files using PowerQuery. The code itself is working fine, but after some column-related errors (I had more columns defined than some of the ...
- 1
0
votes
2
answers
72
views
How to include a variable within M language PowerQuery
In Excel365 I am trying to build a query that includes some POST data, and I have it all working until I try to make those values variables that come from named cells in my Workbook. In the example ...
- 109
0
votes
0
answers
71
views
How to wait for Web Query to complete in Excel VBA Macro
I have an Excel file that uses an API to lookup some data from a website. It is built using the standard "Get Data from Web" built in PowerQuery feature of Excel. I run a macro to update ...
- 109
0
votes
0
answers
29
views
Deleting an image from two separate worksheets
I have an image on two worksheets within the same workbook. One sheet is called "_SETUP" and the other sheet is "Layout1". It all seems to be working as long as there really is ...
- 109
0
votes
1
answer
81
views
Conditional statement to skip data query
I have a workbook that uses data sources from web (XML query) to pull some info in. The server will return an error if I send a "0" or "blank" for one value, so I am trying to ...
- 109
2
votes
1
answer
973
views
How to directly open Office attachments in "Try the New Outlook"?
When trying the new Outlook for desktop, each Office attachment has 3 options (with a little triangle):
Preview
Open in Word/Excel
Download
If I choose option 2 it just pops up a message to purchase ...
- 885
0
votes
2
answers
60
views
How to sum values in a row if you want to use the last non-null value in the column
Excel 365
Suppose I have the excel table as below:
Date
Bank Account 1
Bank Account 2
Total
01/01/2023
£100
05/01/2023
£678
09/01/2023
£100
22/01/2023
£821
The Bank Account 1 & 2 columns ...
- 103
0
votes
0
answers
61
views
Excel HSTACK function disappeared, whats the cause how to resolve?
Over the past month or so I've been using the excel HSTACK function to join columns together and create an output table.
I changed a computer, in the morning on the new computer I was using HSTACK in ...
- 1,035
0
votes
0
answers
21
views
Cells number format breaks in one column only if multiple cells are moved
I have a table where I keep track of best times & corresponding dates in pairs of cells. Time on top with the date below it, stored in range [B3:E10]. The time values are entered into the formula ...
- 1
0
votes
2
answers
92
views
Calculate when volume counts are cleared based on daily amount received and daily amount processed
I have this data table containing the number of returns per day (Ret Volume) and the number of returns that have been processed per day (Volume Cleared).
What I'm after is the date displayed on the ...
1
vote
1
answer
32
views
Single SUMIFS and COUNTIFS work, but two SUMIFS and two COUNTIFS do not work
I'm exporting data for a manufacturing environment and importing the CSV into Excel. This data refreshes every 5 minutes.
On the first worksheet, I'm able to calculate efficiency % properly (N:N) with ...
- 11
0
votes
0
answers
55
views
How do I trigger multiple cells to update?
I'm using openpyxl v1.7.0 (its the only version compatible with our software, it cannot be updated). When I write to cells using the script, it erases all formatting. So, I use the format painter to ...
- 11
0
votes
0
answers
11
views
Excel 365 Enterprise: Chains of External References
Here is my situation:
I have a base spreadsheet called "data" which does not reference any other spreadsheets.
I have 10 intermediate spreadsheets, each with lots of external links to "...
- 136
5
votes
1
answer
1k
views
Excel Needs Key For Microsoft 365 Family Subscription
I have had a Microsoft 365 Family subscription for many years. It will renew on Dec-04-2024. So its active.
I just purchased a new laptop and successfully logged into the MS 365 subscription on that ...
- 1,101
0
votes
0
answers
515
views
How to get the username of the current Office 365 user in a shared document?
I am aware that you can use a VBA function to return the currently logged in user's username using e.g. Environ$("UserName") but how do I get the office 365 username, especially when using a ...
- 3,114
2
votes
2
answers
78
views
How to reference other Excel workbooks without Excel constantly having me re-select the file?
I have a bunch of (~100) Excel files, plus one "master" Excel file that is supposed to gather data from all of the other Excel files.
Based upon various websites, I have already found out ...
- 820
-3
votes
1
answer
123
views
Excel 365 HYPERLINK and converting a column to active links
I have a column of web addresses, some as abc.com and others as https://def.com. How do I go about converting the entire column to active hyperlinks? The abc.com seems to require the https:// in front ...
- 1
0
votes
0
answers
53
views
Can you specify a named-range based on a rule in Excel?
For instance, I have a dataset that has many named ranges in it, but I find that the range gets out of sync when I add rows below it.
If a column a row (for instance, id) starts with a specific number ...
- 21.9k
0
votes
0
answers
70
views
How to copy the format of locked cells in Excel?
after the latest update of the Microsoft 365 package to the version 16.0.16227.20202 (32-bit), I am no longer able to copy paste the format from and to locked cells. In the previous version (16.0....
- 93
0
votes
1
answer
249
views
Permanent PivotTable filter
Is there a way to make a PivotTable filter permanent?
I.e., prevent or discourage users from removing a filter from a PivotTable, since bringing in that intentionally excluded data would cause ...
- 137
0
votes
1
answer
248
views
How can I match two different data columns in excel based on date?
I have two different sheets, each with two columns.
In Sheet 1, Column A lists every date over the last two years, and Column B lists average humidity measurements for those dates.
In Sheet 2, Column ...
0
votes
1
answer
59
views
is it possible to logical AND two values from two different columns in COUNTIFS?
I have the following formula:
=COUNTIFS(Transactions2023[Result], "Win", Transactions2023[League], "NBA", Transactions2023[Date], "<=" & "4/11/2023")
I ...
- 113
0
votes
1
answer
90
views
In Excel, how do I give a "TRUE" row (column) filter to FILTER() for a multi-column (row) array
In Excel, conditionally filtering rows in a single column array is simple. Something like the following will filter the array only if condition is met:
=FILTER(A1:A10,IF(condition,filter,TRUE))
...
- 422
1
vote
1
answer
817
views
How do I recover an old version of an excel file?
I spent an enormous amount of time working on multiple sheets in a single Excel file. Now, it's gone and just has an old version. I'm not sure why it didn't get saved (I typically save things way more ...
- 111
1
vote
1
answer
89
views
When can you drag a sequence in Excel and when not?
Yesterday, I needed to make a list of multiples of 68, so I wrote the formulae =1*68, =2*68 and =3*68 and I dragged down. To my surprise, Excel just repeated =1*68, =2*68 and =3*68.
However, if I ...
- 2,071
4
votes
1
answer
61
views
Why this apparent orthogonality in Excel's INDEX() syntax?
TL;DR
In Excel 365:
Why doesn't a 0 in the second argument of INDEX(A1#,0,{1,3}) return all rows, when it does in each of INDEX(A1#,0,1) and INDEX(A1#,0,3)?
And is there a canonical way of getting it ...
- 422
0
votes
0
answers
62
views
Paste formatted SQL queries from text file into Excel (one Excel row for each query)
I have a .TXT text file that has formatted SQL queries in it.
--The corresponding Excel row ID is: 2
select
main.active_transportation.objectid,
main.active_transportation.type,
main....
- 137
1
vote
1
answer
200
views
Use Column1 as column headers, use Column2 as rows
I have a table in Excel that I've used in a PowerQuery query:
I want to pivot the table so that:
Column1 is used as column headers
Column2 is used as rows
Like this:
2 ...
- 137
0
votes
0
answers
984
views
don't want to automatically add "@" symbol in excel formula
I am adding a formula using VBA in a excel cell and excel automatically adding a "@" symbol after the "=" sign.
this is the vba code.
It is putting the whole string as formula but ...
- 131
0
votes
1
answer
67
views
Replace duplicates in-place (Excel or Google Sheets)
Is there in-built functionality to remove duplicates in-place in Excel (365) or Google Sheets across several columns? For example, consider this set of data:
The goal is to select the entire data ...
- 4,565
0
votes
0
answers
143
views
Why doesn't my Excel 365 does not cover the "LET()" worksheet function?
My Excel account information shows me I'm working with Office 365:
However, the LET() worksheet function is not covered, as you can see:
What do I need to do in order to get this function to work? (...
- 2,071
0
votes
0
answers
25
views
Excel Pivot Table Display Details of Existing Row
I have some data that looks like this (example drastically reduced in size). Two "Top Level" parts that contain some "Materials". Two of those materials are used in both the top ...
0
votes
0
answers
61
views
How to set up custom design fonts and colors in MacOS Excel 365?
In Windows apps for Office 365 (incl. Word, Powerpoint and Excel), users can configure custom design themes with a color set and a font set (for headings and body) in either the Page Layout or the ...
- 281
0
votes
0
answers
609
views
Find if a cell contains a word from a list, including words that might be mispelled
My question is similar to this question posted previously. My data is organized in the following manner:
Column A (Outcome) - contains user entered text describing an outcome. This can be of varying ...
- 1
0
votes
2
answers
63
views
Misleading data format Excel .csv
I am generating a .csv file with data like the example below:
TIME,LUX
18:00:00,30
18:00:01,30
18:00:02,30
18:00:03,30
18:00:04,30
18:00:05,30
18:00:06,30
18:00:07,30
18:00:08,30
18:00:09,30
18:00:10,...
0
votes
1
answer
118
views
Can you change the Excel Autorecovery location for just specific workbooks?
I have some Excel files on my computer's hard drive that are not particularly important, so the default Autorecovery path %AppData%\Microsoft\Excel (on C: drive) is just fine. I have other Excel files ...
- 335
0
votes
1
answer
37
views
How do I find & delete multiple rows in excel 365 based on the cell text
I have a spreadsheet of company addresses. Each company is listed in a row and has a code assigned(text) in column A. I want to efficiently find multiple rows based on their company code(the text in ...
- 1
0
votes
0
answers
99
views
How to change default Table Style for imported data in Excel 365
I don't like this Table Style that Excel 365 uses when importing data.
Dafault Table Style
Is there any way to change it? I tried to set different Table Style as default and save both Book and Sheet ...
- 1
1
vote
2
answers
214
views
Excel: Create truly blank cells from formula so that they wont show up in a pivot table?
So I've had a look at similar answers but they're not working for me. Basically I have a lot of related charts of monthly data, and I'm wanting to plot them on a chart that only shows up the current ...
- 11
1
vote
0
answers
394
views
Insert high quality image to MS excel web
After adding image to excel web, the image resolution is reduced. How do I add image without reducing image resolution?
- 11
0
votes
2
answers
85
views
How do I convert a custom function to handle spill arrays (excel)
I need a function that produces a list of sheet names, I already have one that can turn sheet numbers into sheet names but it doesnt handle arrays
The function I would like to convert is
Function ...
- 53
0
votes
1
answer
52
views
IF Function Not Populating Correct Data in Excel 365
I am completely stumped as to why this formula is not working properly. To provide backstory, I am trying to randomize a DMS grid coordinate, for example: 2339**01**N05005**51**W. I only want to ...
0
votes
2
answers
1k
views
How to drag a cell reference formula down a column, but move the cell reference across a row
I am trying to drag a cell reference formula down a column, but have the row value increase as the formula is dragged down.
For instance, in the shot below, cell B3 has the formula "=I3". I ...
1
vote
1
answer
186
views
Getting unique items from comma separated lists in Excel
I have the following setup (which is shared by a number of users through office 365 and changed frequently):
I need to collect all unique items from a number of comma-separated lists in a range and ...
- 13
0
votes
2
answers
58
views
Excel formula gives unexpected result
Each year I create a workbook within which there are several worksheets corresponding to each month of the year. I copy and paste the same table with formulas into each new month as the year ...
- 103
0
votes
1
answer
452
views
How to extract words from one list that (partially) match another in excel?
Column A contains a list of product names (like "basil 5kg package" and "pineapple frozen 2kg") and column B contains a list of generic names (like "basil" and "...
- 13
1
vote
2
answers
1k
views
How can I apply Conditional Formatting on a column if the cell (partially) contains text from a list (another column) in excel?
I have one column which contains a list of products sold by suppliers, these are complicated names with many words.
I have another column that contains a list of generic product names (mostly one word)...
- 13
0
votes
1
answer
2k
views
Use FILTER function to "Include" match against an Array and not one value if possible
I'm currently trying to filter some data. I have two sheets, one with the users name, e-mail and their function within the company and another with the same e-mail and then which mailboxes they have ...
- 1
0
votes
1
answer
28
views
INDEX MATCH with multiple matches. Find the MAX match
I ran a GIS operation that provided me with a table to show the 3 closest lengths of pipe to a valve. Main line pipe comes in 12,8,6 & 4 inch sizes and outputs to 4,3 & inch lateral lines.
I ...
