Login

r/ExcelTips

▲ 18 r/ExcelTips

Just started first “data” gig. Why’s Excel so fun to get into?

I started as customer service with my company, but recently got promoted by the Client Services director to help with locating trends, and also keeping together data for calls for upcoming “outbound call projects.” He mentioned that in our feedback sessions regarding their Salesforce and website upgrades and mentioned the way that I approached certain issues and solutions I proposed, they felt right giving this opportunity to learn something new and be of assistance, behind the scenes. Great opportunity, I also believe I’m gonna be a great benefit. I only used excel for school work so nothing crazy but as soon as I learned what formulas are and how to make the charts look right, adding calculations/formulas to show results, it’s been so fun and interesting learning about how to make the most and how people have made the most of excel. Applying AI to it makes it so much more fun and of course easier. I’ve used ai to teach me formulas and what each component in the formula means. Ive learned to read existing formulas, but have had AI mostly make my formulas for less room for user error. I give it what I think
Up and we go from there. Feels like I’m gonna do great in this job and I look forward to learning more.

reddit.com
u/Critical-Tennis1897 — 24 hours ago
▲ 4 r/ExcelTips

Title: Reporting automation to combine 12 csv files every week

​

Body: I download 12 csv exports from different tools. Sales, ads, support, shipping. I open each one, clean headers, vlookup into a master sheet, build a pivot, then paste into PowerPoint.

It is 2 hours of manual work and I always worry I pasted the wrong week. Power query breaks when a vendor changes a column name. I do not have power BI budget. I just need something that ingests the files, standardizes them, and spits out the slide with the updated chart. How do you make this truly hands off?

reddit.com
u/davidmaich — 1 day ago
▲ 218 r/ExcelTips

Groupby is actually insane, why did no one tell me.

Hello everyone;

I've been making pivot tables for like 10 years for the dumbest quick sums ever.

last week i was messing around with a sales dump and accidentally typed GROUPBY instead of something else. had no idea this was even a thing.

so i have Region in A and Sales in C. i just did:

=GROUPBY(A2:A5000, C2:C5000, SUM)

and it just... spilled the whole summary. no insert pivot, no drag fields, no refresh. it just lives there.

i used to do this for a 30 second check. now it's one line

if you want it by region AND rep you can just hstack them:

=GROUPBY(HSTACK(A2:A5000,B2:B5000), C2:C5000, SUM)

pivotby does the same but with columns too. honestly i still use normal pivots for proper reports but for quick dirty checks this is so much better.

Anyone else just finding this now or am i late as usual?

Note; you need office 365.

reddit.com
u/Normal-Touch-2396 — 3 days ago
▲ 22 r/ExcelTips

Excel MROUND Function - Use It For Custom Rounding

If you’ve ever looked at a dataset and thought, “Why won’t these numbers round to the specific interval I need?” there’s a good chance standard rounding functions are the issue. Standard formulas like ROUND only care about decimal places, leaving you stranded when you need to round to custom intervals like the nearest 5, 7, 10 or 500.

That’s where MROUND steps in. It’s one of Excel’s most underrated math functions, quietly bringing order to pricing, scheduling, and production data.

Below is a full walkthrough of what MROUND does, when to use it, and how to avoid the hidden quirks that trip up most Excel users.

What MROUND Is & Why It Matters

The MROUND function returns a number rounded to the nearest specified multiple. Unlike standard rounding, which forces you to round to tens, hundreds, or specific decimal places, MROUND lets you round to any step or interval you choose.

You need it when you want to:

  • Normalise pricing: Round items to the nearest $0.05 or $0.99 interval.
  • Streamline logistics: Round order quantities to match fixed box sizes (e.g., multiples of 12 or 50).
  • Simplify scheduling: Round times or durations to the nearest 15-minute block.
  • Clean up reporting: Standardise data variations into predictable, clean increments.

MROUND vs. Standard Rounding

  • ROUND / ROUNDUP / ROUNDDOWN: Scale numbers based strictly on power-of-10 decimal places (e.g., 10, 1, 0.1, 0.01).
  • MROUND: Scales numbers based on custom factors (e.g., 2, 5, 7, 15, 500).

MROUND in Action

Here is how MROUND alters numbers based on different chosen multiples:

