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,878
questions
0
votes
1
answer
164
views
IF function: equals a sum does not work
I have a cell (A40) in an Excel worksheet which contains the sum of a range of values (formula: =SUM(A1:A39)).
Another cell should print "OK" if the sum is 0 and "ERROR" if the sum is not 0. I have ...
1
vote
1
answer
159
views
EXCEL VBA code that searches info between two tabs. And returns what info is missing [closed]
What is the best/easiest way to search through data from two Excel tabs on the same spreadsheet. To see if the field is on another tab. For example, on tab 1 there is a list of fruit and vegetables. ...
2
votes
1
answer
4k
views
How can I use a macro to group and average the values in an Excel column?
I'm new here and I've been struggling with macro writing. I was wondering if anyone could help me out with making/explaining a macro that could be used in my data set.
I have an Excel file that looks ...
1
vote
4
answers
8k
views
Excel: count unique instances across two columns
I have a spreadsheet that has a column for cell sites and another column for frequency like this:
SITE FREQUENCY
MA1084 687
MA1084 687
MA1084 687
MA1084 4382
MA1084 4382
MA1084 ...
2
votes
1
answer
6k
views
Excel - X-axis issues when combining x-y scatter and column charts
I want to superimpose a column chart on an x-y scatter chart. I have done that by adding a series (for the column chart), creating a new y-axis for the values, etc. However, the x-axes don't work out. ...
3
votes
1
answer
3k
views
Regex to find commas, excluding commas inside a string demarcated by double quotes
I would like to manipulate a closed .csv file without opening it through a button on an Excel AddIn. I am able to complete all the steps but am having a problem with finding a regex that would ...
1
vote
2
answers
75
views
How to show excel date custom formate as date
I have in excel cell date in this formate: yyyymmdd (e.g. 20141114)
I tried formating it as but it always shows: #################################################
How to show it as Nov 14, 2014 ?
-1
votes
1
answer
732
views
How to find the highest three-digit "special" number on excel? [closed]
Im trying to use excel to find the highest 3 digit number, which the sum of the cubes of the digits that compose that number is equal to the number itself
Ex: 3^3 + 7^3 + 1^3 = 371
0
votes
1
answer
103
views
Excel Extreme Average Values?
I have encountered this error multiple times. What is even more out of place that it does not do it for the E column only for the other columns, and as I am showing you in the picture I know and have ...
0
votes
1
answer
1k
views
Excel MOD function doesn't work properly if the value is 12AM (00.00)
so I sorted out with the MOD function to calculate my worked hours where I used to start at XX PM/AM and finish at XX AM/PM where without the MOD function I could not calculate properly if I was ...
1
vote
1
answer
976
views
Sort list into tuples in Excel
Is there any way to sort a list into tuples as defined by a priority list in Excel?
For example I would do a draw to get priorities:
Banana -> 1
Apple -> 2
Tomato -> 3
And I have sheet of ...
1
vote
1
answer
415
views
Prevent APPCRASH notification for Excel 2000 running on Windows 8.1
I'm using Office 2000 on Windows 8.1 (a perfectly legal copy bought many years ago). It works great...but although I can work on XLS files, every time I close Excel, the following dialog appears twice:...
0
votes
2
answers
79
views
Excel formulas&subtotals
I have this excel sheet and I have to calculate into the G column the sum of sales until a date ( in G1 the sum until 01.10.2015, in G2 until 02.10.2015, G3 until 03.10.2015 and so on). Can I make it ...
-1
votes
1
answer
100
views
Excel Budget Spreadsheet
I am trying to create a very nuanced budget in Excel. I have a lot of rules about budgeting and thought it would be easier to create a spreadsheet. When a certain line item exceeds budget it is to ...
0
votes
0
answers
33
views
using an excel sheet to update other sheets
I have a work book using three different sheets. The first sheet is a ‘pending sheet’ where info is input row ‘x’ into columns A thru K, and M. Columns L and N are left blank. I am trying to develop a ...
0
votes
1
answer
2k
views
VBA updates the wrong cell
I have recently started a new job. It is a management position and the company doesn't have much in the way of an I.T department. As such, I have been trying to create my own management tools. My ...
0
votes
1
answer
152
views
ID sequence when cell value change
I have a spreadsheet with values like
A
A
B
C
C
C
D
E
I would like to an ID next the them, but the sequence should only increment when the original value changes, so I would get something like
A - ...
0
votes
1
answer
1k
views
Insert formula if blank if next cell in row within range, but freeze data from previous cell/ remove formula
I have some users that are manually entering data from one excel generated from a reporting system and entering in that data into hundreds of cells per day. The format of the report generated excel is ...
0
votes
2
answers
266
views
Excel counting "A"'s with cell values of "A,B" "R,A" etc
Trying to count the number of individual variable in Excel but Countif doesn't seem to fit my needs
For example individual cells have "A","A,B", "A,J", etc and I want to count the number of "A"s - is ...
1
vote
2
answers
716
views
VBA Macro for fill random Dates
i have some 10,000 records in excel file. i want to put random dates for those 10,000 records. Random dates i will give manaully.
For Ex:-
Random dates:- 01-OCt-15 to 30-Oct-15
Emp id Emp Name ...
-1
votes
1
answer
98
views
Manually-generated excel files versus computer-generated reports/files
Why is it the data in a user-generated report (e.g., manually encoded journal entries in an excel file) are destroyed once I append or merge them using Audit Command Language or windows cmd?
Like for ...
0
votes
0
answers
466
views
Excel: How can I tile arrange charts that are on top of another
I copy a chart several times in Excel, but when I paste them they all come one on top of the other, is there a command to arrange them in tiles.
0
votes
1
answer
5k
views
How can I calculate distance between coordinates on multiple matching rows in Excel?
I have a sheet with 20,000 rows. Each row has an object ID, which can occur multiple times throughout the sheet. Each row also contains coordinates, as they are real world points. Each object can ...
0
votes
0
answers
2k
views
How to add colored triangles to the corner(s) of a cell?
I know by default triangles in red or green are indicators of warnings or comments. But what I need here is a solution to create a new format style to a cell, that can add, say four corners, or just ...
0
votes
2
answers
3k
views
How to make a cumulative sum ignore negative numbers in Excel
I am considering data that pertains to a battery system where data in excel cells is the amount of charge added in a time period. What I want is a sum that will essentially give me the amount of ...
0
votes
2
answers
122
views
Excel to get greater result from a sum of 2 columns
Hi,
I have a spreadsheet with soccer results. Each one represented by 2 columns. One column shows the goals for and the other one shows the goals against. The table on sheet 1 would look like this:
...
0
votes
1
answer
54
views
Searching a column for a set of values and listing missing values
I have a column of 45 spaces that will have letters and numbers. I want to write a formula that will search the column for the values 1-10 and list the values found more than once or not found. I ...
1
vote
2
answers
131
views
=IF(VLOOKUP(...)...) formula returning error
I am attempting to write a formula to return a Y or N after it looks something up.
The formula I have is: =IF(VLOOKUP(B6,HIRES!A:A,1,FALSE)>"","Y","N")
I need it to return Y if it finds the ...
3
votes
2
answers
23k
views
How can I sum a column with cells containing text?
I have an Excel sheet containing nearly 6000 products. In the Quantity column, there are some values ending with "60pcs", and some others with "7pkts".
I need to take the total quantity at the end ...
0
votes
1
answer
2k
views
Excel data connection updating all data EXCEPT one specific column
Using Excel 2013.
So I have a workbook (let's called it book B) that pulls a table from another workbook (the source file, book A). No fancy connections or custom queries, I've used the Excel ...
1
vote
1
answer
118
views
Can't calculate % of row in Excel Pivot Table
Product types A, B, C, D
Each type has numerous products, say type A has 100 products, type B 50 etc..
product# product type buyer
01 A john
02 A ...
1
vote
1
answer
2k
views
Creating a distribution chart in Excel
I would like to replicate
in Excel, which groups hundreds of occupations under 12 major job categories, and plots them according to number of jobs and probability of computerisation.
I currently ...
0
votes
1
answer
50
views
View broken path of Excel hyperlink
Editing a working hyperlink shows the destination worksheet selected and the destination cell. However, if that link breaks (eg Sheet name was changed), the Edit Hyperlink window no longer shows where ...
1
vote
1
answer
2k
views
Add Sheet reference to Excel formulas
Is there a way to add sheet references to a range of existing formulas that don't have any sheet references?
Simple Example:
Range A1:A2 of Sheet1
A1: =A1 → =Sheet1!A1
A2: =ROUNDUP(B2,0) → =...
4
votes
3
answers
10k
views
Is there a "see all sheets" keyboard shortcut in Excel 2013 on Windows?
When I right-click the arrow buttons on the bottom left corner of Excel, a window listing all sheets will be displayed (as shown below):
[
I couldn't find any keyboard shortcut to display this, ...
0
votes
1
answer
342
views
Return value with if and vlookup
I'm trying to create a formula that can help me maintain a list.
I have drawing numbers and revisions on one sheet. I need to like bring that to the main sheet for each drawing number that is on the ...
1
vote
1
answer
173
views
Separating character string in Excel 2010
Is there a way to split a string of characters from one cell into separate characters in seperate cells?
For example, the text is 154322JW01. I want to split this into 10 cells with one character ...
2
votes
2
answers
914
views
Open attached spreadsheet in a ppt file with LibreOffice
I have a presentation file created in Microsoft PowerPoint and one of the slides contains a attached spreadsheet created in Microsoft Excel.
I'm using the presentation file with LibreOffice Impress ...
1
vote
2
answers
469
views
Excel Help - Is it possible to return the column number of the 2nd match within a horizontal array?
I'm trying to return the column number that corresponds to the 2nd occurrence of zero in the following table:
B C D E F G
2 0 0 3 4 9
For ...
1
vote
1
answer
59
views
Command that will take me to a specific column? [closed]
I'm looking for a command that will allow me to input a single criteria, the column I'm looking for, and then take me to that column. How can this be done in Excel?
1
vote
1
answer
48
views
Is there a simple or refined way to get the number of times a field 'coexists' with another?
This is driving me nuts. I feel like I'm missing the obvious. Help would be wonderful.
I have a set of data in an Excel spreadsheet that looks like the below:
PRODUCT CATEGORY
fork utensil
...
0
votes
2
answers
113
views
How can I index an exact value repeated in multiple columns in Excel
This application is to list what racks a item would be located in a warehouse.
For example if an item was stored on a few different racks. I don't need a total of how many times the item is repeated, ...
-1
votes
1
answer
143
views
Data analysis with Excel
I want to summarize the data in the following Excel table about school enrolment by District and by Gender using a chart in Excel.
the excel table
The raw data is pasted below:
Enrollment (B- ...
1
vote
1
answer
291
views
call excel 2010 vba from bash menu
I am writing a bash menu function and am having trouble calling a VBA written in excel 2010, from within it. The first line is the path to the VBA. When the uer selects ngs I am trying to have the ...
0
votes
2
answers
290
views
How to sum sales for a set month Excel
I have an excel sheet where im tracking sales, I would like to have an overview of sales each month in another sheet so I can graph it.
my raw dater is below
17/06/2015 4
22/06/2015 8
35/06/2015 ...
0
votes
0
answers
137
views
Wrong decimal separator when inputting axis range in Excel
I have my decimal symbol set to a decimal point in Windows Regional Settings. This particular computer is running Windows 10, but I am experiencing this issue on Windows 7, 8, and 8.1 too.
In ...
4
votes
1
answer
284
views
Apply an Excel VBA function to many files
I have an Excel VBA function and around 30 files with data (and the same data structure) to which I would like to apply the function to.
My approach would be to write a macro in a separate Excel file ...
0
votes
2
answers
2k
views
Excel array functions -- how to count non-blank cells over multiple worksheets?
I have an Excel workbook with numerous worksheets, some of them containing a numeric value in cell A1:
'DATA_1' 'DATA_2' 'DATA_3'
A A A
1 1....
0
votes
1
answer
318
views
In excel 2010, substitute any number corresponding to pattern with look-up value
I'm trying to figure out a way to replace/substitute/regex a specific pattern in an Excel table with the corresponding value from another table.
The pattern, which appears x times inside a text block, ...
1
vote
1
answer
2k
views
Protected view in Office 2010 hangs and can't be turned off
I'm using Office 2010 SP2 on Windows 10. Whenever I open an Excel or Word file, it hangs at "Opening in protected view".
To resolve this issue, I went into Trust Center, disabled trusted view as so:
...