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.
1,410
questions
1
vote
2
answers
47
views
Excel 2016 - How to count UNIQUE rows that are "assigned" to someone?
I am building a chart that contains the total amount of rows and accounts assigned to a user in Excel 2016. The first issue is easy and I resolved it with a COUNTIF function, however the second part ...
1
vote
1
answer
33
views
Excel formula to return column header values based on following rows
I am trying to come up with a formula that will return values of headers for each row that has one more more "x". (As seen in the "results" column below)
Can this be done without ...
- 11
0
votes
2
answers
40
views
Count number of distinct lists excluding zeros in two-dimensional array
In Excel 2019 I have a formula in a cell which when evaluated with F9 in the formula bar returns the following two-dimensional array (matrix):
{0,0; 0,0; 0,0; 0,0; 0,0; 0,0; 44846,1; 44846,1; 44846,1}
...
- 147
0
votes
0
answers
29
views
Excel formula or VBA to create simple coversheet calendar from multiple worksheets
I have several worksheets that I want to create a simple calendar with. Every month I will be creating a new workbook.
Detailed operating information is on each worksheet, name of the worksheet is ...
- 23
0
votes
0
answers
19
views
Run Excel Macro Faster?
I am trying to run this macro, it works, however, it taking too much time to work like 3 minutes.
The refresh button should hide the empty rows and show some shapes in the sheet.
see the attached code:...
0
votes
1
answer
23
views
Converting My Macro into Array?
I have done a Macro code where I was able to show rows which contains the word "Overdue" and hide the other rows which contain the word (Completed, Pending, in progress, Delayed". ...
0
votes
1
answer
50
views
Mac Excel 2016 formula to add selected cells just by clicking on them
In a previous version of Excel, I could just click on each cell that I wanted to add and it would automatically put a + between each one. I can't figure out how to do that on this version.
So if I ...
- 3
-2
votes
2
answers
54
views
Conditional Formatting with greater than & less than parameters based on date in cell
Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...
- 1
0
votes
0
answers
24
views
The colours and fonts automatically changes on shared excel file
We use a shared document to import data. We have a lot of different colours and informations on it, which is really important it stays that way, it is updated 1000x per day between 30 people.
However ...
- 1
0
votes
0
answers
26
views
Excel - Using VLOOKUP on grouped table records
I have an xlsx report from a finance tool which provides raw numbers but none of the formatting or formulae I wish to use to better read and analyse the numbers.
The report is structured like so:
...
- 279
0
votes
0
answers
24
views
column to row but keep the same the previous row
I have multiple block of data as below and I want to transform it from column to row.
Im new to excel so I hope you guys can help me with.
e.g:
input:
A AA 1 2 3 4 5
B BB 1 2 3 4 5
C CC 1 2 3 4 5
...
-2
votes
1
answer
37
views
How to match and merge lookup results in Excel?
I'm a total newbie with Excel and I'm stuck at doing this. I'm guessing there must be some work around "lookup" functions but I don't know which one and how to start.
Let's say I have a ...
0
votes
1
answer
81
views
Chart axis labels — Use $1M (million) short form instead of full $1,000,000 number
I have a PivotChart in Excel 2016 that has dollar amounts as the x-axis.
Currently, the labels are formatted as full numbers. Example: $1,000,000.
Is there a way to format the labels by replacing the ...
- 137
0
votes
1
answer
34
views
Sum of days between dates in Excel
I have a column where its cells are formatted as "dd hh:mm:ss"
When I'm going to do the totaling of the lines and the number of days "no" exceeds 31, the sum works perfectly, but ...
- 593
1
vote
1
answer
33
views
Drag to fill duplicates values
I want to create a sequence of dates that populate a column.
A
2023-12-31
2023-12-30
2023-12-29
...
Format Cell is set to Date.
Calculation Options is set to Automatic
Enable fill handle and ...
- 333
0
votes
1
answer
34
views
Excel list - how to select/copy only 5 values of each item?
I have a list containing 400 items with customer names as follows:
ItemName CustName MobileNo
I need to make a new list that only 5 customers is selected/copied for every item in a single go so ...
2
votes
1
answer
85
views
In Excel 2016, how do I link a CSV where you can specify the file to import like I could in Excel 2013?
In Excel 2013, I used to be able to specify a sheet to Data Refresh and at the time of creating a linked csv file, I could prompt for the location or file with every Data Refresh. In Excel 2016, I am ...
- 6,142
2
votes
0
answers
31
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 ...
- 137
2
votes
0
answers
126
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 ...
- 137
0
votes
0
answers
11
views
Duplicate values not returning correct cell value
Duplicate Values
I have this image of what is happening when sorting the top scores from the largest to the lowest. The issue is that when two teams have the same score (Team 1 and Team 2 in the image)...
0
votes
1
answer
76
views
Is there a way to drag a SUMIF/(S) formula down vertically but pull information horizontally, and vice versa?
Essentially, I am trying to fill information into Sheet A (SA) using =SUMIFS but the information that it is pulling into the actual formula comes from Sheet B (SB) and is organised in horizontal form.
...
- 1
0
votes
1
answer
37
views
Overlaying different dates with corresponding data points in the same Excel graph automatically?
I have a dynamic set of data that is filtered in from a table, with different arrays of data assigned to potentially overlapping dates, shown here Data Input from Filter Formula (the dates and data ...
0
votes
1
answer
31
views
If rows are missing for certain years within sysdate-based year range, generate filler rows using Power Query
I have a table in Excel 2016:
YEAR AMOUNT
---------- ----------
2024 100
2025 200
2025 300
2026 400
2027 500
2028 ...
- 137
1
vote
1
answer
31
views
Force bar chart to show missing years within 10-year range (system year + 9)
I have a table in Excel 2016:
YEAR_ AMOUNT
---------- ----------
2024 100
2025 200
2025 300
2026 400
2027 500
2028 ...
- 137
2
votes
3
answers
348
views
How to paste only the formatted value not the actual value
As the title says, I want to copy and paste cells into the format that they are shown in.
Original value (actual value):
25
59,05
90,1
Formatted value (what is shown):
25,000
59,050
90,100
The ...
- 121
-2
votes
1
answer
33
views
How can I calculate the median of some values with NAs in Excel?
I would like to calculate the meidan for two categories with NA() [displayed as #N/A] in them. When I create a box plot excel is happy to help.
But when I try to work that out in a formula -- MEDIAN(...
- 99
0
votes
2
answers
194
views
Transpose Group of Rows to Single Row | MS EXCEL
I need to Transform Some Uneven Group of multiple rows to single row
I Tried to Extract the Required data from multiple row to multiple Column. But all data placed under different Rows (same as input ...
- 115
0
votes
0
answers
16
views
Is there a way to stop previous day's values from being recalculated after new data comes through on the current day
My Excel formula depends on data I receive from a website every morning. My problem is, when new data comes, data from previous day recalculates & affects current day's value as it is also ...
0
votes
1
answer
230
views
how to structure columns in excel
I have the following data in excel:
How can I separate the data into columns? and adding a ";" at the end of each column.
expected output
I already tried in a thousand ways but I'm lost. I ...
- 263
0
votes
1
answer
26
views
EXCEL: What type of dataset am I working with overall? How to manipulate pivot tables to answer complex relationships between fields?
I am beginning to delve into data analysis via excel and I would like to learn more about the terminology in terms of some data I'm playing with.
I've created a sample data set for mock invoice ...
- 21
0
votes
0
answers
505
views
Pivot bar chart legend order
I have a PivotChart that is a stacked bar chart in Excel 2016.
I want to control how the Ward numbers are displayed in the chart.
I want to keep the PivotTable Ward number columns in alphabetical ...
- 137
1
vote
1
answer
267
views
Excel: Split a 2 column range (A:B) into multiple smaller columns with a pre-set number of rows
I'm struggling with a problem; I'm trying to split two columns (A&B) containing anywhere from 100-1,000 rows of information which need to remain together, into multiple columns containing a pre-...
- 21
0
votes
0
answers
27
views
Excel´s Power Query: SUMX not working properly(?)
I'm working on a pretty straight forward Query. I need to add a measure in order to calculate the sum of 5 different columns. My best guess is I need to use SUMX but it is giving weird values.
enter ...
0
votes
0
answers
36
views
need advice two excel formulas
#SOLVED -
=IF(AND(COUNTIF(b3:e3,"Yes")>=1,i3="Yes"),"Remove Online Module",IF(AND(COUNTIF(b3:e3,"No")=4,OR(i3={"Yes"})),"Complete Online ...
- 19
0
votes
0
answers
62
views
Call a sheet using a variable name
I have this code in wich I add a sheet with a certain name and then I need to call this sheet to get a cell value but it doesn't assume.
Dim nome1 As String
Dim tableName As ListObject
Dim addedRow As ...
0
votes
1
answer
45
views
Excel: Plot a single X and Y coordinate in a static graph
I am trying to create a coordinate graph where the X and Y axis are in a static 1 to 4 scale and the values do not change. The point to be plotted is gathered from two formulas that calculate the ...
1
vote
0
answers
47
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
0
votes
2
answers
18
views
Find Multiple hits of the search value across all columns & rows of a table and return a list of the results from the 1st column of the table
I have a spreadsheet that holds a list of offices in the 1st column. Each column after this holds the list of Postcodes that this office covers (only the 1st part, ie se3). Some of the postcodes are ...
0
votes
1
answer
293
views
With Excel Dynamic Arrays, is it possible to spill across merged cells?
[I'm trying to follow the guidance about answering one's own question, but I'm not 100% sure I'm doing it right]
With Excel Dynamic Arrays, is there a way, when spilling across merged cells, to avoid #...
- 420
0
votes
1
answer
71
views
Microsoft Excel Conditional Formatting for Dates and Prices
I'm looking to conditionally format a table following this general idea; I have short dates in one column and years in the headers for several other columns, Id like to highlight the cell when the ...
- 1
0
votes
1
answer
101
views
Excel summary page that auto populates from sheets
I have a problem with excel that may not be possible. I have a workbook that contains a list of sheets with various projects e.g. 'project 1' is a tab, 'project 2' is a tab etc. I now need to ...
- 1
0
votes
1
answer
29
views
Format of Excel drop down list
When choosing from a drop down list in Excel, is it possible for the cells to change the format when you choose a specific subject?
Each subject has a different format for the cells.
- 1
0
votes
1
answer
21
views
When creating a box and whiskers diagram in Excel, the percentages are off by 10x. How can I fix this?
I have some values that I wish to use a box and whisker's diagram. The values are percentages above a certain standard value, so percentage is the appropriate axis, but Excel is displaying 10 times ...
- 1
4
votes
2
answers
280
views
Create a formula that sums up values between equally spaced cells
I am trying to make my cells follow a continuous incremental pattern so that I can drag it down and it applies to all cells. I am creating the calculation in one sheet, but the data is in other sheet.
...
- 41
0
votes
1
answer
88
views
running 2 excel versions at once, cant associate filetype to older version
I have excel 07 and the latest Excel 365 (I think thats Excel 16) both installed in windows 10. When I just had 07, I had no problem associating csv and xlsx files with that program. Now that I have ...
- 1,007
0
votes
1
answer
99
views
Color another cell based on (if blank) by two different cells
I had this working at one point
Column A: Names
Column B: Date
Column C: Date
IF Column B cell is filled, Color Fill ColumnA-Cell GREEN
IF Column C is filled, Color Fill ColumnA-Cell is RED
...
0
votes
1
answer
69
views
Running several sets of assumptions through a model in Excel
Hello and thank you in advance for your help! This question is for excel.
I have created a dynamic model which outputs estimated annual interest revenue, and it depends on 5 inputs (average term ...
0
votes
0
answers
25
views
Conditional Formatting not being applied to sheet when data is added from another
I have a training spreadsheet with conditional formatting showing dates passed, coming or in date for personnel.
When doing a data dump from the updated registry the sheet does not apply the ...
0
votes
0
answers
39
views
Excel 2016 to open in the same place we closed it, like Excel 2010
Slow to the game, I know, but I just upgraded to Office 2016 from Office 2010. In 2010 when I opened Excel, it opened on the same screen, in the same position, no matter how many instances I opened......
- 441
1
vote
2
answers
75
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