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.

Filter by
Sorted by
Tagged with
0
votes
0answers
8 views

How can I delete a column in sheet 2 based on sheet 1 condition?

In sheet1, I have different data number in column 1. Each data has different parameter values. I want to delete the whole column in sheet 2 corresponding to upper and lower value of sheet 1. The ...
0
votes
0answers
29 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 ...
0
votes
2answers
26 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. ...
0
votes
1answer
28 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
1answer
34 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 ...
-1
votes
1answer
43 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
1answer
68 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 ...
0
votes
0answers
18 views

Excel: Writing a 'report' within a worksheet

Summary I want to have a specific worksheet within a workbook display a set of data from another worksheet within the same workbook - like a kind of report - in a specific order, for re-use/population ...
0
votes
0answers
19 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 ...
0
votes
0answers
35 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 ...
0
votes
1answer
26 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 ...
0
votes
1answer
23 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
1answer
45 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). ...
1
vote
0answers
11 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 ...
0
votes
2answers
33 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 ...
0
votes
1answer
32 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 ...
0
votes
3answers
39 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 ...
0
votes
1answer
24 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
45 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
1answer
26 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 ...
0
votes
1answer
41 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 ...
0
votes
1answer
43 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
1answer
23 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
3answers
47 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 ...
0
votes
0answers
16 views

MAX/MIN of Groupings of Non-sequential Numbers to Generate a (Data) Range for Each Group

I have a XLS of long numbers that should be sequential / contiguous, however, they are not always - meaning the data may be 1 2 3 5 6, where 4 is skipped. I need to identify the start/end of each ...
0
votes
2answers
34 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 ...
0
votes
1answer
24 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'...
0
votes
1answer
30 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 ...
0
votes
0answers
87 views

Excel vba to check if a sharepoint file is in use/editable

I have some files in company sharepoint network. I am using a local excel vba script to open and edit those files. Set wb = Workbooks.Open("http://XXXX/XXXXX.xlsm") wb.LockServerFile The ...
1
vote
1answer
27 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 ...
1
vote
1answer
21 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&...
0
votes
0answers
30 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:=...
0
votes
1answer
32 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 ...
3
votes
2answers
113 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 ...
0
votes
2answers
47 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 ...
0
votes
0answers
12 views

Plotting country outlines in Excel map charts

I've plotted a map chart in Excel of UK counties that looks like this: What I want is to be able to have an outline of the whole of the country, instead of lots of whitespace for where there is no ...
0
votes
0answers
39 views

Create a “dashboard” by using a “dynamic sumif”

Using the file at the below link, I have created a basic staff planner and Gantt chart. What I am trying to do now, is extract the data from the Gantt chart to produce a dashboard type thing to show ...
0
votes
0answers
28 views

How to get Excel to tell me exactly what the issue is with this file?

We make a software tool that has an export to Excel function. In some cases the exported .xls file has a problem and I have been tasked with fixing it. When opening the exported .xls file in Excel ...
0
votes
1answer
17 views

How to create a Line Chart with a scaled X Axis and Dummy Variables

I'm intending on creating a line chart with three Lines. The Final X axis will display the Values shown in column Time Period. But I am using the column Days as a dummy variable to get the axis spaced ...
0
votes
1answer
10 views

Macro to skip filtered rows

I have the following macro which does the following: Get the file names from a folder on Windows - this is only done if the list and folder is sorted A to Z Combines the name with the folder name ...
0
votes
0answers
30 views

Remove old users to speed up shared workbook

My team use a shared, macro-enabled (.xlsm) workbook, with roughly 25 users in it at the same time, 24 hours a day. Over a few days, the shared user list gets full up, sometimes people's entries ...
0
votes
0answers
24 views

How can I automatically create groups in Excel based on level hierarchy?

I have a lot of data, and I'd like to be able to collapse subordinate levels at every nested tier of a hierarchy of part numbers. The level 0 part is made up of all of the level 1 parts, the level 1 ...
0
votes
1answer
35 views

How to match cell value to cell format

I have cells that appear as 20-Feb, 20-Mar, 20-Apr, etc, when you look at the sheet. When you click on the cell you can see in formula bar 2/20/2021, 3/20/2021, 4/20/2021. I would like to copy and ...
0
votes
1answer
79 views

Excel doesn't sum up numbers with 16 digit

I was doing random calculation and I observed a fatal error in Excel can you test it B2 = 1000000000000000 C2= B2 + 8 ==> excel displays 1000000000000000 D2= C2-B2 ==> excel displays 0 Can ...
0
votes
0answers
62 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 ...
0
votes
0answers
19 views

Get data from the cell of next row in a table, if a cell is found empty

I have to create a formula based table on the next sheet (e.g. Sheet-B), based on the data/table on "Sheet-A". My 'Sheet-A' data has 100+ rows and 30+ columns, where 'Column-A' has text data ...
0
votes
0answers
29 views

How to delete all emoji from Excel list of phrases?

is there a way to fast clean all disturbing emoji from a long excel list of phrases? Like this one? "👧🧒👩🏻 + 3 🐶🐶-8" to be like "+3-8"
0
votes
0answers
64 views

Checking Whether Two Columns Match any Numbers from a Different Column

I was wondering if anyone knew the best way to use one column as a sort of "criteria" column by which two other columns are searched for any numbers that match that criteria column. However, ...
0
votes
1answer
44 views

Irregular label on Excel chart

I have an Excel chart with months on its x-axis. The first date is December 2016 and the last date is November 2020. I need these two dates to be seen, but I can't have all the labels being seen (it's ...

1
2 3 4 5
25