How to hide formulas in Excel

Hide Formulas in Excel

In this blog post, I will show you how to hide the formulas used in an Excel Worksheet and display only the results of those formulas. Following is an Excel Worksheet used by a H.R Manager, for generating the Payslips of his colleagues. Before sharing this workbook with other executives, the H.R manager wants to … Read more

Protect an Excel Workbook: Passwords, Sheets, Cells and Structure

Protect an Excel Workbook

Excel has three kinds of protection, and they do very different things. A file password encrypts the workbook so it can't be opened. Sheet protection and workbook structure protection only stop accidental changes; they aren't security. Can't open without passwordFile → Info → Encrypt with Password Lock cellsReview → Protect Sheet Lock sheet tabsReview → … Read more

Apply a Formula to an Entire Column in Excel (5 Ways)

Formula to an Entire Column

To put the same formula down a whole column, double-click the fill handle, use Ctrl + Enter, let a dynamic array spill, or turn the data into an Excel Table so new rows get the formula automatically. FastestDouble-click the fill handle Automatic for new rowsExcel Table Works inAll versions (dynamic arrays: 365 and 2021+) Watch … Read more

Reverse Text in Excel (4 Ways)

Reverse Text in Excel

To turn Hana into anaH, Excel 365 can do it with one formula. In older versions, use a one-line VBA function, or Power Query’s Text.Reverse. Excel 365CONCAT + MID + SEQUENCE Any versionStrReverse in VBA Power QueryText.Reverse Watch the short video: Excel formula to Reverse a Text or Number 1. Formula (Excel 365) =CONCAT(MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1))Copy Step … Read more

Highlight cells according to current Time in Excel

Highlight by Time of Day

In a timesheet or schedule with dates across the top and hourly slots down the side, conditional formatting with NOW() highlights the cell for the current date and hour, so you can see at a glance where you are in the day. FunctionNOW() ToolConditional formatting with a formula UpdatesWhenever the sheet recalculates The schedule Dates … Read more

Count Each Item in a List in Excel (4 Ways)

Count Each Item in a List

To see how many times each item appears, use UNIQUE and COUNTIF in Excel 365, GROUPBY for a sorted summary, a PivotTable, or Power Query’s Group By. All four update when the list changes. Excel 365UNIQUE + COUNTIF, or GROUPBY Any versionPivotTable RefreshablePower Query Group By The list The list is an Excel Table named … Read more

Add, Subtract or Multiply a Number Across Many Cells in Excel

Add a Number to Many Cells

To add 10 to hundreds of cells, or raise every price by 5%, use Paste Special → Operation. It changes the values in place, even across separate ranges. A dynamic array formula gives the result in new cells instead. In placePaste Special → Add, Subtract, Multiply, Divide New cells=B2:F6+10 Works inAll versions (formula: Excel 365) … Read more

Find Duplicates in Excel (5 Ways)

Find Duplicates in Excel

Highlight duplicates with conditional formatting, label them with COUNTIF, or list them with FILTER in Excel 365. You can also mark only the second and later copies, which is what you need before deleting. QuickestConditional Formatting → Duplicate Values List themFILTER + COUNTIF Remove themData → Remove Duplicates 1. Highlight with conditional formatting Select the … Read more

Sum of the First 50 Natural Numbers in Excel (5 Ways)

Sum of 1 to 50 in Excel

1 + 2 + 3 + … + 50 = 1,275. It's a classic puzzle, and a neat way to compare Excel tools: a one-line maths formula, SEQUENCE, SUMPRODUCT and ROW, Power Query and VBA all get there. Answer1,275 Shortest=50*51/2 Excel 365=SUM(SEQUENCE(50)) 1. The maths formula =50*(50+1)/2Copy The sum of 1 to n is n(n+1)/2. … Read more