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
3
answers
6k
views
Excel "Can't find Project or Libary" - but which one?
Using Office 2016. I have an Excel file with VBA. Whenever I open it, I get the error "Can't find Project or Library". Excel immediatlely closes.
I opened Excel and, without opening the file, looked ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
1
answer
5k
views
How to change Data Source in Power Query for Pivot Data? (Browse Button missing)
Ok, short explanation what I did: I created a Report in a Workbook ("Main File") linked with Power Query to another Workbook ("Source") which contains my data source. I have multiple Queries, 8 of ...
CommunityBot
- 1
0
votes
1
answer
4k
views
Get address of offset formula in another cell Excel
I am working with a group of very complicated offset formulas. I need to see which cells they reference (excel visually highlights the cell off of which the offset is based). The offsets are to a ...
CommunityBot
- 1
1
vote
1
answer
28
views
Can I create unequally spaced gridlines on an Excel scatterplot?
I am making a scatterplot with the following data:
Date
Value1
Value2
3/19/2024
5.31
45
4/15/2024
4.91
44
4/29/2024
4.93
44
5/13/2024
4.97
45
5/27/2024
4.71
42
6/9/2024
4.45
40
I created a ...
- 10.4k
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 ...
CommunityBot
- 1
0
votes
3
answers
5k
views
How to split a string by certain length in excel and put it into columns
In excel 2016 I want to split a single string with no separators into a 2-character cell under column A (same column). The string has no fixed length.
Example AAB4101X -> AA | B4 | 10 | 1X
Example ...
CommunityBot
- 1
0
votes
1
answer
5k
views
Is it possible to filter a numeric column for values starting with a specific number, without using a helper column?
I have an Excel data where one column has numeric values of varying lengths, with sample values like 100, 100001, 11050, 201, 222001, etc.
I want to filter values starting with 1. I am able to do it ...
CommunityBot
- 1
1
vote
1
answer
6k
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 ...
CommunityBot
- 1
1
vote
1
answer
4k
views
highlight cell with same value in different sheets
Excel 2016.
I have two cell ranges in two different sheets: sheet1!B2:B250, and sheet2!B2:B164 for customers names.
When I insert a cell in between the cell range in any sheet and type a name e.g. "...
CommunityBot
- 1
0
votes
2
answers
546
views
Excel will paste the last thing copied in Excel, not the most recent thing in the clipboard (pic included)
I am trying to copy data from another program and then paste it into excel. If I have previously copied something in Excel, and the green flashing border is still visible, it will paste what I had ...
- 788
0
votes
3
answers
7k
views
Why is my conditional formatting not working?
I have a column of consecutive dates. I would like to highlight the rows for Saturdays. I have Conditional Formatting applied to $2:$112, and the formula for formatting is =weekday($a2) = 7. However, ...
CommunityBot
- 1
0
votes
2
answers
67
views
Calculate a moving average in excel where the lookback (window) duration is a variable that changes based on another cell's changing values
I am trying to calculate a moving average of a dataset where the moving window time duration is not fixed, but changes over time based on numbers in a different column. Using the screenshot I have ...
- 973
0
votes
1
answer
4k
views
Inserting rows without moving the conditional formatting
I'm working on a spreadsheet with a lot of conditional formatting.
But I've got one thing that I'd like to change but isn't working as wanted.
I'd like to highlight an individual row ( lets say Row ...
CommunityBot
- 1
1
vote
1
answer
30
views
Plot data set as an chart in Excel, and then add a second series showing the data's multiple standard deviation bands aligned to data values
I am working with a time series set of data which is the Rate of change between successive price changes in a historical section of a stock price. I have 59 observations. I have also calculated their ...
- 973
0
votes
1
answer
914
views
All the excel sheets disappeared except the first one
I got stuck into a strange problem I never came across with MS Excel till date... I'm using latest version of MS office 365 ProPlus. The last time I updated my office was nearly 2 weeks back. I have ...
0
votes
1
answer
87
views
What is the formula for listing all non empty cells in a range of cells
I'm trying to gather all of the non empty cells in a n x n range of cells, then output them in a single column that I can drag the fill handle to automatically grab the next valid value. I'm running ...
- 5,470
0
votes
1
answer
3k
views
Add multiple Utilization (percentage) trend lines to a Stacked Bar Chart with a count
I would like to know how to put in percentage trend lines for individual levels in a stacked bar chart using the data below. I want a usage trend line for the total, as well as certain specific levels....
CommunityBot
- 1
0
votes
0
answers
66
views
Need Help Migrating A Drop Down List From One Workbook To Another
I need some help migrating a drop down list and the data selected from that list to another workbook.
I have a list of people that need to be contacted each week, with an updated file that is sent to ...
0
votes
1
answer
10k
views
hide filter in pivot table
I created a pivot table from the following data:
Salesperson | Amount | Include?
John | 100 | Yes
Jane | 350 | Yes
Jim | 200 | No
Jack ...
CommunityBot
- 1
0
votes
2
answers
30
views
How to Move the cursor one word to the left in ms excel?
In MS Word, Ctrl+left arrow helps use move to the previous word quickly. But the same shortcut in MS Excel takes us to the previous cell. I can't find any shortcuts for the same in the Microsoft's ...
- 1
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 ...
- 239
1
vote
3
answers
4k
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 ...
CommunityBot
- 1
0
votes
0
answers
28
views
Why don't values fit on the page in editing mode but do actually fit when printing?
In editing mode with the view set on "page break preview" some values don't fit on the page.
However, if I go to print, they fit on the page.
How does this work?
It might not look like it, ...
- 258
1
vote
1
answer
4k
views
How do you make a dynamic link to another workbook?
I have numerous workbooks titled in the format "Year Detailed", e.g. "2000 Detailed.xlsx", "2001 Detailed.xlsx", ... "2018 Detailed.xlsx".
I am trying to make a summary sheet in another workbook, ...
CommunityBot
- 1
1
vote
1
answer
4k
views
Copy data to a new table and auto update dynamically in Excel
I have a table with some data that is similar to the table below:
I would like to display a table on the right of this table to show each colour product in its own column:
The worksheet would ...
CommunityBot
- 1
0
votes
0
answers
20
views
Refreshing data only after every time interval from live master sheet
I have a master sheet that receives live data. I want to link that data to another sheet, which I have already done.
The data on the master sheet refreshes instantaneously (which is almost every ...
1
vote
1
answer
4k
views
How to Extend Zoom Scale in Print Preview in MS Excel 2016
Using Excel 2016, how can i zoom in whilst in print preview ?
I can zoom in a little using, CTRL + Mouse Scroll, however, I want to have a closer view, is this possible ?
CommunityBot
- 1
0
votes
1
answer
224
views
Excel - Vlookup within Vlookup dependent on drop-down
Please bear with my explanation here - I want to be as clear as possible. I have about 8 different tables (different categories), which contain ranges that correspond to point values. I have been ...
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" ...
- 61
1
vote
1
answer
4k
views
Calculate average ignoring blank cells which also contain formula
I am having trouble with the final Grand Total Average calculation to ignore “blank” and “zero” from the group of sub totals from the above columns.The blank cells also contain a formula.
I only ...
CommunityBot
- 1
0
votes
0
answers
15
views
Excel Rank Formula
I am using the Excel formula:
=IF(M2=0,"",IF(M2>0,SUMPRODUCT(($B$2:$B$162=B2)*($C$2:$C$162=C2),(M2<=$M$2:$M$162)/COUNTIF($M$2:$M$162,$M$2:$M$162))))
And I am getting some numbers with ...
- 1,358
1
vote
4
answers
12k
views
How to combine SUBTOTAL and SUMIFS in Excel?
I am struggling to combine my SUMIFS formula with SUBTOTAL. As you can see in the picture, my SUMIFS formula (highlighted in yellow, formulate pasted as text below it) sums up Column C when values in ...
CommunityBot
- 1
0
votes
1
answer
6k
views
Can you use FILTER function on top of results from another FILTER function? i.e nesting two FILTER functions
Excel's FILTER function takes in the following parameters:
=FILTER(array, include, [if_empty])
Let's say I use the above function to product a new table, titled table_2. Can I then nest table_2 ...
CommunityBot
- 1
1
vote
3
answers
12k
views
Convert one column into a new column every 10 rows in Excel
I have a set of 110 numbers in Excel. I want to split this into 11 columns of 10 rows each.
So, for column:
324
292
300
120
276
320
984
136
300
280
69
289
266
292
266
494
315
118
295
289
I want ...
CommunityBot
- 1
0
votes
1
answer
8k
views
Show data label on error bars
I have a bar chart showing rates per lane (trucking) with error bars showing the % variance between 2 (Feb 2018 and Feb 2019). How do I get the % to show on the error bars, like a data label?
enter ...
CommunityBot
- 1
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 - ...
CommunityBot
- 1
0
votes
0
answers
20
views
max if functions in Excel to return text from a different row matching the max if conditions
I have an Excel spreadsheet with a summary table that contains credit card charges information from different sheets. The sheets have columns A to F with 'Date', 'Cardholder Name', 'Amount', 'Result (...
- 56k
0
votes
0
answers
41
views
How to remove reference to disabled 3rd party Excel Add-ins (NOT COM add-ins!) from menu
This is for Excel 2016.
I did find this amongst a hundred other web results regarding the removal of COM add-ins, which I figured for myself, there's a handy remove button right in the menu:
However, ...
- 143
7
votes
1
answer
24k
views
How can I stack all non-blank cells from a column?
I am looking for a formula that will take all non-blank values in one column and stack them in another column.
Please see example below. Essentially I am trying to go from column A to B using a ...
- 1
1
vote
1
answer
2k
views
How do I disable Excel touch scroll bounce at top of sheet?
How can I disable Microsoft Excel's sheet overscroll bounce animation when touchpad scrolling to the top or left of sheet on Windows 10?
You can see what I mean by the video here, https://imgur.com/a/...
- 3,812
0
votes
0
answers
72
views
My windows excel icons are same for .xlsx and macro enabled .xlsm files
I'm running Microsoft Office Professional Plus 2016.
The macro enabled .xlsm files, used to have an exclamation mark in their excel icon.
For some time now, all type of excel files have the same ...
- 43
0
votes
1
answer
177
views
Excel Reference next row in array when logic is false
I want tabs in my Excel sheet to reference fields in a different page if criteria is met; if the criteria is not met, I want to skip that row, and move to the next row where the criteria is met.
My ...
- 2,508
0
votes
1
answer
581
views
Excel keeps priority on Office Clipboard
I'm working on different softwares and different workbooks at the same time.
I am copying a lot from software into workbooks or from workbooks to workbooks.
But the issue is that when I copy something ...
- 1
-1
votes
1
answer
307
views
Excel help - extract all unique values from column a in multiple sheets
I have a sheet called output, and a sheet for each month a jan, feb etc.
Without vba, is there a formula I can use to extract all information from column A in the month sheets, into column a in the ...
- 9,469
0
votes
2
answers
156
views
How to TEXTJOIN/CONCATENATE based on constraints for specific no of rows count
I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process.
I have columns as listed in the below screen shot and I ...
- 2,300
2
votes
3
answers
123
views
Weighted harmonic mean that ignores #DIV/0
I have this values:
Dataset: I have a row in a sheet (AQ4:KK4) with 255 values. The dataset have some 0 values.
Weights: I have a column in another sheet (L3:L257), also 255 values. Weights are never ...
- 21.6k
4
votes
1
answer
4k
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 ...
CommunityBot
- 1
1
vote
1
answer
69
views
Coloring cells across multiple excel sheets in an xls file based on a condition
I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers.
(Note that ...
- 2,300
0
votes
1
answer
45
views
Copy data values for another column if matched
Sheet 1 has Month, Day and Year while Sheet 2 has Year and Day. If the data of Year in Sheet 1 and 2 are matched I want to copy the Day values from Sheet 1 to Sheet 2. Is it done by VLookup and how?
- 16