Questions tagged [microsoft-excel-2016]

The 2016 version of Microsoft's spreadsheet application. Unlike previous versions, this Office Suite is cross-platform, working on both Windows and OSX. Because of this, when using this tag, it may be applicable to also tag your question with the Operating System you're on.

525 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
218 views

MS Excel: Displaying custom lower error bars

I have an odd issue with custom error bars in MS Excel 2016 (from MS Office 365). The upper error bars are showing but not the lower ones. At least not all the time... only once in a while... See the ...
4
votes
1answer
122 views

Excel 2013/2016 not updating values while workbook is minimized

I have the following setup: 1 Workbook named Workbook1 with the value 123 in A1 1 Workbook named Workbook2 with value =[Workbook1.xlsx]Sheet1!$A$1 in A1 Now, when I go and change the value of ...
3
votes
1answer
799 views

Why does Windows Defender slow my Excel file saving, even if the xlsm file type is excluded?

I'm experiencing a strange issue with Microsoft Excel (Office 365 MSO 16.0.11126.20234 32B) and Windows Defender running on Window 10 Pro (1809 Build 17763.253 64B) . When I save a macro-enabled Excel ...
3
votes
1answer
1k views

TEXTJOIN() on an array incorrectly returns a blank

TL;DR: TEXTJOIN() on an array returns a blank - can't use VBA to solve the issue in an alternative way. I've created several sheets with heavy dependence on Array functions due to limitations ...
3
votes
2answers
5k views

Missing Paste (P) option in Excel 2016

I'm copying some data from a webpage which is tabulated. When I click Ctrl+C in Excel 2010 and Ctrl+V to paste, it pastes correctly. However, in Excel 2016, the Paste (P) option is not there. Only - ...
3
votes
1answer
594 views

Define Name for Worksheet Scope in Microsoft Excel for Mac V15.38

