Фишки Excel, которые я использую КАЖДЫЙ ДЕНЬ! ЭТО нужно каждому

Seven practical Excel tips (demo + how-to)

Short summary of a demo by Olga Pivikova (founder of Excel Club). Focus: speed up routine work, make reports easier to read, and automate repetitive tasks.

Tip 1 — Visual hierarchy with fills

Make reports easier to scan by applying a gradient or darker-to-lighter fill per hierarchy level (for example: activity type → category → article → sub-article).

How-to

• Apply fills by level (darker for top-level, lighter for sub-levels).
• Use Format Painter in locked mode: double‑click the paint roller to copy formatting to many non-contiguous cells; press Esc to stop.

Tip 2 — Proper indentation without adding spaces

Avoid inserting space characters (which break pivots/lookups). Use cell formatting to shift text visually while keeping the data clean.

How-to

• Right‑click the cell → Format Cells → Alignment → Increase “Indent” (choose a value such as 3–6).

Tip 3 — Hide zero values visually with number format

Hiding zeros with a custom number format is cleaner and faster than conditional formatting — the cell still contains 0 for calculations.

How-to

• Format Cells → Number → Custom.
• Use the three-section format syntax (positive;negative;zero). To hide zeros, leave the third section blank.
• Also use a thousands separator and remove unnecessary decimals to improve readability.

Tip 4 — Delete empty rows quickly

Fast way to remove blank subtotal/empty rows when cleaning databases.

How-to

• Select the column that identifies empty rows.
• Press F5 (Go To) → Special → Blanks.
• Right‑click any selected blank cell → Delete → Entire row.

Tip 5 — Move columns/rows without breaking formulas

Move data around safely so formulas keep referencing correctly.

How-to

• Select the column(s), move the cursor to the border until move-arrows appear.
• Hold Shift and drag to relocate — this preserves formulas.
• Note: only contiguous columns/rows can be moved together; omitting Shift will replace columns instead of moving them.

Tip 6 — Combine many files in one click using Power Query

Power Query automates importing and merging multiple files, records the transformation steps, and makes the process repeatable.

How-to

• Data → Get Data → From File → From Folder → select the folder → Combine & Load (or Transform).
• Power Query shows a source filename column, lets you sort/process, and stores the applied steps for reuse.

Tip 7 — Make a worksheet truly hidden

Use the VeryHidden worksheet property so the sheet does not appear in View → Unhide.

How-to

• Enable Developer tab → Visual Basic (VBE).
• In Project Explorer select the worksheet → open Properties window → set Visible = VeryHidden.
• To restore, change Visible back to Visible in VBE.

Other notable points

• Prefer number formats with thousands separators and minimal decimals to improve readability.
• Avoid manual space characters for indentation — they break pivot and lookup aggregation.
• Double‑clicking Format Painter is faster than repeatedly clicking it.
• Power Query is a powerful automation tool; the presenter offers a course on it.
• The presenter recommends the Excel Club online school (courses from beginner to advanced); link is available in the original video description.

Main speaker / sources

• Olga Pivikova — presenter, founder of Excel Club (main speaker/source)
• Excel Club — online school referenced (course and community)