10 times my boss asked for "Excel" help
Power BI
Aug 29, 2023 3:30 PM

10 times my boss asked for "Excel" help

by HubSite 365 about Chandoo

Data AnalyticsPower BILearning Selection

So the bossman wanted some Excel help. Here we go again

These features help in boosting productivity and efficiency when working with Excel spreadsheets. There's always something new to learn in Excel, and the features you've highlighted are just a few that can make your data analysis and presentation much easier!
  1. 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.

  2. 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.

  3. 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.

  4. 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'.

  5. 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'.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. Get Everything in Line with a Click (5:23)
    This likely refers to aligning data. On the 'Home' tab, there are alignment options like 'Align Left', 'Center', and 'Align Right', as well as vertical alignment options.
  11. Powerful Excel Shortcuts (5:57)There are numerous shortcuts in Excel. A few examples include:
    • Ctrl + C - Copy
    • Ctrl + V - Paste
    • Ctrl + Z - Undo
    • Ctrl + Shift + K - Insert a new row
    • Alt + E, S, V - Open the Paste Special dialog
    • Ctrl + Arrows - Move to the edge of data regions
    • F2 - Edit active cell ...and many more.