Questions tagged [microsoft-excel]
A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,877
questions
1
vote
0
answers
426
views
macOS Excel missing fonts warning
I keep getting this warning in macOS Excel: "We were unable to load all your fonts. Some fonts may be missing". I tried solutions in Microsoft support doc but nothing work.
0
votes
1
answer
2k
views
Excel add weekly total and daily totals on same graph
I have a finance sheet that tracks all of my expenditures and totals over time. It has a dashboard with a graph that shows the running total on a weekly basis. I also have one that is the running ...
1
vote
2
answers
904
views
IRR() and XIRR() getting radically different results in Excel
I'm trying to use Excel to calculate the IRR on a payment stream. The payment amounts are irregular but they're almost all weekly with a few that vary from 4 to 10 days. I got 121% using IRR(). ...
0
votes
1
answer
134
views
Complicated Sumifs formula?
I have 3 separate excel sheets-
(1) a list of invoices with columns: Invoice Number, customer ID, customer Name, and Amount Due.
(2) a list of products of a certain type- it has columns: invoice ...
0
votes
0
answers
33
views
I need to count how many cells in a column have data in them
This spreadsheet is a list of names not numbers. I am drawing a blank on how to count them when its not numbers. I just need to know the total number of names in each column. I am sure it is simple ...
0
votes
2
answers
563
views
Combine dates with delimiter
I'm trying to combine four cells with dates into one with + as a delimiter. Some of the cells are empty. I tried the textjoin function but Excel returns a date for the empty cells.
Cell one ...
-1
votes
1
answer
100
views
Office 365 Excel - Turn cell matrix into list without blanks
I am trying to take a matrix of 2500x2500 cells (95% of the cells are blank) and create a list without any blanks.
0
votes
0
answers
595
views
How to change a file's encoding?
I am not sure if the question is the right one for my situation, but here is what I want to do:
I've downloaded a .csv file with Chinese characters in it. When opened with MS Excel, the Chinese ...
0
votes
1
answer
543
views
Excel - prefix/suffix text on field after text entry
I would like to enter a string of text into a field and once I have hit Enter to have a constant prefix and suffix added to the text - e.g. if I type BuyLinux or InsultBill into a cell and hit Enter (...
0
votes
1
answer
740
views
How to pick a random cell in list of cells but without duplicating the choice - Excel
How do you populate a cell(s) (B24:B34) from a list of numbers (f2:f12) but without duplicating any of those numbers.
I just want to be able to drag the formula (from B24) down so i can add more ...
0
votes
0
answers
90
views
Submit form data to Excel spreadsheet that is not readable/writable
I am creating a form in Microsoft Word with VBA. I have it submitting data to an Excel workbook. I would like to prevent general read/write access to the spreadsheet while still allowing anybody with ...
1
vote
3
answers
100
views
Prevent false condition from being evaluated
I created an excel formula that convert a string into a date.
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
It's working as expected when a value is specified but blank cell are giving me an error so I ...
2
votes
1
answer
7k
views
Dragging the fill handle in Microsoft Excel—white-gloved hand shows up instead of black crosshair (Mac, Mojave, Excel 16.28)
I want to "drag the fill handle" like so: https://support.office.com/en-us/article/copy-a-formula-by-dragging-the-fill-handle-in-excel-for-mac-dd928259-622b-473f-9a33-83aa1a63e218
But for some reason,...
0
votes
1
answer
33
views
countif as long as they are numbers
how can I set up a formula, that counts all values that are <>0and also =0.
The obvious formula is:
=COUNTIF(A1:E8,"<>0")
How can I integrate all value with ZERO as well.
merci
A
2
votes
1
answer
115
views
Splitting text with very specific whitespace into rows in excel
I have a large list of products that I need to import in a clients site. The data I was given is structured terribly though. To be specific, the product categories are spaced apart using white-space. ...
0
votes
0
answers
252
views
Split string based on delimiter - #value error
My string is separated by an indefinite and variable number of underscores. "SOMETEXT_MORETEXT_ABCD_DEF_..."
I need to put each word inside a different column. I've found these links which explain ...
0
votes
1
answer
604
views
VBA - Mouse hovering over 99 command buttons -- how to loop this?
I wrote the simple code below, but it is soooo inefficient! How can I make the following code more efficient? Objective of code: There are 99 command buttons. The objective is -- if mouse-hovers ...
0
votes
1
answer
151
views
Is there a formula to run in order to remove special characters created in word? (Curly quotes, hyphens, etc.)
I have a .csv document in Microsoft excel that I import into Drupal that gets data pasted into it from Word. But when I try to import it, I usually get errors due to curly quotes, dashes, hyphens and ...
1
vote
2
answers
91
views
Highlight differences in multiple columns including False, 0 and blank?
Related to this question, I am trying to compare a table of settings to identify some configuration issues. I am using the following formula to highlight differences between two known good configs (...
0
votes
1
answer
26
views
Data Date 8/31/18, Excel Graphing 8/1/18
I have a data set that uses the end of month date, i.e., 7/31/18, 8/31/18; however when I graph the data the axis is showing 7/1/18, 8/1/18, etc. I have tried correcting it in the Format Axis pane, ...
0
votes
2
answers
293
views
Changing the structure of csv data
I'm trying to change data looking like this:
original csv
into this format:
name = "The V Girl";
author = "Mya Robarts";
pages = 341;
imageURL = "https://i.gr-assets.com/images/S/compressed.photo....
-2
votes
2
answers
231
views
Nested if formula is too long
This formula uses more levels of nesting than you can use in the current file format
I have a really long nested formula, is there anyway I can make it shorter to work?
=IF(OR(AND(BR2="Closed",BS2="...
0
votes
1
answer
844
views
Excel Power Pivot Data model Sum of Max date groupings
I have a dataset in Excel that I need to summarize. The source table is in a format like below:
Table example
The data extends for years and includes a heap more metadata columns.
The value in each ...
0
votes
1
answer
500
views
Excel generating wrong scale for graph, plotted points disappear when corrected
I'm trying to generate a graph using the values listed in the data tables above the graph; when first generated the scale of the x-axis is wrong and when corrected, the plotted points disappear. How ...
0
votes
0
answers
276
views
Connecting MySQL to Excel for Mac -- "Specified driver could not be loaded"
I have installed the MySQL to Mac OBDC connector that can be found at this link, and followed the instructions that you can find here. But when I go to actually try to pull data into Excel, I get the ...
0
votes
1
answer
429
views
Create date with 15th of month from Month name in Excel
I have the month names in the A - column, like "January" in cell A1.
Furthermore I have the year in the B - column like "2019” in cell B1.
How can I now create a formula to get a result of 15/1/2019 ...
0
votes
2
answers
852
views
Trying to Return First non-blank cell relative to row and column references (INDEX MATCH?)
I am trying to pull the first non-blank 'location' associated with each id and place that in the "initial location" column. Right now, I just have ID and Location, I am trying to create the Initial ...
3
votes
2
answers
1k
views
Keyboard Shortcut - Highlight to bottom of range based on data one column over
I work with similar data pulls frequently and am trying to be more efficient in how I transform them.
Typically, I will autofill a field down to the end of the range one column over by using the ...
0
votes
3
answers
327
views
Excel MID function
What formula can I use to extract variable length strings, where all I know is the beginning and end points of the string. So for example, I have a large list of transactions where I need to extract ...
0
votes
2
answers
54
views
Multiply several columns by Paramater
I have a set of budgeted columns - Jan, Feb, Mar, etc will sales values in a Foreign currency. I want to multiply all monthly columns by a FX rate enter via a Parameter. It this possible?
I know I ...
0
votes
1
answer
37
views
PowerQuery in Excel 'master' spreadsheet on SharePoint can only be refreshed by one user and gives authentication errors for others
I created a group of SharePoint libraries on an intranet that each contain an identically formatted spreadsheet, and another library that contains a “Master” spreadsheet that fills a table from the ...
0
votes
1
answer
111
views
Populating Data from a Column Multiple Times in Excel
I currently have a spreadsheet that contains a repeating set of data in specific columns with one field in each row being blank, such as the following:
I also have a column of numbers I wish to have ...
0
votes
1
answer
25
views
Excel look for a string in a text
I'm currently working in Excel, I have a column with a string like the following one (Column D)
POSTE \EUROPA 7-F\CAIOCAIO\ 20012 ITAITA
Than I have in another column a list with several values (...
0
votes
1
answer
357
views
How to hide the dropdown button of the excel table while printing in Visio Professional 2016
I am inserting an excel sheet through an object (Excel sheet) in a visio and trying to make a diagram using a table. While printing the visio drawing i am getting the dropdown button of the table with ...
3
votes
2
answers
26k
views
How to reverse Excel legend order?
I have an Excel graph (a stacked bar chart) where years are on the x-axis.
The problem is that the order of the legend labels doesn't fit with the order of the values in the bars as shown in the ...
1
vote
1
answer
2k
views
How to split a cell based on a pattern on MS Excel?
I have messy data in which different columns are all entered in the first column on Excel. I want to split the text to different columns, but am struggling with using the 'text to columns' function on ...
1
vote
2
answers
45
views
Microsoft excel you have entered too many arguments
I'm trying to make a function to show if cell values are equal to show 'Paid' if not show 'Pending' and if the cell value is blank to show 'Empty' but I kept getting to many arguments error in MS ...
0
votes
2
answers
190
views
How to subtract adjacent cell value from a cell
How to achieve this ?.
D1=N6-C1,
D2=N6-C2,
D3=N6-C3,
D4=N6-C4
And I have to apply this for more than 1000 cells. Please help.
1
vote
1
answer
3k
views
Sorting and Reordering columns using Excel Formula
I have a set of excel columns as follows (with n number of rows and columns)
I want it to be Sorted and reordered using the group, but I need it to be done using the formula, and not the simple ...
1
vote
1
answer
2k
views
Backup Excel personal.xlsb to OneDrive
I am using OneDrive for Business and I want to backup my Excel personal macro file; personal.xlsb.
I have tried the following methods without success.
Add folder to OneDrive - I cannot seem to ...
0
votes
0
answers
42
views
How do I paste time into a time data validation cell without error?
For most data validation cells with drop down lists, I find that if I paste one of the values in the list exactly, there's no error.
However, with my drop down list of time values (Ex. "5:00 PM", "6:...
0
votes
1
answer
35
views
Need help on a complex nested IF THEN in Excel 2013
I'm trying to do a complex IF THEN statement. If A, then create text according to parameters. If B, create text according to different parameters. And if C, create text according to a new set of ...
0
votes
1
answer
54
views
Subindexing and Marginal Differencing in Power Query (Excel)
I have this dataset:
User Date Time
A 9/1/2019 8:00 AM
A 9/1/2019 10:00 AM
A 9/1/2019 4:00 PM
A 9/1/2019 5:00 PM
A 9/2/2019 8:00 AM
A 9/2/2019 5:00 PM
B 9/1/2019 ...
-1
votes
1
answer
4k
views
VBA to copy entire cell values to clipboard [closed]
Sub Test()
Dim copycell As DataObject
Dim ws As Workbook
Dim strText As String
Set ws = Workbooks.Open("C:\Users\Desktop\New Microsoft Excel Worksheet.xlsx")
strText = ws.Sheets("...
0
votes
1
answer
5k
views
MS Excel. How to filter a pivot table based on input range?
I know that I can go to Row Label filter –> Value Filters –> Greater Than etc. However, I'd like to have two input fields, From and To and I'd like for the pivot table to only display values within ...
0
votes
2
answers
3k
views
Excel: Number stored as text duplicate false-positive
Situation:
I'm creating a rough and hopefully temporary EPOS system using just excel for overall simplicity, and I'm at the point of adding stock to inventory. This requires scanning modified barcodes ...
1
vote
1
answer
71
views
Calculating Large Number Power
I am actually looking for a solution to be able to calculate this Excel formula:
=1-PERMUT(1.15*10^77,358*10^7)/(1.15*10^77)^(358*10^7)
I tried to solve it in Octave too.
Is there any solution for ...
1
vote
2
answers
550
views
MS Excel. How to get around pivoting data that has blank placeholder columns?
I've been asked to produce a dashboard based on a data set that looks something like the screenshot below. The dates are the column headings and there are blank and placeholder columns in between each ...
0
votes
0
answers
42
views
MS Excel. How to analyse dates when they are heading names?
I've been given a data set that consists of hundreds of Stores and almost a hundred dates, below is a small sample. The issue is that dates (mmm-yy) are actually heading names. The reason I see this ...
0
votes
0
answers
1k
views
How to use Get Data From Web in Excel from site needing authentication
How do I use Excel's Get Data > From Web (Data > Get Data > From Other Sources > From Web) feature when the desired web page is behind a login?