Original Number Target Multiple Result Explanation
57 2 58 58 is the closest multiple of 2
26 5 25 25 is closer than 30
57 7 56 56 is the nearest multiple of 7
26 15 30 30 is closer than 15
257 500 500 500 is closer than 0
1.3 0.3 1.2 Works perfectly with decimal multiples too

Formula Structure

=MROUND(number, multiple)
  • number: The core value or cell reference you want to round.
  • multiple: The target interval you want the final number to conform to (e.g., if this is 3, your result will always be a multiple of 3).

Pitfalls to Watch Out For

While MROUND is incredibly useful, Excel handles it with a few rigid rules that can break your spreadsheet if you aren't prepared:

1. The Sign Match Rule (#NUM! Error)

The number and the multiple must share the same mathematical sign (both positive or both negative).

  • =MROUND(-10, -3) outputs -9 (Works perfectly)
  • =MROUND(10, -3) or =MROUND(-10, 3) outputs a #NUM! error.

2. The Midpoint Decimals Glitch

When a number lands exactly halfway between two multiples, Excel rounds up away from zero. However, due to floating-point binary math in Excel, floating midpoints (like 6.05 vs 7.05) can occasionally round in unexpected directions.

To see MROUND in action with a guided walkthrough, take a look at the video I made: https://www.youtube.com/watch?v=xkYWT5yfqrg

u/giges19 — 5 days ago
▲ 18 r/ExcelTips

Best way to extract data from PDF to Excel?

I'm looking for a good to͏ol to extract data from PDF to Excel without destroying the formatting or mixing up columns. Tried a couple random converters already but most need way too much cleanup after esp since most of my docs are scanned. Anyone found something legit and accurate?

reddit.com
u/Physical-West6634 — 10 days ago
▲ 101 r/ExcelTips

30 Essential Excel Shortcuts for Accounting & Finance (Verified for Windows)

I've compiled the 30 most useful shortcuts for accounting and daily data management. All tested on Windows.

File & Workbook Management

  1. Ctrl + N: New Workbook (Creates a new file)
  2. Ctrl + S: Save Workbook (Saves current progress)
  3. Ctrl + W: Close Workbook (Closes the active file)
  4. Ctrl + O: Open Workbook (Opens an existing file)

Data Entry & Editing

  1. F2: Edit Cell (Enters the active cell for editing)
  2. Ctrl + ;: Insert Current Date (Static timestamp)
  3. Ctrl + Shift + :: Insert Current Time (Static timestamp)
  4. Ctrl + D: Fill Down (Copies top cell into selected cells below)
  5. Ctrl + R: Fill Right (Copies leftmost cell into selected cells to the right)
  6. Ctrl + K: Insert Hyperlink (Adds a link to a cell)
  7. Ctrl + Z: Undo (Reverses the last action)
  8. Ctrl + Y: Redo (Repeats the last undone action)

Formatting (The Accountant’s Toolkit)

  1. Ctrl + 1: Format Cells (Opens the full formatting menu)
  2. Ctrl + Shift + %: Apply Percentage Format (0.00%)
  3. Ctrl + Shift + $: Apply Currency Format (Default currency)
  4. Ctrl + Shift + #: Apply Date Format (DD-MMM-YY)
  5. Ctrl + Shift + !: Apply Number Format (Two decimals & thousands separator)
  6. Alt + H + M + C: Merge & Center (Combines selected cells)
  7. Alt + H + B: Add Border (Opens the border menu)

Formulas & Calculations

  1. Alt + =: AutoSum (Inserts a SUM function automatically)
  2. Shift + F3: Insert Function (Opens the function wizard)
  3. F4: Toggle Absolute Reference (Adds $ to cell references in a formula)
  4. Shift + F9: Calculate Active Sheet (Recalculates current worksheet only)
  5. F9: Calculate All (Recalculates all open workbooks)

Data Analysis & Navigation

  1. Ctrl + Shift + L: Toggle Filters (Adds/removes filter arrows)
  2. Ctrl + T: Create Table (Converts range to an official Excel table)
  3. Ctrl + F: Find (Searches for text or values)
  4. Ctrl + H: Replace (Finds and replaces content)
  5. Ctrl + Alt + V: Paste Special (Opens the Paste Special menu)
  6. Alt + E + S + V: Paste Values (Quickly pastes values only, removing formulas)

Note: These are Windows shortcuts. Mac users will generally need to use Cmd instead of Ctrl, and Alt-key sequences may vary

reddit.com
u/Good-Willingness2234 — 12 days ago