I am trying to create named references that have a Worksheet scope in Microsoft Excel for Mac V15.38. I want to set up a template sheet that gets duplicated multiple times (example: a template sheet ...
3
votes
0answers
516 views

When customizing the Ribbon in Excel 2016 on Windows, what governs Group layout within a Tab?

Is it possible for the layout and format of Command items in an Excel Ribbon Group to be controlled by the user (i.e. through "normal" use of Excel, without recourse to XML hacking or UI editors)? ...
3
votes
1answer
4k views

How to disable “marching ants” from overriding my external clipboard data in Excel 2016?

Did the copy-and-paste behavior change in Excel 2016? Every time I want to copy something from an external application, I have to press Esc first in Excel 2016 to cancel the marching ants (selection ...
3
votes
2answers
7k views

Excel 2016 Freezing when entering data into a spreadsheet with multiple formulas

When working in spreadsheets with multiple formulas, if users enter data into a cell then leave the cell, Excel freezes for a few seconds to a couple minutes. The time varies depending on which ...
2
votes
0answers
38 views

Excel rows of data suddenly disappeared after undoing deletion of unrelated rows. Version history suddenly blank. Get my data back?

I have been editing an Excel workbook for 6 days. The workbook is saved in sharepoint and also marked to always be kept on my laptop. I have saved this .xlsx file dozens of times in the last few days. ...
2
votes
0answers
21 views

Efficient way to concatenate row titles based on column indicator?

I have a list of ingredients in column A, and different products in columns. The products all consists of different combinations of the same ingredients. In each product's column, there are Y ...
2
votes
0answers
60 views

I am unable to enter 6E029 in a cell in Excel

I am unable to enter the text from 6E000 to 6E999 in Excel. Every time I enter this it is taking exponential form. How can I format this to actual text?
2
votes
1answer
295 views

Excel Chart - X-axis values stopping at a certain point

I really hope someone can help me with this as it's driving me crazy. I have a chart in Excel which is based on a series of dynamic named ranges. When I update the source data, the table extends and ...
2
votes
0answers
26 views

Calculating and rendering a complex report in Excel

Summary: I'm trying to create a report/worksheet within Excel that shows me exactly how much of each component good I need to have/make on a specific day, based on the quantity of finished goods ...
2
votes
2answers
61 views

Excel: Formula that supports more than 15 digits

My formula is follows: {=TRUNC(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0))/(10^(INT((LEN(SUM(IFERROR(LEFT(A1:A600,...
2
votes
1answer
141 views

Need Auto Increment number beginning with Alphabet in Excel

Suppose I have a number ED000071, I want the next numbers to be ED000072, ED000073, ED000074.........ED999999, EE000001. Could someone please help me to write a formula for this in Excel?
2
votes
1answer
79 views

Microsoft Excel Axis Options sidebar

Could I check what is the keyboard shortcut to select the various options in the Axis Options side tab? e.g. for Bin width, the 'B' is underlined and in older excel version where axis options are in ...
2
votes
1answer
173 views

What is the easiest way to insert data with the wrong decimal separator into Excel?

I have a lot of situations where I need to quickly insert/import numeric data into Excel. I have no personal preference for either ',' or '.' as decimal separator. However, the data I import sometimes ...
2
votes
0answers
4k views

How to change the format of a date in a Pivot Table?

I'm having an issue in my pivot table (Excel 2016). My source data contains actual dates (generated with EOMONTH function), in a standard YYYY-MM-DD format: However, my pivot table is showing a ...
2
votes
1answer
1k views

How to keep already-opened workbooks minimized from restoring when opening another workbook?

(Excel 2016 on Windows 10) Suppose one or more workbooks are open, but they have been minimized. When I open another workbook, for example by double-clicking on it in Explorer, one of the minimized ...
2
votes
1answer
923 views

Sending specific email based on cell values in Excel 2016?

Is there a more efficient way to send email reminders to a user based on a cell value that changes frequently? Here's the code of what I'm working on so that you guys could understand the context of ...
2
votes
1answer
2k views

Auto-populate cells in Excel

As you can see in my screenshot, I have a table called "Payment Details Form" that needs to be filled and printed for each individual of 100 employees: In another worksheet, I have a data table of ...
2
votes
0answers
815 views

“The system cannot find the file specified” error for CreateObject(“Excel.Application”)

Trying to run a vbscript which uses Excel 2016. When I try to create an Excel object with this code Set objExcel = CreateObject("Excel.Application") The system returns the following error in a ...
2
votes
1answer
2k views

How can I embed an Excel workbook with multiple worksheets into another Excel workbook?

I just discovered this Excel feature that allows a workbook to be embedded in another workbook and even allow updates in the source document to automatically flow through. By default this embeds only ...
2
votes
0answers
3k views

Automatic update of powerpoint graphs from a linked excel

So I've been having a bit of a problem recently, I somewhat solved it, but I'd love to see if anyone has a better suggestion. I have created a powerpoint file with a bunch of graphs using the simple ...
2
votes
2answers
389 views

Creating a ranking in Excel by matching values in non-contigous cells

I have an excel file that looks like this: A B C D E F G H I J K L M N O 1 Alice Bob Charles 2 10 35 54 9 21 71 15 43 75 98 13 ...
2
votes
2answers
45k views

Excel - pivot table does not group dates

I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script. For some reason ...
2
votes
1answer
996 views

Show MS Excel Syntax as you type Formula

I have Office 2013 on my work computer and Office 2016 on my personal computer. On my work computer in Excel 2013 when you type a formula you get the suggested syntax for formulas as you type, but on ...
2
votes
3answers
2k views

Find text in the textbox in Excel

I've come to realize the Excel "Find and Replace" dialog couldn't find text in the textboxes. I think I need a macro to solve this. How could I find text in the textboxes: in the active sheet in all ...
1
vote
0answers
14 views

Issue with mail merge rejecting high-content rows

I have a mail merge creating word documents from a (macro-enabled) workbook of 77 columns (two sheets - one for dropping raw data into and another that does some cleanup before the mail merge). I ...
1
vote
0answers
34 views

Find Replace from excel to word template macro

I want to use excel to find (ColA) data and replace it with (ColB) Data in to a premade word template I have the macro that already opens up the word template with what i want to find/replace I am ...
1
vote
1answer
47 views

COUNTIF across multiple sheets does not work with SUMPRODUCT

I have the two sheets below. (The example table above is highly simplified for this question) I'm trying to write a formula that adds the total occurrences of A under the year 2017 (Answer is 3). ...
1
vote
0answers
11 views

Excel table.combine({Sheet1},{Sheet2}) erases data on an append

I have a power query combination that looks like this: = Table.Combine({#"Sheet1", #"Sheet2"}) Now the tables combine, and most of the data appends, but some rows are missing in ...
1
vote
1answer
129 views

Excel 2016 - How Do I Change The Border Line Color from Default Black to Another Color

In Excel 2016, the default border line color is black. Is there a way to change the default to another color?
1
vote
1answer
140 views

Excel conditional formatting matching text in other cell that may include other text as well

I am trying to do some conditionally formatting for a sports draft I'm currently taking part in. I've included a screenshot of my 2 sheets I'm working off of. The top sheet is the draft pool and the ...
1
vote
0answers
258 views

Importing Entity Relationships from Excel into Visio

Looking for help/any advice importing a very large many-to-many entity relationship diagram from data in Excel into a relationship diagram in Visio. My data is identifying upstream & downstream ...
1
vote
1answer
19 views

Excel: Dependent drop-down + calculations through multiple sheets (with different columns position)

What I am trying to do is doing some calculations (Main sheet) through different sheets with similar structure in the same Workbook, so once when I pick something from drop-down it should calculate ...
1
vote
0answers
35 views

Deleting table rows in Excel VBA by cell value - doesn't delete all rows, why?

I created this macro in Excel 2016, and the problem persists in Excel 2019. My spreadsheet has a macro to delete all rows in which a cell in a specific column contains the word 'Closed'. The macro ...
1
vote
1answer
444 views

How to ignore column values under a certain threshold when making a chart in Excel

I have a column of about 14,000 populated numerical cells, and when graphing this column I would like to ignore all values that are less than 1000. These values are due to error in sensors and show up ...
1
vote
1answer
143 views

Extracting data from one Excel sheet to another, with no spaces in rows

I've got an Excel workbook with 3 sheets. One is MasterList. One is Assigned. One is Contacted. In the MasterList, there's a dropdown next to a name. In that dropdown is "assigned" or "contacted." ...
1
vote
0answers
27 views

How can i compare 2 columns row by row in excel and then extract unique information?

My Question(s): 1) Being able to compare Column A word in Column B and highlight the difference but doing this comparison row by row between the 2 columns. Iam working with a large data set of 48k+ ...
1
vote
1answer
53 views

Dynamic Excel Formulas For Ranges

How can I make a formula that: I can vertically drag down on Sheet2... That TEXTJOINS two arrays (total of 28 vertical cells) of data on Sheet1... Which after it TEXTJOINS those 28 cells, goes to ...
1
vote
1answer
73 views

How to group in Power Query such that group headings show above items in the rows

I need to convert information from a database into a time schedule by office. Dates as column headers. Management wants the rows to be names of employees sorted by office. How can I make a column ...
1
vote
1answer
301 views

How do I flip a bar chart?

In excel, I have a set of percentages and a set of text labels. When I create a bar graph going horizontally, I see "label 5" at the top and "label 1" at the bottom. There doesn't seem to be a way ...
1
vote
0answers
35 views

Excel: Random select within table that meets with a condition

My issue is the following: I have a list of references, addresses of the reference and the city of the reference (the sample is shared below). My table has roughly 7000 lines, the references are ...
1
vote
0answers
1k views

Loading new data from a CSV file into an Excel table

I have an Excel spreadsheet which I made by starting with a CSV file converting the data into a table adding some columns to the table in which various computations happen saving the file as .xlsx ...
1
vote
0answers
105 views

Microsoft excel security warning excel 2016

I have a excel worksheet in which I do import data from external web page by this following method: Data-> Get external data -> From Web This data gets auto refreshed every 15 min. While refreshing ...
1
vote
1answer
87 views

Microsoft Excel has Black background

How do I get the black background changed back to the default setting? The original cell color was white. I didn't see an answer to this question in the blogs.
1
vote
2answers
215 views

Suspect behaviour getting cell width in Excel

From what I have gleaned, the way to get a cell's width in Excel is to enter the formula =CELL("width") into the cell in question. This works fine for one cell at a time, but when I copy the formula ...
1
vote
0answers
154 views

During data import Access doesn't recognize all of the named_ranges available in excel file

As I described in the subject, during importing Excel file into an Access made database I'm facing an issue of Access not seeing all of the named_ranges I created in my Excel file. This is the data I ...

1
2 3 4 5
11