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
2
answers
82
views
Excel - Combining multiple cells in a single row
I have data disposed in this manner:
Jonh Seedman New York, New York 01/01/1900
5th Ave +1 NXX-NXX-XXXX
Homer Simpson Springfield, Illinois....
- 3
0
votes
1
answer
768
views
Excel file opens file on second double click only
When I double click an XLSX file in Explorer, Excel starts, but does not open the file.
When I double click the same file again while Excel is already open, the file is opened.
However, if I close ...
- 6,064
-1
votes
1
answer
130
views
Unable to see the cells in the Excel workbook
I am unable to understand this worksheet which was written by someone else. There are certain columns which are not visible unless activated. This activation occurs when I change a value in particular ...
- 1
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
2
votes
1
answer
3k
views
Excel graph not showing some x value labels
My x-axis for an Excel graph is missing some labels
and I cannot figure out why.
More specifically, my x-axis is supposed to look like this
(I will bold some of them so it's easier to read):
50, 100, ...
- 123
-1
votes
2
answers
795
views
Excel - Conditional format cells based on a column and duplicate and another column's value
How would i conditional format a column for duplicate values if the cell is a duplicate of another value of same column AND another column contains the same value as the duplicate, without adding a ...
- 31
0
votes
1
answer
55
views
How do I separate these rows from the second column?
I have a large table that, after some filtering for duplicate, looks like the below and I need the "TAGS" to be in the column adjacent to the "SERIALS". I've tried a Pivot Table ...
- 3
0
votes
1
answer
627
views
Excel: insert rows in multiple tables (not plain cellsheets) simultaneously, sans macro
Hello to SU community,
I have an Excel file with 4 tabs: RC, PC, TI, Contacts. The 2nd and 3rd are locked so as to avoid any accidental modification. Tabs RC and PC each contain one table, and the ...
- 33
1
vote
1
answer
65
views
LARGE function throws error
I would like to compute average scores over top k exams. That's why I need to get top k scores. There is a large function in excel, but I am not even able to execute it in a simple list in excel ...
- 113
0
votes
1
answer
3k
views
How to create "workaround" MAXIFS and MINIFS formulas in Excel?
I am using the following formula to calculate the average daily temperature value in column B (see image below) for each day over a 15-year meteorological record (temperature values are in column B ...
- 101
0
votes
0
answers
80
views
Show window contents while dragging is not working in MS Excel
While dragging the horizontal or vertical bar, the contents do not move on Microsoft Excel. But the window contents move while doing the same process on a browser or any other software. I have not ...
- 1
0
votes
2
answers
114
views
How to highlight trends in time records? (system errors)
I have a list of system error timestamps.
I want to graph the time from the timestamps to see if there are any trends -- i.e. approximate times of day when the system often goes down.
Question:
Is ...
- 157
1
vote
1
answer
2k
views
Convert table image to Excel, using Excel
I have the table below in the image png file format.
Would it be possible to convert this to an Excel table using Excel?
- 351
3
votes
1
answer
10k
views
How to Change Orientation of Multi-Level Labels in a Vertical Excel Chart?
I have the following chart and I am not able to edit the first level of labels (membership, chapters, etc). When I go to Format Axis -> Alignment I can only change the orientation of the second ...
- 119
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
2
votes
2
answers
3k
views
In Excel, how can I round or display numbers to 2 significant figures (the two leading non-zero digits of a decimal number)?
In Excel 2016 I need to show zeros out to the leading non-zero digits and then just the two digits after that. For example:
0.00002423 should read 0.000024,
0.242 should read 0.24,
0.0032255 ...
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.
...
0
votes
1
answer
132
views
How can I delete a column in sheet 2 based on sheet 1?
I have two different sheets: "Input"(sheet2) and "Instruction"(Sheet1)
I want to delete the whole column in "Input" sheet corresponding to the value in the cell(row 34, ...
- 5
0
votes
0
answers
49
views
Excel 2016: How to find a specific name in sheet "A" and and get the adjoining cell's value to a cell in sheet "B"?
Excel 2016: How to find a specific name in sheet "ASPI" and and get the adjoining cell's value to a cell in sheet "Watchlist"?
I have currently used the method of this formula to ...
- 1
0
votes
2
answers
14k
views
Division calculation within pivot table values in excel
Please see example below. The row headings (redacted) are defect types of a product. The column headings are the name of the products (redacted) and just below are the total sales of that product. ...
- 15
0
votes
1
answer
167
views
#VALUE! error using excel 2016 nested IF statements
I'm unable to perform a simple nested IF statement on this data, the conditional seems perfect to me so it must be a formatting error or something else. I'm new to excel so I could be wrong.
This is ...
0
votes
1
answer
2k
views
How to return first non-blank value with LOOKUP function
I have a table:
table in which I reference data from a dataset using a LOOKUP function. I can reference columns(such as new_deaths) which have the latest(non-blank) data just fine. However, for some ...
- 5
0
votes
2
answers
16k
views
Excel - How to delete the infinite blank rows?
We have 5000 records of data.
For some reason, our Excel file has over a million rows where every column is blank.
How can we get rid of these 1 million + empty rows?
0
votes
2
answers
1k
views
Is there a way to copy all empty cells in a data set on one sheet and paste the empty cells into a data set on a different sheet?
Is there a way to copy all empty cells in a data set on one sheet and paste the empty cells into a data set on a different sheet?
For every cell in the “Sales Sheet” that is blank, the corresponding ...
- 1
0
votes
1
answer
2k
views
Excel - automatically updating cell reference when applying conditional formatting to range
My question concerns conditional formatting in Excel. Here is a summary of my situation:
I have conditional formatting set up for a particular row in Sheet A. The formatting formula checks whether ...
- 1
0
votes
0
answers
69
views
Excel Chart "Save as Picture" making dates into numbers
I'm trying to save a picture of an excel chart (for use in distributions where it doesn't allow me to just copy and paste the chart). So I right click the chart I want to save, click "save as ...
- 21
0
votes
1
answer
6k
views
Excel: Drop down with month instances
Is there a possibility to have a drop down with months inside, when choosing one month to show me all instances that are within that month?
Something similar has Excel format Tables, when you choose ...
- 121
0
votes
1
answer
377
views
Combining an Excel IF Statement with VLOOKUP
I have the following if statement in Excel:
=IF(VLOOKUP(AB2,AD:AD,1,0)=TRUE,1,0)
What I am trying to do is say the following:
"If the results of the VLOOKUP return any value other than "#N/...
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
0
votes
2
answers
258
views
Excel: Return all matches in columns using one criteria (from drop down)
I am having two tables, one table is main data, separated into many columns and blocks per 10 rows in one, this is fixed. In Column A are unique numbers that will be used in drop down.
Table 1
On ...
- 121
0
votes
1
answer
48
views
Changing the layout of an Excel Sheet
A little help will be deeply appreciated. I have a database whose layout is not convenient for me. Here is the layout:
I would like the sections within the FINANCIAL column to become headed columns ...
- 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 ...
- 1
0
votes
1
answer
135
views
Blocking certain rows for certain users when sharing an excel-sheet
This year, I will finish a master I've been doing and enjoyed, and we, the participants, would like to stay in touch after the course ending. We already have the WhatsApp Group, but it's difficult to ...
- 103
0
votes
1
answer
818
views
Conditional formatting for specific group of values/rows based on cell value(s)
I am having some data table that is separated in lets say "blocks" of rows. As you can see on the image, starting from Column D (A1) there are some values in first row, and then 5 rows empty ...
- 121
0
votes
1
answer
349
views
MSExcel: Is it possible to make an adaptive formula through reference?
Example:
Desired result:
(Please note that the red values in the first image above are made red to signify that they are actually functions, imagine adding an "=" sign before them.)
I have ...
0
votes
1
answer
1k
views
Cannot Reference Pivot Table headers due to header formatting
I am writing a SUMPRODUCT function that references headers in a pivot table for my report on a separate sheet. The header that is giving me an issue is the year, i.e. 2020, 2021, etc. I have tried ...
1
vote
1
answer
1k
views
Open Semicolon Delmited Text File in Excel Via Double Click
I can make Microsoft Excel open up a semicolon separated value text file by placing "sep=;" at the beginning of the file. Then upon double clicking, it properly separates every column and ...
- 1,610
0
votes
1
answer
185
views
SUMIFS with three criteria
I am trying to write a SUMIFS formula that will aggregate my data based on three criteria. As per the first table, I am presented with data that provide the sales for each agent, broken down by three ...
0
votes
1
answer
42
views
Fillup cell depending on value of another cell
So I want to fill up certain cells with certain values. I want to use the IF function. I have the following data:
4271423/421424/4237168/4246332=D917/F222
4271423/421424/4237168/4253485=D917/F222/J008
...
0
votes
3
answers
2k
views
Convert the number 1999 to the year 1999 - in date format in Excel
This has to be easy, and extremely common, but I can't find the answer with Google.
I have a column with years (number format) in Excel. I need that column in Excel to be in date format for importing ...
- 1
0
votes
2
answers
48
views
excel - group sheets by columns
I have a bunch of .csv files with the same structure, but it's not guaranteed that they are in same order row-wise. I would like to group them all in a single spreadsheet. The following image draws ...
- 101
0
votes
1
answer
294
views
Get the position of the last row in an Excel sheet
I have a formula in my cells that refers to a separate sheet with some data in a single column, something like
A1:A500
However, new values are being added regularly so 500 becomes 501, 502, etc.
I don'...
- 365
0
votes
1
answer
347
views
Create multicategory timeline chart in Excel
I'd like to create an Excel chart showing on the x axis the dates from 1943 to 2021 and on the y axis the relevant category which follows a specific timeframe.
I.e. from 1/1/1943 to 18/01/1943 ...
- 3
2
votes
1
answer
12k
views
How to use file path stored in a cell in another cell to refer to a value in excel
I have a macro that loads the path of a file to a cell dynamically, Let's say the cell is C1. C1 contains
File Path: "/a/x/c/s"
How do I use the value stored in C1 in other cells to get the ...
- 123
1
vote
1
answer
488
views
MsgBox based on 2 values in different cells
I am completely a VBA novice and am trying to achieve the following, without success.I would really appreciate your help.
What I need to achieve:
If someone selects for each row Action = "Delete&...
- 23
0
votes
0
answers
2k
views
Excel VBA error handling with workbook open
Sub Test()
Application.ScreenUpdating = False
On Error GoTo Errhandler
Set WB = Workbooks.Open(Database, UpdateLinks:=False, ReadOnly:=False, IgnoreReadOnlyRecommended:=True, Notify:=...
- 107
0
votes
1
answer
48
views
Error from custom function
Function IFBLANK(param1 As Variant, param2 As Variant) As Variant
If param1 = "" Or param1 Is Null Then
IFBLANK = param2
Else
IFBLANK = param1
End If
End ...
- 107
7
votes
3
answers
1k
views
Excel IFBLANK equivalent for IFERROR
IFERROR works great because it shortened the formula significantly by having a default behavior, returning the results if condition is not met (no error).
I'm looking for the IFBLANK version of this ...
- 107
0
votes
2
answers
3k
views
Excel - How to index and match values using header and subheader?
I am presented with financial data that contains a header for various regions, and a sub-header for different years under each region, as per Table 1 below.
I am trying to write a formula in cells B4 ...