Combine Data Without Copy Pasting (0:00)
Excel provides various ways to merge data from multiple sheets or tables. One of the most popular methods is the "Power Query" tool, which allows users to combine, clean, and transform data.
Fill the Blank Cell with Value from Above (0:44)
If you have a column with intermittent blank cells, you can easily fill them with the value from the cell directly above. Simply select the range, press F5 to open the 'Go To' dialog, click 'Special', select 'Blanks' and click 'OK'. Then, without clicking anywhere else, type =, press the up arrow key, and press Ctrl + Enter.
Running Totals (1:27)
A running total, or cumulative sum, is the summation of a sequence of numbers that is updated each time a new number is added to the sequence. This can be done using a formula like =SUM($A$1:A2) for a running total in column B based on values in column A. As you drag this formula down, it will give a cumulative sum.
Filter Top 10 Values (2:00)
Use the filter feature on a data range. Once activated, click on the dropdown arrow on the column header, navigate to 'Number Filters', and then select 'Top 10'.
How to Highlight the Top 10 Values? (2:28)
Using conditional formatting, you can easily highlight the top 10 values in a range. Select the range of numbers, go to the 'Home' tab, click 'Conditional Formatting', then 'Top/Bottom Rules', and finally choose 'Top 10 Items'.
Autofit in a Jiffy (3:11)
To quickly adjust the size of rows or columns based on the content, double-click the border of the column or row header. Alternatively, you can select multiple rows or columns and right-click to choose the 'Autofit' option.
Format Painter Magic (3:33)
If you want to copy formatting from one cell to another, the Format Painter is a handy tool. Click on the cell with the desired formatting, click the 'Format Painter' icon on the 'Home' tab, and then click on the cell where you want to apply the same formatting.
Many Totals in One Click (4:12)
By selecting a range of numbers and then right-clicking and choosing 'Quick Analysis', you can quickly apply multiple totals like SUM, AVERAGE, COUNT, etc.
Rounding Labels to 1000s (4:43)
If you want to display numbers in thousands, you can use custom number formatting. Right-click on the cells, choose 'Format Cells', go to the 'Number' tab, select 'Custom', and type #,##0, in the Type input.