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,437
questions
0
votes
0
answers
15
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
568
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
234
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
89
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 ...
- 157
1
vote
1
answer
141
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 ...
- 157
2
votes
4
answers
8k
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
2k
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(...
- 109
0
votes
2
answers
546
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
53
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
1k
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 ...
- 271
0
votes
1
answer
30
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
4k
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 ...
- 157
1
vote
1
answer
584
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
39
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 ...
- 37
0
votes
0
answers
159
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
457
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
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
0
votes
2
answers
65
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
2k
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 #...
- 464
0
votes
1
answer
108
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
1k
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
56
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
28
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
909
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
262
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,173
0
votes
1
answer
20k
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
396
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
31
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
92
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......
- 476
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
0
votes
0
answers
42
views
How do I use a series of quantities in a column of cells to populate set information for that many cells into another column?
I'm working with sensitive information, so I can't show too much, but I think my request will be clear.
I'm trying to use the below quantities to spit out values in a different sheet that number of ...
0
votes
1
answer
157
views
MS Excel show hidden columns that are scrollable
I have a Gantt chart spreadsheet that has a scrollable section that shows the chart.
As you can see from the image, part of the chart is hidden and one has to scroll to see the rest of it. How can I ...
- 1
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
0
votes
1
answer
749
views
Pinned files are not unpinned on the taskbar in pinned applications
The problem is this. Excel is pinned to the taskbar.
When you right-click, a list of pinned files and recent files appears. Some files in the Pinned ones cannot be unpinned from the list. The files ...
0
votes
0
answers
2k
views
Microsoft Excel - Why is my graph not plotting correctly?
I have two columns of data,
the first column is Average RT, going from 2050 to 20450.
My second column is fraction, from 0.000000 to various decimals, but all add up to 1.
My aim is to plot the ...
-1
votes
1
answer
86
views
Highlight rows when all values are for Order Number
I have a spreadsheet with two columns. One column is Order Number and the other one shows its status. As shown in the screenshot below:
Sample Data Sheet:
I want to highlight all the rows when the ...
-1
votes
1
answer
968
views
If a cell has a duplicate take value from another cell
As you can see cell A4 with the quantity of B4 has a duplicate in cell A24 with quantity of B24. There is an 8 and 4 in total.
How can I find this kind of duplicate and sum them together?
1
vote
2
answers
670
views
Formula to get comma-delimited list of unique values from table column
I have a table in Excel 2016:
ID TIME_PERIOD
-- ---------------
1 FORECAST
2 HISTORY
3 BEYOND FORECAST
4 HISTORY
5 BEYOND FORECAST
6 BEYOND FORECAST
7 FORECAST
8 FORECAST
9 ...
- 157
0
votes
0
answers
17
views
Filling following Six cells based on the letter entered in one cell
enter image description hereI have a sheet where I have to enter the letter combinations that shows the time taken by the product on different work stations. For example, if a products "X" ...
- 1
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
0
votes
1
answer
440
views
Reuse an existing external datasource connection for PivotTable (without creating a duplicate connection)
In Excel 2016, I can create a connection to an Oracle database (via ODBC) using Microsoft Query or the Connection Wizard.
Creating a connection using one of those methods works as expected. The ...
- 157
0
votes
0
answers
1k
views
Dynamic filter in PivotTable based on the system date
I have a pivot table in Excel 2016.
In the PivotTable, I want to create a filter on the data's YEAR column so that only records where the YEAR is between this year and this year + 9 are used in the ...
- 157
0
votes
1
answer
27
views
Navigate to a specific table/view in list of objects in Microsoft Query
When setting up a Microsoft Query connection (to an Oracle table/view via ODBC) in Excel 2016:
Problem:
I have hundreds of Oracle views to scroll through in order to get to the view that I want:
...
- 157
1
vote
1
answer
54
views
Stacked bar chart to show asset maintenance types by year
I have a table of roads by year.
The NEXT_ACTIVITY column categorizes the roads by the theoretical maintenance activity that the roads fall into.
YEAR COUNT/TOTAL ROAD_COUNT NEXT_ACTIVITY
...
- 157
1
vote
1
answer
116
views
VBA code to enable three Cells, each in its own worksheet, equal to each other always
I have three cells in three worksheets: Cell A1 in Sheet1, Cell A2 in Sheet2 and Cell A3 in Sheet 3.
I would like to have values in the three cells always equal to each other. This means that an ...
- 113
1
vote
2
answers
783
views
Why can't INDIRECT() take a dynamic array reference just after opening a file?
I have a formula containing INDIRECT() pointing to some range -- call it B4:F8.
If I specify the range explicitly, like this:
=INDIRECT("B4:F8")
then it works fine.
However, if the range in ...
- 464
0
votes
2
answers
1k
views
concatenate with ISBLANK and delimiter
I am attempting to combine two columns in Excel 2016, with a space between the results. My problem is being able to use ISBLANK to ignore blanks on either column, and still have the space delimiter in ...
0
votes
2
answers
2k
views
Excel: Use table from one sheet in a different sheet
I have a table in an Excel sheet; the table and sheet are both called "LTIP_RD_COND_CURRENT_VW".
The table's source is a Microsoft Query via an ODBC connection to an external database.
I ...
- 157
0
votes
1
answer
2k
views
Excel Convert Row to Matrix
Is there an integrated function of Excel 2016 to transform a series of 1x95 lines of data into unique 5x19 matrices?
I have a ton of 5x19 matrices that are getting imported into Excel. Unfortunately, ...
- 103
0
votes
1
answer
829
views
Compass bearings on excel
Would like some help creating an excel formula:
Compass bearings are from 0 to 359. I have attached a picture of how it looks like here:
Basically, I am looking for a solution for when I input 2 ...
