You just have to change to a Custom format – right click and select format and at the bottom of the list is custom. 0.00##\%;[Red](0.00##\%) The first part of custom format is your defined format you posted. Everything after the semicolon is for negative numbers. [RED] tells Excel to make the negative numbers red and … Read more
The simplest is to do a PivotChart. Select your array of dates (with a header) and create a new Pivot Chart (Insert / PivotChart / Ok) Then on the field list window, drag and drop the date column in the Axis list first and then in the value list first. Step 1: Step 2:
To hide the prompt set xls.DisplayAlerts = False ConflictResolution is not a true or false property, it should be xlLocalSessionChanges Note that this has nothing to do with displaying the Overwrite prompt though! Set xls = CreateObject(“Excel.Application”) xls.DisplayAlerts = False Set wb = xls.Workbooks.Add fullFilePath = importFolderPath & “\” & “A.xlsx” wb.SaveAs fullFilePath, AccessMode:=xlExclusive,ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges wb.Close … Read more
Totero’s answer is correct. The link is also very helpful. Basically the formula you need is: B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) Then press ctrl+shift+enter (or it will not work using a array formula). Two important things to keep in mind here: The complete list is in cells A2:A20, then this formula has to be pasted … Read more
Use the “indirect” function on conditional formatting. Select Conditional Formatting Select New Rule Select “Use a Formula to determine which cells to format” Enter the Formula, =INDIRECT(“g”&ROW())=”X” Enter the Format you want (text color, fill color, etc). Select OK to save the new format Open “Manage Rules” in Conditional Formatting Select “This Worksheet” if you … Read more
Google is full of information on this. As Hans Passant said, Form controls are built in to Excel whereas ActiveX controls are loaded separately. Generally you’ll use Forms controls, they’re simpler. ActiveX controls allow for more flexible design and should be used when the job just can’t be done with a basic Forms control. Many … Read more
Select cell B2 and click “Freeze Panes” this will freeze Row 1 and Column A. For future reference, selecting Freeze Panes in Excel will freeze the rows above your selected cell and the columns to the left of your selected cell. For example, to freeze rows 1 and 2 and column A, you could select … Read more
Quite often the issue is a non-breaking space – CHAR(160) – especially from Web text sources -that CLEAN can’t remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “))) Ron de Bruin has an excellent post on tips … Read more
You can add the image into a comment. Right-click cell > Insert Comment > right-click on shaded (grey area) on outside of comment box > Format Comment > Colors and Lines > Fill > Color > Fill Effects > Picture > (Browse to picture) > Click OK Image will appear on hover over. Microsoft Office … Read more
Unfortunately, there is no formula way to result in a truly blank cell, “” is the best formulas can offer. I dislike ISBLANK because it will not see cells that only have “” as blanks. Instead I prefer COUNTBLANK, which will count “” as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has … Read more