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.
570
questions with no upvoted or accepted answers
5
votes
1
answer
357
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 ...
- 283
4
votes
1
answer
243
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 ...
- 133
3
votes
1
answer
2k
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 ...
- 131
3
votes
2
answers
2k
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 ...
- 31
3
votes
2
answers
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 - ...
- 131
3
votes
1
answer
750
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 ...
- 580
3
votes
0
answers
570
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)? ...
- 464
3
votes
1
answer
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 ...
- 1,061
3
votes
2
answers
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 ...
- 31
2
votes
0
answers
116
views
Change PivotChart source from one PivotTable (external data source) to another PivotTable (table/range)
I have a PivotChart in Excel 2016 that is currently based on PivotTable-A.
PivotTables A and B are identical, other than the fact that they have different data sources:
A's data source is an ...
- 157
2
votes
0
answers
731
views
Change PivotTable source from external data source connection to table/range
In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL ...
- 157
2
votes
0
answers
91
views
How to persistently use R1C1 styling in Excel?
I want Excel to be permanently labelled using R1C1 notation for cell positions. However, the changes I make by going to File > Options > R1C1 setting only work as long and the project is open. ...
- 277
2
votes
0
answers
1k
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
0
answers
26
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
0
answers
66
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?
- 21
2
votes
1
answer
1k
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 ...
- 21
2
votes
0
answers
31
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 ...
- 165
2
votes
2
answers
100
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
0
answers
3k
views
Excel and Word want to connect to arc.msn.com
When one of my users opens Excel or Word, a popup opens up asking his credentials for our proxy server. Apparently, Excel and/or Word try to connect to arc.msn.com (for whatever reason).
He is using ...
- 313
2
votes
1
answer
696
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?
- 21
2
votes
0
answers
444
views
How to use old dot notation for ranges in Excel formulas?
As a veteran spreadsheet user I am accustomed to using dots for ranges. Excel supported this in previous versions: For example, if I entered =sum(b2.b21) then when I hit enter Excel converted it to =...
- 1,715
2
votes
1
answer
205
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 ...
- 41
2
votes
0
answers
3k
views
Excel highlighting selected cell
I'm currently working in Excel a lot (MS Excel 2016) and searching for values.
In the search result if I'm clicking on an entry, in the background automatically the cell is selected.
Unfortunately ...
- 141
2
votes
2
answers
421
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 ...
- 223
2
votes
0
answers
5k
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 ...
- 1,020
2
votes
1
answer
1k
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
1
answer
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
0
answers
963
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 ...
- 143
2
votes
1
answer
3k
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 ...
- 281
2
votes
0
answers
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
2
answers
435
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 ...
- 121
2
votes
1
answer
1k
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
1
answer
15k
views
How to automatically import data from csv file and append to existing Excel table
I have a .csv file and a master excel file. The master file contains a table and I would like to automatically append the data in the .csv file to the existing table. The data has the same headers and ...
- 31
1
vote
1
answer
79
views
Excel Pivot Table showing same count as the first column
I'm a beginner at Excel and am currently practicing pivot tables.
The data that I'm dealing with is this table:
In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...
- 11
1
vote
2
answers
233
views
Excel: faster formula to find word that contains search string?
I have a bunch of text strings in Excel 2016. I am trying to search for various substrings. To be more helpful for reviewers, I want to return the word, delimited by spaces, which contains that ...
1
vote
0
answers
251
views
How to make an excel sheet dynamically reference another sheet
I am trying to make an excel sheet that generates material costs for construction. My plan is to have one sheet where I can enter the material and its quantity, and another that stores the prices of ...
- 11
1
vote
2
answers
91
views
Excel sheet Formula Query
I have excel sheet where I need to fetch the column name header name if the column value is set to 1. And that to a new column in each row. Is that possible to do via a formula or is a Macro required?...
- 11
1
vote
0
answers
183
views
How to set color for Maximum and Minimum Value bar in Clustered Column Chart?
Let's say, I've the following Pivot Table generated from a Random Sales Dataset.
And the corresponding Pivot Chart.
How can one make the Maximum & Minimum value bar of "Sum Of Profit" ...
- 45
1
vote
0
answers
742
views
Excel Secondary Axis being Stupid with Stacked Bar Charts
I hope you can help with this as it is the first time google has no result for an error I thought it was common.
I am following the instructions for a bullet chart, but instead of a column, I would ...
- 29
1
vote
0
answers
273
views
Use Same Scale for Axis and Bubble Sizes in Bubble Size Chart Excel
I've been dwelling on the Internet for a long time before understanding how Bubble Charts work in Excel. Now it seems logical to me that the Bubbles have their own scale to represent data in a way ...
- 21
1
vote
1
answer
57
views
How to automatically diff changes from master database?
I have a problem, which I can’t get around. I thought maybe someone of you already had some similar experience/problem and would be able to help the rest of the community or share some useful ...
1
vote
1
answer
467
views
Formulas - Conditional Formatting
I need the cells from Aug to Dec to be formatted automatically based on the "plan numbers" if actual numbers are higher (green); if lower (red)- I'm trying to set it up so when data comes (...
- 15
1
vote
0
answers
60
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 ...
- 111
1
vote
0
answers
236
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 ...
- 11
1
vote
1
answer
495
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).
...
- 139
1
vote
0
answers
17
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 ...
- 21
1
vote
1
answer
296
views
How to merge two formulas in one cell
How can I merge these two formulas in one cell?
= IF(D2="TRUE",C2+C2*20%, C2)
= IF(E2="TRUE",C2+30000, C2)
I have created two dropdown menu in excel, but can't merge them ...
- 11
1
vote
0
answers
2k
views
Can I have Excel create new rows instead of throwing a SPILL error?
If an equation returns multiple results, it will attempt to write the output to multiple cells. However, a SPILL error is thrown if there are non-blank cells that would be overwritten.
Is it possible ...
- 227
1
vote
1
answer
227
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 ...
- 11
1
vote
0
answers
1k
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,482