Using Excel more powerful

Posted on In

A simple tutorial to use excel more powerful.

Please try these tips on your excel when you reading it.

  • Keyboard Shortcuts
  • Get & Transform Data
  • Text to Columns
  • Formulas
  • Pivot Charts & Tables
  • Cell Format
  • Format Painter
  • Conditional Formatting
  • Quick Access Toolbar

The original title of this post is `Making you excel yourself in Excel’, but it sounds too geek and stupid, so I changed it.

Keyboard Shortcuts

I prefer using more keyboards rather than mouse. Many actions can be implemented by preset shortcuts(like Ctrl + 1) or by Alt leaded shortcuts. Here are just some common shortcuts.

  1. Ctrl & Shift + ↑↓←→

    Ctrl will move cell cursor to the last non-blank cell(That means if there is a blank cell in a row, ctrl will not move to the end but a cell before the blank)

    Shift will select cell will cell cursor moving.

    Then combine these two key can select a area quickly without mouse.

  2. Shift

    With only Shift, you can move cells/rows/columns without replace original data

  3. Ctrl + b/i/u (for bold, italic and underline) or Ctrl + 2/3/4/5 (for bold, italic, underline and strikethrough)

    These shortcuts are same in Word, formatting text in selected cell.

  4. Ctrl + 9/0

    Ctrl + 9/0 can hide row or column of the selected cell.

  5. Ctrl + 1

    I use this key lot, It open the `Format Cell’ Dialogue, like change the number format or cell format.

  6. Ctrl + t

    Create a `Table’(If you don’t know this notion, you can search online or take it as a table in database software).

  7. Alt + = (For quick sum)

    If you only select data and leave no blank row/column, this shortcut only calculate rows sum of each columns; if you leave a blank column, it calculate column sum; if both, calculate both.

    To calculate each parts’ sum, use Ctrl + g shortcut to open ‘Go To’ and choose ‘blank’, then press Alt + =.

  8. Ctrl + Enter

    Fill each cell in your selection with the value you just input.

    I often use it to fill the blank value

  9. Ctrl + Shift + 1/2/3/4/5/6 (for changing number format in selected cells)

  10. Ctrl + Shift + 7 (Add an outside border for entire selected cell)

  11. F4

    When not in selecting areas, F4 will repeat the last action, It’s useful to repeat some formatting work.

    It has a second usage, pressing it when you are reference some area, it will change your selection into absolute reference.

Get & Transform Data

One useful tool is ‘From Web’, it can analysis webpage and extract tables on that page, then you can import it directly and save ‘copy - paste - adjust format’ process.

Text to Columns

This is an excellent tool, you can use it when you have a group of data organized in some pattern but excel cannot recognize it so it is presented as one column in excel.

‘Text to columns’ provides two options, by width or delimiter, mostly we just change the ‘delimiters’ settings and let others default. It’s kind of like a Janet and John version of regular expression (I’ve wrote a post about it, click here[TODO]).

Formulas

Excel is famous for its formulas, and I’m not going to tell a detailed usage of one formula, I just put some formula I often use.

  1. VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

    This function is used to search each value in table A as the key, and the key-value pair stored in table B. Just make sure the table_array is absolute referenced.

  2. LEFT, RIGHT, MIDDLE

    These function are used to manipulate string value, like the slice operator in the array.

  3. &

    & is not a function but it can join two value into one string.

Table, Pivot Charts & Tables

Table

In excel, a file endwith ‘.xls’ or ‘.xlsx’ are called Books, a tab named sheet are called tab, thus there will be a smaller notion called table as table in database.

When you use filter, if there is a blank row, then if you don’t select all data manually, then the default filter will ignore data after blank row, same as table.

After creating a table, many useful action can be done. I’ll only introduce slicer tool and leave others. This slicer can create sort of dynamic chart and make your table better.

Pivot Charts & Tables

Pivot table and pivot charts can be see as one thing, They can also make your table dynamic. It’s a great tool to explore the data.

Cell Format

Well, cell format is a very basic thing in excel, I’ll only show how I create a table with two labels in one cell.

Format Painter

Format Painter is very powerful, you just need to know how to use it.

  1. Use(lock) it more than one time

    Very simple, double click it, then the painter will be selected temoparily, click it to close it after you done you work with it.

  2. Use it with keyboard

    Also simple, click it, and press your keys. I always use it to clear all cell formats in one sheet, by select a blank cell(which has no format) and click format painter, and Ctrl+A, then format whole sheet.

  3. Use multiple cell format

    You can paste more than one cell format to more than one cell, like this.

  4. Use it to make cell like merged but work well with filter.

    Merging cells can make a lot of inconvenience and not make table more beautiful, but painter can paste a ‘merged’ format to make it look like this.

Conditional Formatting

Conditional formatting is another brilliant part of excel. This post will dabble on it, more detailed usage can be easily found online.

Core ideas of conditional formatting is apply which FORMAT on which AREAS with which filter RULES.

Preset rules

They are easy to understand, just try them.

Define your own rules

A very simple example, I want highligh column ‘Item’ if its region is ‘Central’ with red color and yellow if it’s ‘East’ region.

There are some hidden rules that if you define a rule to A cell, then you select an area to apply it, the value in the rule(if not absolute) will follow the change of outside cells.

So it’s simple, I need to determine whether this cell have ‘Central’ or ‘East’, use the ‘FIND’ function. =FIND("Central",B2)>0

Then apply this rule to $D$2:$D$44, and repeat with ‘Yellow’ and ‘East’.

One more tips, if your rules don’t work, try inspect the value of it, cause if you copy or cut some cells, the applied area will be change with it. A good solution is use the Table, then you don’t need to worry this.

Quick Access Toolbar

Like I did in Powerpoint, put some useful option in the QAT, save you from finding these buttons.

Reference

Tools for screen recording:

https://github.com/NickeManarin/ScreenToGif