# Questions tagged [microsoft-excel-365]

Use this tag for questions specifically about the 365 version of Microsoft Excel; otherwise use [microsoft-excel].

101
questions

**0**

votes

**2**answers

73 views

### Filter columns based on starting string, read number from filtered cells and perform arithmetic operation to generate summary of two rows in Excel

I am having a Project and resource allocation report. I need help in generating the formula to display summary on the right section of the report.
Here're the specifications of my report:
Column A ...

**0**

votes

**3**answers

41 views

### Excel - Filter rows based on starting string, read number from filtered rows and perform arithmetic operation to generate column summary

I am having a Project and resource allocation report. I need help in generating the formula to display summary in the bottom section of my report.
Here're the specifications of my report:
Column A ...

**0**

votes

**0**answers

12 views

### Excel detects wrong input when `Shift` is pressed

I'm using Excel 365 on a Mac and want to hide/unhide columns and rows using shortcuts. I came across many cheat sheets online and they all tell the same story (like here):
ctrl + 0 / ctrl + shift + 0 ...

**0**

votes

**0**answers

10 views

### Disable the “block files from other computer” feature

When opening an Excel file from an email out of Outlook (Office 365) the file takes ages before opening into Excel.
If I save the file on my computer and open it it's the same problem.
But if I ...

**1**

vote

**1**answer

25 views

### Use filter function to extract list from incontiguous cells

I am attempting to make a form create a list (purple) of all the subtotals (blue).
Within this list I do not want to include subtotals which have a value of 0.
Given this, the form with the current ...

**1**

vote

**3**answers

20 views

### Microsoft Excel: Use OR Logic and multiple ranges with COUNTIF

I am analysing some form responses and want to count if someone has put a specific answer in a range of different boxes. I have attached an example to explain the logic.
When filling out address ...

**-1**

votes

**1**answer

22 views

### Generate Time ranges in Excel 365

What is the formula for calculating a random time that ends later than the start time calculated in another cell?
For example, A1 contains the formula =RANDBETWEEN(1,1359)/60 formatted as a time, so I ...

**0**

votes

**0**answers

26 views

### Formula returning a #VALUE error for one cell but not another

I am using Microsoft Excel 365 in which I am using a formula to cleanup the raw data that I got by copying and pasting the list information from MTG Salvation card search. Several observations about ...

**0**

votes

**0**answers

73 views

### Need Help for Inventory Tracking System with XLOOKUP

Sorry for not wording the question better, but I'm a bit confused how to put it. Please feel free to edit or suggest a better title!
I am making an inventory system for a collection of rental ...

**0**

votes

**0**answers

24 views

### Combining multiple data entries, for some unique and some duplicate customers. (100K Rows, 30 Columns)

(Top Table)-I need to remove the duplicate customers (left column) while filtering/move/pasting all of the other data in the columns to the right to move upward to complete it across 30 columns.- ...

**0**

votes

**0**answers

9 views

### Text boxes have become unselectable/uneditable in Microsoft Excel 365

Yesterday I was working in an Excel file and added several text boxes. I saved the file last night and turned off my machine. I rebooted today, reloaded the file and have been working on the file for ...

**0**

votes

**2**answers

22 views

### Average that changes at the end of each month - dynamic formula

I'm trying to build a cash flow spreadsheet and finding it difficult to find the right formula.
I would like to calculate the last 6 months average starting from this month - 1, average of june to ...

**1**

vote

**0**answers

23 views

### open csv as utf8 without creating a query

I often need to open utf-8 encoded csv files in Excel (365).
I open them using Power Query (get data - from text/csv) where I can specify encoding, and it's generally fine.
However sometimes I need to ...

**0**

votes

**1**answer

44 views

### Excel Cell Currency Picker

I want to show the currency symbol (as format not text or concat prefix) in a cell which is set as a defaulted "AUD" currency so it'll look like "AUD 1,450,000.00" but have that ...

**-1**

votes

**1**answer

56 views

### How to combine table 2 sheets (ข้อมูลตัวอย่าง1 and ข้อมูลตัวอย่าง2) to new table 1 sheet (Use VLOOKUP formula with multiple condition)?

