User Profile
HansVogelaar
MVP
Joined 8 years ago
User Widgets
Recent Discussions
Re: Delete Empty Rows Quick?
Here is an example. For simplicity I used data in columns A to D only, and formulas in column E. I entered the word Filter as a dummy header in E1, and =SUM(--(A2:D2<>"")) in E2, then filled down. Select any cell in the used range. On the Home tab of the ribbon, in the Editing group, select Sort & Filter > Filter. This adds filter drop-down arrows to the header row. As the screenshot shows, you can also press Ctrl+Shift+L to activate the filter arrows. Click the filter arrow in the top cell of the formula column - E1 in my example. Clear the check box (Select All), then select the check for for 0. It should now look like this: Click OK (the OK button is not shown in these screenshots, it is at the bottom of the drop-down menu). All rows with at least one non-blank cell in columns A to D will be hidden, only the empty rows remain: Now select all rows except the top row with the headers. Right-click anywhere in the selection, and select Delete Row from the context menu. Finally, select Sort & Filter > Filter or press Ctrl+Shift+L to turn off the filter. The rows with data will be displayed again. If you wish, you can now delete or clear the column with the formulas (column E in this example).Re: Delete Empty Rows Quick?
Let's say you have data in columns A to T, with headers in A1:T1. Enter the following formula in U2 (i.e. in row 2 in the first empty column): =SUM(--(A2:T2<>"")) Fill down to the last used row. The formulas will return 0 in rows that are completely empty in columns A to T. You can filter column U to display on;y the 0 results. Delete the visible rows, then turn off the filter.Re: How do I format one cell based on values in others cells?
Select J3. On the Home tab of the ribbon, click Conditional Formatting > New Rule... Select 'Use a formula to determine which cells to format'. Enter the formula =COUNTIF(B:B; TODAY()) Click Format... Activate the Fill tab. Select a fill color. Click OK, then click OK again.Re: F/S presentation
Riny's fine reply works well for numbers between -999999 and +999999: If you want to accommodate numbers outside that range, you should set the grouping symbol in your operating system for numbers and currency to a space. Or use a format such as [>=1000000]0 000 000 "$";[<=-1000000]-0 000 000 "$";# ##0 "$" which will work for numbers between -999999999 and +999999999.Re: Conditional Formatting
Let's say you have formulas like that in K2:K100. Select this range On the Home tab of the ribbon, click Conditional Formatting > New Rule... Select 'Format only cells that contain'. Leave the first drop down set to 'Cell Value'. Select 'equal to' from the second drop down. In the box next to it, enter the formula =MAX($K$2:$K$100) Click Format... Activate the Fill tab. Select a fill color. Click OK, then click OK again.Re: How to restore the option to delete row in the edit drop down menu in Excel Checkbook 2023
There are many checkbook templates for Excel. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
