Quick link

• You can add bar charts without inserting them

• Debugging formulas with front-drive and successor functions

• Create and use custom lists

• Save time with navigation pane

• Fix percentage format error

• Capture dynamic snapshots of data

Whether you want to improve spreadsheet efficiency, enhance data analysis, or enhance your career competitiveness, mastering new Microsoft Excel skills can help you. Add these six practical Excel tips to your skill library now!

6 You can add bar charts without inserting them

The first thing we learned in our school’s Excel courses is how to create charts with data. Although this is a necessary skill, sometimes, removing the complex effects of the chart and visualizing data in a simple way is more efficient.

Related ##### 2 ways to quickly visualize Excel data without extra space

Data stripes and mini-pictures are the correct solutions!

Posts In this example, the traditional bar chart takes up too much space and the bars are not aligned with the table rows.

At this time, conditional formatting and REPT functions come in handy. Let’s take a look at these two techniques separately.

Create bar charts using conditional formatting

To create a bar chart within a cell using conditional formatting, first select the cell with the values, and then click Conditional Format in the Start tab.

Then hover over the Data Stripe and select a fill style.

The selected cell is now partially filled according to its relative size in the dataset. The maximum value corresponds to the fully filled cells, while the zero value is completely blank.

What's even better is that if the value changes, the length of the data stripe will be updated synchronously.

But if you want the bar graph to be displayed next to the numerical cell (for example, column B is a numerical value and column C is a bar), you can do this: first select cell C2 and enter:

 <code>=B2</code>

Then press Ctrl Enter to keep it in the current cell.

Then double-click the fill handle to copy the formula to all rows below.

Next, select the data column you just generated, press Ctrl 1 to open the "Format Cell" dialog box, select "Custom" in the "Number" tab, enter;;; (three semicolons) in the text box, and click "OK".

Related##### Want to hide Excel cell content? Try this unpopular trick

Keep the cell structure and hide its numeric values.

Posts 1 Although the C cell looks empty, it actually contains the values copied from column B - it's just invisible.

At this point, just apply the data strips in the conditional format to these "invisible" cells.

Create a bar chart using REPT function

Another way to create a bar chart within a cell is to use the REPT function, which can repeat the specified character several times.

Enter in the adjacent cell where you want to visualize the value:

 <code>=REPT("|",<em>x</em>)</code>

Then press Ctrl Enter.

where "|" is a vertical line character (needed in double quotes), and x is a reference pointing to a numerical cell. For example, referring to B2 here will generate 14 vertical line symbols.

5. Using the front-drive and successor function debugging formulas. I have received emails from my colleagues and readers asking why their Excel formulas do not take effect. Most of the time, I can find the problem by tracking the front-wheel drive and successors in just a few seconds.

Let’s explain these two concepts first:

• Precursor: A cell that affects the value of a certain cell. For example, if the formula of C1 is =SUM(A1 B1), then A1 and B1 are the precursors of C1.
• Successor: Cells that depend on the value of a certain cell. In the same example as above, C1 is the successor of A1 and B1.

Let’s take a look at a simple example: Suppose you enter a formula in B1, and the original intention is to find the average value from A1 to A10, but the result is wrong.

To debug, first select the formula cell and click Tracking Precursor in the Formula tab.

The arrow immediately shows that the front drive only has A1 and A2, which means you missed A3.

Similarly, you can select any cell in column A and click "Tracking Successor" to see which formulas refer to its value.

4 Create and use a custom list Excel to recognize that you may continue to enter "Tue", "Wed", etc. after entering "Mon". Therefore, dragging the fill handle automatically completes the weekly sequence.

The same is true for months: after entering "Jan", pulling down, Excel will automatically fill in "Feb".

How to create a custom list in Excel Create a custom list to avoid repeated manual input of the same content in multiple tables.

Posts For example, suppose you work in the Premier League, you always enter the team name manually. Stop doing this and let Excel do it for you.

Enter the list in the worksheet first, or import from another source.

3 Save time using the navigation pane to switch worksheets in the bottom horizontal tab bar is time consuming and cumbersome, especially when the workbook contains a large number of worksheets.

One solution is to right-click the navigation arrow in the lower left corner to pop up a vertical worksheet list.

Related##### 6 super practical Excel right-click techniques

Don't let your right click be idle!

Posts 2 But I would rather recommend using Excel's navigation pane because it not only lists the worksheet name, but also displays elements such as tables, charts, named areas, etc. in each table, which is equivalent to the dynamic menu of the entire workbook.

To enable the navigation pane, open the View tab and click Navigation.

In the navigation pane, click any worksheet to jump directly, and click the arrow next to the worksheet to locate specific elements.

!

The above is the detailed content of 6 Excel Hacks to Elevate Your Spreadsheet Game. For more information, please follow other related articles on the PHP Chinese website!