I have data in Excel file to combine table 2 sheets (Have data with web : https://www.contextures.com/xlSampleData01.html)
How to combine table 2 sheets (ข้อมูลตัวอย่าง1 and ข้อมูลตัวอย่าง2) to new ...

**0**

votes

**1**answer

22 views

### Excel: How to change column to average based upon unique identifier

I have a situation where for a certain daily date range I have 5 different columns of data which are for 5 different pieces of equipment. I'm trying to set up a calculator where I can select the piece ...

**0**

votes

**1**answer

249 views

### How to control percent in Forecast Sheet no more over 100%?

How to control percent in Forecast Sheet no more over 100% ?
I learn to make Forecast Sheet with percent value with link : https://support.microsoft.com/en-us/office/create-a-forecast-in-excel-for-...

**0**

votes

**1**answer

28 views

### Force excel to always open with Text encoding assistant

I am looking for a way to force excel to always open .csv or txt files with the Text encoding assistant.
As I have opened a file and used the wrong settings (UTF8 - encoding issues with umlauts). ...

**0**

votes

**3**answers

54 views

### IFS Formula With Exact Digits

I am trying to create an "IFS" formula where if the number of a cell has 2 digits, then x, if 3 digits, then y, etc. The numbers are coming from another table with VLOOKUP.
So, if the ...

**0**

votes

**1**answer

23 views

### Look for Date values in embedded in strings

I have a value in a cell that might have a 2-digit month and 2-digit year value embedded in it, and I need a function that would return TRUE if that is found.
Sample input data: sCa!1220LB6qHZbCnsEE ...

**1**

vote

**1**answer

106 views

### Filter function with IF statement in 1st parameter to perform fill down multiplication OR division

Link to my previous related question for more background if needed: Transpose unique rows to columns while IF statement conditions are met but do not consolidate
My table headers start on row 20 ...

**0**

votes

**0**answers

40 views

### Auto-populate a cell based on text input of one cell and the presence of text in another cell

I'm trying to reduce the amount of manual input on a Drawing Log I'm presently creating.
Basically, I've got 5 columns: Drawing No., Client, Project, Date, and Description (in that order, though Date ...

**0**

votes

**1**answer

130 views

### Want the same cell to either display the value based on a formula or else allow user input, depending on the radio button chosen

I have cell (C17) whose value should depend on the choices made via radio buttons. One button is called 'defaults' and other button is 'custom'. The thing is, when I select the 'custom' button, I want ...

**1**

vote

**2**answers

146 views

### Excel date format changes with different regional settings (2020-08-01 to 2020.08.01)

I searched most of the posts, but there is no solution to my problem.
This is what I tried using Excel 365 with English Windows Machine:
And this is what I get when I open the file with Excel 365 on ...

**0**

votes

**1**answer

24 views

### ExcelVB Programming Application

I am looking to create an application to generate graphs for machine run time logs.
I am looking to paste a values from multiple cells say "B5","B6","E5","E6",&...

**0**

votes

**1**answer

147 views

### Extract multiple columns with single dynamic array formula

In Excel 365, how can I select non-contiguous columns from a range using a single dynamic array formula? Consider data in columns A:E as follows:
Entering =XLOOKUP(G1,A1:E1,A2:E11) into cell G2 ...

**0**

votes

**1**answer

34 views

### I cannot select or interact with pictures taken with the excel camera

Though there are some similar questions, none restore full functionality or directly address excel camera pictures.
I am using the excel camera tool on office 365, windows 10.
After placing the image, ...

**0**

votes

**1**answer

33 views

### Excel 365: How to extract data from one sheet and automatically sort it WITH DUPLICATES

I have a small gaming table, of about 100 entries, with constant data updates.
Sheet: Officers
Name | Def | HP |
-----------------------
John | 10 | 50 |
-----------------------
Doe ...

**0**

votes

**1**answer

40 views

### How to stop Text-to-Columns splits as default

I was copy-pasting some data from a PDF price book into Excel. I then then split the data into different columns along the $. However, when I then copy-pasted the next section of the PDF into Excel, ...

**1**

vote

**0**answers

43 views

### Why is text typed into other cells appearing in a cell with no formulas in a workbook with no macros?

I have a workbook with a few sheets of data, one PowerQuery, a few pivot tables.
On one of the sheets, when I type into some cells at the top of the workbook, whatever I type is repeated in cell B3. ...

**1**

vote

**1**answer

40 views

### formulas in another column that reference spill range not updating when sheet is recalculated

I have a a column that is a spill range that uses the filter and sort_by formulas. This spill range is dynamic and I have an IF statement that references the spill range but upon recalculation of the ...

**1**

vote

**1**answer

69 views

### Excel array formula not behaving as expected

I have a little table in EXCEL that uses COUNTIFS to count the number of instances in a dataset that matches four criteria that the user can select from a data validated list in the first four cells ...

**1**

vote

**1**answer

23 views

### Excel VLOOKUP issue

Why does my VLOOKUP return 5 instead of 1
And to show it's selecting the correct cells
=VLOOKUP(G1, $A$9:$B$11,2,1)
Why is it returning 5 instead of 1

**0**

votes

**1**answer

16 views

### Access 2007 Exports to Excel open in old version of Excel but I want them to open in 365

My company has a database built in Access 2007. We use Office 365, but still have the old version of Access for this database. When we export from the Access database to an Excel spreadsheet, it ...

**0**

votes

**1**answer

36 views

### Want to add a horizonal Line along with series value in bar graph

hey I am really unsure how to add line like this. i tried few google search to do that but doesn't work and change the orientation of graph. Any help will be appreciated

**0**

votes

**1**answer

28 views

### How to define Outline direction in Excel Data Grouping

In Excel 365 (version 1908), I want to group columns using the Data -> Outline -> Group function. This works fine, but it only groups columns to the right. However, I want to group the columns to the ...

**0**

votes

**1**answer

18 views

### text formula =TEXT(A1,“mm/dd”) results to 00/dd

I am trying to convert the date to text.
First, I tried =TEXT(A1,"mm/dd/yyyy") and the result is 00/dd/yyyy. From what I read in some forums, I could replace 'yyyy' with 'e' and that solved half of ...

**1**

vote

**2**answers

92 views

### Excel doesn't give 'file in use pop-up' when other user's file is in sleep

When \\server\file.xlsx is opened in read/write by user 1 & user 2 tries to open \\server\file.xlsx, user 2 gets promped with "file already in use by user 1, open in read only or notify." as it ...

**2**

votes

**0**answers

85 views

### MS Excel 365 Not Sorting

I have recently started experiencing a weird bug with excel, for some reason it has stopped sorting columns. It does not matter the way I try to sort, it just does not do it, however, filtering does ...

**1**

vote

**1**answer

291 views

### Excel 365 : How to prevent excel from locking file while editing

I just recently changed to using office 365 and am having issue with excel in particular.
I often have to use excel to modify certain xlsx files and upload it to our company website which then gives ...

**2**

votes

**1**answer

31 views

### How to format the table like this

I have the following table and past it for test case in CSV format too:
;;Prediction;;;;Real Result
;;Shares;;Likes;;Shares;Likes;Notes
;;min;max;min;max
;;108;2220;1711;17086;206;2800;The Range is ...

**0**

votes

**1**answer

385 views

### OneDrive only shows “OneDrive user” as editor in version history (but no specific username)

I share a few Excel files through OneDrive. We only access the files via browser to open them in Excel 365. When you go to the version history of a file and check a specific previous version of a file:...

**0**

votes

**1**answer

39 views

### I'm trying to get live exchange rates into a sheet/cell

Excel 365 or excel V. 16.36 on a Mac. I'm trying to get live exchange rates into a sheet/cell.
My problems:
Cant seem to find the get from web option.
The menu in excel is not the classic, so cant ...

**0**

votes

**0**answers

49 views

### I wish to center align a table text column based on the value in another Cell

I have a table which is populated from another worksheet using the offset function. This table is using rows 10 to 45 with 3 columns and activeX scrollbar. "=OFFSET(Temp!$C2,GLNames!$U$1,0)"
I tried ...

**1**

vote

**0**answers

36 views

### Excel - Select range depending on cell

I want to select a row range, which I've been doing manually until now. However as data grows, I've tried to select the row according to the relative worksheet, but don't know how to. Here's an ...

**0**

votes

**1**answer

23 views

### Unusual Excel Sheet - Completely Blank, All Disabled

Anybody has an idea what is this strange worksheet in Excel? It contains one graph, but nothing else and almost all the controls in the ribbon are disabled. Please note this is not just blanked sheet ...

**1**

vote

**1**answer

171 views

### Geography, Stocks - Excel's rich data types / linked data types

In Excel 365 (the full online version), I see there is such functionality:
Now, I find articles implying that such feature exists in Excel's Desktop version (at least Excel 2019) too:
https://...

**0**

votes

**2**answers

283 views

### Dynamic “merge & Center” rows based on date values using “Conditional Formating”

My question is really similar to the one presented here:
Excel do dynamic "merge & Center" based on values or through "Conditional Formating"
but in this case I want this in ...

**6**

votes

**1**answer

545 views

### Enlarging the conditional formatting window in Excel

I use a lot of conditional formatting, often with relatively complex formulae to decide the format. Administering them, though, becomes a nuisance - the conditional formatting dialogue is too small ...

**1**

vote

**0**answers

1k views

### Excel 365, How to Fill a Calendar with Data from a Table

So this is going to probably be a bit confusing, but I will try to explain the best I can.
I have a workbook that is intended to keep track of tasks, due dates, and assignees over seven projects. ...