برتری داشتن, وبسایت و پروفایل شخصی داود جمیری

نحوه استفاده از اکسل مانند یک حرفه ای: 18 نکته ، ترفند و میانبر آسان در اکسل

گاهی اوقات ، اکسل برای درست بودن خیلی خوب به نظر می رسد. تمام کاری که من باید انجام دهم این است که فرمولی وارد کنم و تقریباً هر کاری که نیاز به انجام دستی داشته ام به صورت خودکار انجام می شود. آیا نیاز به ادغام دو صفحه با داده های مشابه دارید؟ اکسل می تواند این کار را انجام دهد. آیا نیاز به انجام یک ریاضی ساده دارید؟ اکسل می تواند این کار را انجام دهد. آیا نیاز به ترکیب اطلاعات در چندین سلول دارید؟ اکسل می تواند این کار را انجام دهد.

تنها مشکل این است که استفاده از آن برای مبتدیان دشوار است. اگرچه ورود داده ها به صورت دستی آسان است ، اما یادگیری همه فرمول ها و میانبرهای مورد نیاز برای تسلط بر ابزار می تواند یک کابوس باشد. بعلاوه ، حتی اگر از فرمولها به درستی استفاده کنید ، همیشه این احتمال وجود دارد که به پیام های خطا . به زبان ساده ، استفاده از اکسل بسیار دشوار است.

جای نگرانی نیست. در این پست ، من بهترین نکات ، ترفندها و میانبرهایی را که می توانید همین حالا استفاده کنید ، بازی اکسل خود را به مرحله بعدی برسانم. دانش پیشرفته اکسل مورد نیاز نیست.

Download 9 Excel Templates for Marketers [Free Kit]

اکسل چیست؟

Microsoft Excel یک نرم افزار صفحه گسترده است که بازاریابان ، حسابداران ، تحلیلگران داده و سایر متخصصان برای ذخیره ، سازماندهی و ردیابی مجموعه داده ها استفاده می کنند. این بخشی از مجموعه محصولات Microsoft Office است. گزینه های جایگزین شامل Google Sheets و Numbers است. گزینه های اکسل بیشتری را در اینجا پیدا کنید .

برای شروع آموزش Excel خود ، یک فیلم زیر را در مورد نحوه استفاده از Excel برای مبتدیان ببینید.

اکسل به دلیل داشتن قدرت محاسباتی قوی در درجه اول برای ایجاد اسناد مالی مورد استفاده قرار می گیرد. این نرم افزار را اغلب در دفاتر حسابداری و تیم ها پیدا خواهید کرد زیرا به حسابداران اجازه می دهد به طور خودکار مبالغ ، میانگین ها و کل را ببینند. با اکسل ، آنها به راحتی می توانند داده های کسب و کار خود را درک کنند.

در حالی که اکسل در درجه اول به عنوان یک ابزار حسابداری شناخته می شود ، متخصصان در هر زمینه می توانند از ویژگی ها و فرمول های آن استفاده کنند – به ویژه بازاریابان – زیرا می تواند برای ردیابی هر نوع داده استفاده شود. این نیاز شما به صرف ساعت ها و ساعت ها شمردن سلول ها یا کپی و جای گذاری شماره های عملکرد را برطرف می کند. اکسل معمولاً یک میانبر یا راه حل سریع دارد که روند را سرعت می بخشد.

مطمئن نیستید که چگونه واقعاً می توانید از اکسل در تیم خود استفاده کنید؟ در اینجا لیستی از اسنادی که می توانید ایجاد کنید وجود دارد:

در اینجا اسنادی آورده شده است که می توانید به طور خاص برای بازاریابان ایجاد کنید.

این فقط نمونه کوچکی از انواع اسناد بازاریابی و تجاری است که می توانید در Excel ایجاد کنید. ما لیست گسترده ای از الگوهای اکسل ایجاد کرده ایم که می توانید از آنها به درستی استفاده کنید اکنون برای بازاریابی ، صورتحساب ، مدیریت پروژه ، بودجه بندی و موارد دیگر.

برای کلیه نیازهای بازاریابی خود می توانید الگوهای اکسل را در زیر بارگیری کنید.

بعد از بارگیری الگوها ، زمان استفاده از نرم افزار فرا رسیده است. بیایید ابتدا اصول را بیان کنیم.

مبانی اکسل

اگر تازه کار با Excel را شروع کرده اید ، چند دستور اساسی وجود دارد که پیشنهاد می کنیم با آنها آشنا شوید. این موارد مانند: است

  • ایجاد صفحه گسترده جدید از ابتدا.
  • اجرای محاسبات اساسی مانند جمع ، تفریق ، ضرب و تقسیم.
  • نوشتن و قالب بندی متن و عنوان ستون.
  • استفاده از ویژگیهای خودکار پر کردن اکسل.
  • افزودن یا حذف تک ستون ها ، ردیف ها و صفحات گسترده. در زیر ، نحوه افزودن مواردی مانند چندین ستون و ردیف را بررسی خواهیم کرد.
  • در هنگام عبور از کنار آنها در صفحه گسترده ، عناوین ستون و ردیف قابل مشاهده باشند تا بدانید که هنگام حرکت به پایین سند ، چه داده هایی را پر می کنید.

برای تعمیق عمیق این اصول ، به راهنمای جامع Microsoft Excel مراجعه کنید .

با توجه به روحیه کارکرد موثرتر و پرهیز از کار خسته کننده و دستی ، در اینجا چند فرمول و عملکرد اکسل آورده شده است که باید بدانید.

فرمول های اکسل

تحت تأثیر قرار دادن دامنه گسترده ای از آسان است. فرمولهایی که می توانید برای درک اطلاعات خود استفاده کنید. اگر تازه شروع به استفاده از اکسل کرده اید ، می توانید برای انجام برخی عملکردهای پیچیده – بدون افزودن به پیچیدگی مسیر یادگیری خود ، به فرمول های زیر اعتماد کنید.

  • علامت برابر: قبل از ایجاد هر فرمولی ، باید علامت برابر (=) را در سلولی که می خواهید بنویسید نتیجه ظاهر می شود.
  • افزودن : برای افزودن مقادیر دو یا چند سلول ، از علامت + استفاده کنید. مثال: = C5 + D3 .
  • تفریق : برای کم کردن مقادیر دو یا چند سلول ، از علامت استفاده کنید. مثال: = C5-D3 .
  • ضرب : برای ضرب مقادیر دو یا چند سلول ، از علامت * استفاده کنید. مثال: = C5 * D3 .
  • بخش : برای تقسیم مقادیر دو یا چند سلول ، از علامت / استفاده کنید. مثال: = C5 / D3 .

با جمع کردن همه اینها ، می توانید فرمولی ایجاد کنید که همه را در یک سلول جمع ، تفریق ، ضرب و تقسیم کند. مثال: = (C5-D3) / ((A5 + B6) * 3) .

برای فرمولهای پیچیده تر ، شما باید از پرانتز در اطراف عبارات استفاده کنید تا از استفاده تصادفی از ترتیب عملیات PEMDAS . به خاطر داشته باشید که می توانید از اعداد ساده در فرمول های خود استفاده کنید.

توابع اکسل

توابع اکسل برخی از کارهایی را که در فرمول معمول استفاده می کنید ، خودکار می کند. به عنوان مثال ، به جای استفاده از علامت + برای جمع کردن طیف وسیعی از سلول ها ، از عملکرد SUM استفاده می کنید. بیایید چند عملکرد دیگر را بررسی کنیم که به شما کمک می کند تا محاسبات و کارها به صورت خودکار انجام شود.

  • SUM : عملکرد SUM به طور خودکار طیف وسیعی از سلول ها یا اعداد را جمع می کند. برای تکمیل یک جمع ، شما می توانید سلول شروع و سلول نهایی را با یک دو نقطه در وسط وارد کنید. این شکل ظاهری این است: SUM (Cell1: Cell2) . مثال: = SUM (C5: C30) .
  • AVERAGE : عملکرد AVERAGE به طور متوسط ​​مقادیر محدوده سلول هاست. نحو همان عملکرد SUM است: AVERAGE (Cell1: Cell2). مثال: = متوسط ​​(C5: C30) .
  • IF : عملکرد IF به شما امکان می دهد مقادیر را بر اساس یک تست منطقی برگردانید. نحو به شرح زیر است: IF (تست_منطقی ، مقدار_اگر_راست ، [ارزش_اگر_سطحی]) . مثال: = IF (A2> B2 ، “بیش از حد بودجه” ، “تأیید”) .
  • VLOOKUP : عملکرد VLOOKUP به شما کمک می کند هر چیزی را در ردیف های صفحه خود جستجو کنید. نحو عبارت است از: VLOOKUP (مقدار جستجو ، آرایه جدول ، شماره ستون ، مطابقت تقریبی (TRUE) یا مطابقت دقیق (FALSE)) . به عنوان مثال:
  • INDEX : تابع INDEX مقداری را از یک محدوده برمی گرداند. نحو به شرح زیر است: INDEX (آرایه ، ردیف_نم ، [ستون_نم]) .
  • MATCH : تابع MATCH مورد خاصی را در محدوده سلول ها جستجو می کند و موقعیت آن مورد را برمی گرداند. می تواند همزمان با عملکرد INDEX استفاده شود. نحو عبارت است از: MATCH (مقدار_نمایه ، آرایه_نمایش ، [نوع_تطبیق]) .
  • COUNTIF : تابع COUNTIF تعداد سلول هایی را که مطابق با معیارهای خاصی هستند یا مقدار مشخصی دارند برمی گرداند. نحو این است: COUNTIF (دامنه ، معیارها). مثال: = COUNTIF (A2: A5 ، “London”).

خوب ، آماده ورود به نیتریت-گریت هستید؟ بیایید به آن برسیم. (و همه طرفداران هری پاتر در آنجا … پیشاپیش خوش آمدید.)

توجه : تصاویر GIF و تصاویر مربوط به نسخه قبلی Excel است. در صورت لزوم ، نسخه به روز شده است تا دستورالعمل هایی را برای کاربران نسخه های جدیدتر و قدیمی اکسل ارائه دهد.

1 برای شناسایی و ایجاد معنا در داده ها از جداول محوری استفاده کنید.

جداول محوری برای سازماندهی مجدد داده ها در صفحه گسترده استفاده می شود. آنها داده هایی را که شما دارید تغییر نخواهند داد ، اما بسته به کاری که می خواهید انجام دهند می توانند مقادیر را جمع بندی کرده و اطلاعات مختلف را در صفحه گسترده شما مقایسه کنند.

بیایید به یک مثال نگاهی بیندازیم. بیایید بگوییم می خواهم نگاهی به تعداد افراد هر خانه در هاگوارتز بیندازم. ممکن است فکر کنید که من داده های زیادی ندارم ، اما برای مجموعه داده های طولانی تر ، این کار مفید خواهد بود.

برای ایجاد جدول محوری ، به داده > جدول محوری می روم. اگر از جدیدترین نسخه Excel استفاده می کنید ، می توانید به درج > جدول محوری بروید. اکسل به صورت خودکار جدول محوری شما را پر می کند ، اما شما همیشه می توانید در ترتیب داده ها تغییر دهید. سپس ، چهار گزینه برای انتخاب دارید.

  • گزارش فیلتر : به شما این امکان را می دهد که فقط به سطرهای خاصی در مجموعه داده خود نگاه کنید. به عنوان مثال ، اگر می خواستم یک فیلتر از طریق خانه ایجاد کنم ، می توانم به جای همه دانش آموزان ، فقط دانشجویان را در Gryffindor بگنجانم.
  • برچسب های ستون : این عنوان های شما در مجموعه داده خواهد بود.
  • برچسب های ردیف : این می تواند ردیف های شما در مجموعه داده باشد. هر دو برچسب Row و Column می توانند حاوی داده هایی از ستون های شما باشند (به عنوان مثال نام را می توان به برچسب Row یا Column کشید – این فقط به نحوه مشاهده داده ها بستگی دارد.)
  • مقدار : این بخش به شما امکان می دهد داده های خود را متفاوت ببینید. به جای اینکه فقط مقدار عددی را بکشید ، می توانید عدد را جمع کنید ، بشمارید ، به طور متوسط ​​، حداکثر ، دقیقه را بشمارید ، یا چند دستکاری دیگر با داده های خود انجام دهید. در حقیقت ، به طور پیش فرض ، وقتی فیلدی را به سمت Value می کشید ، همیشه شمارش انجام می شود.

از آنجا که می خواهم تعداد دانش آموزان هر خانه را حساب کنم ، به سراغ جدول سازنده Pivot می روم و ستون House را به سمت Row Labels و Values ​​می کشم. این تعداد دانش آموزان مرتبط با هر خانه را خلاصه می کند. Excel pivot table creation

2. بیش از یک ردیف یا ستون اضافه کنید.

هنگامی که با داده های خود بازی می کنید ، ممکن است متوجه شوید که دائماً به افزودن سطرها و ستون های بیشتر احتیاج دارید. حتی گاهی ممکن است لازم باشد صدها ردیف اضافه کنید. انجام این تک به تک بسیار خسته کننده خواهد بود. خوشبختانه ، همیشه راه ساده تری وجود دارد.

برای افزودن چندین ردیف یا ستون در یک صفحه گسترده ، همان تعداد ردیف یا ستون موجود را که می خواهید اضافه کنید برجسته کنید. سپس ، کلیک راست کرده و “درج” را انتخاب کنید

در مثال زیر ، می خواهم سه ردیف اضافی اضافه کنم. با برجسته کردن سه ردیف و سپس کلیک کردن روی insert ، می توانم سه ردیف خالی اضافی را به سرعت و به راحتی در صفحه گسترده خود اضافه کنم.

insert Spaces on Excel

3 برای ساده کردن داده های خود از فیلترها استفاده کنید.

وقتی به مجموعه داده های بسیار بزرگ نگاه می کنید ، معمولاً نیازی نیست که به طور همزمان همه ردیف ها را جستجو کنید. گاهی اوقات ، شما فقط می خواهید به داده هایی نگاه کنید که در معیارهای خاصی قرار دارند.

در اینجا فیلترها وارد می شوند.

فیلترها به شما امکان می دهند داده های خود را تجزیه و تنظیم کنید تا فقط در یک زمان ردیف های خاصی را مشاهده کنید. در اکسل ، می توان به هر ستون داده ها یک فیلتر اضافه کرد – و از آنجا می توانید سلول های مورد نظر خود را برای مشاهده همزمان انتخاب کنید.

بیایید نگاهی به مثال زیر بیندازیم. با کلیک روی برگه داده و انتخاب “فیلتر” یک فیلتر اضافه کنید. با کلیک بر روی پیکان کنار سرصفحه های ستون ، می توانید انتخاب کنید که آیا می خواهید داده های شما به ترتیب صعودی یا نزولی مرتب شوند و همچنین ردیف های خاصی را که می خواهید نشان دهید.

در مثال من از هری پاتر ، بگذارید بگوییم من فقط می خواهم دانش آموزان را در گریفندور ببینم. با انتخاب فیلتر Gryffindor ، سایر ردیف ها ناپدید می شوند.

Excel filters in action نکته حرفه ای : وقتی فیلتر فعال است مقادیر را در صفحه گسترده کپی و جای گذاری کنید تا تجزیه و تحلیل بیشتری در صفحه گسترده دیگر انجام دهد .

4 نقاط یا مجموعه داده های تکراری را حذف کنید.

مجموعه داده های بزرگتر دارای محتوای تکراری هستند. شما ممکن است لیستی از چندین مخاطب در یک شرکت داشته باشید و فقط بخواهید تعداد شرکت های خود را ببینید. در شرایطی از این دست ، حذف موارد تکراری کاملاً مفید است.

برای حذف موارد تکراری ، سطر یا ستونی را که می خواهید موارد تکراری را حذف کنید برجسته کنید. سپس ، به تب Data بروید و “Remove Duplicates” (که در زیرعنوان Tools در نسخه قدیمی اکسل قرار دارد) را انتخاب کنید. برای تأیید اینکه با کدام داده می خواهید کار کنید ، یک پنجره بازشو ظاهر می شود. “حذف موارد تکراری” را انتخاب کنید و کار شما خوب است.

Removing duplicates in Excel

همچنین می توانید برای حذف کل ردیف بر اساس مقدار ستون تکراری ، از این ویژگی استفاده کنید. بنابراین اگر سه ردیف با اطلاعات هری پاتر دارید و فقط باید یک مورد را ببینید ، پس می توانید کل مجموعه داده را انتخاب کرده و سپس نسخه های تکراری را بر اساس ایمیل حذف کنید. لیست منتخب شما فقط دارای نامهای منحصر به فرد و بدون هیچ گونه تکراری خواهد بود.

5. ردیف ها را به ستون ها منتقل کنید.

وقتی در صفحه گسترده خود ردیف داده دارید ، ممکن است تصمیم بگیرید که واقعاً می خواهید موارد موجود در یکی از آن سطرها را به ستون تبدیل کنید (یا بالعکس). برای کپی و چسباندن هر سرآیند جداگانه زمان زیادی لازم است – اما آنچه ویژگی جابجایی به شما امکان می دهد انجام دهید این است که داده های ردیف خود را به راحتی به ستون ها منتقل کنید یا روش دیگر.

با برجسته سازی ستونی که می خواهید به سطرها منتقل کنید شروع کنید. روی آن کلیک راست کنید و سپس “کپی” را انتخاب کنید. بعد ، سلولهای صفحه گسترده خود را در جایی که می خواهید اولین ردیف یا ستون شما در آنجا شروع شود ، انتخاب کنید. بر روی سلول کلیک راست کرده و سپس “Paste Special” را انتخاب کنید. یک ماژول ظاهر می شود – در پایین ، گزینه ای برای جابجایی را مشاهده خواهید کرد. آن کادر را علامت بزنید و تأیید را انتخاب کنید. اکنون ستون شما به یک ردیف یا بالعکس منتقل می شود.

Transpose tool in Excel

در نسخه های جدیدتر Excel ، یک منوی کشویی به جای پنجره بازشو ظاهر می شود.

Excel transpose tool in newer versions

6. اطلاعات متن را بین ستون ها تقسیم کنید.

اگر می خواهید اطلاعات موجود در یک سلول را به دو سلول مختلف تقسیم کنید ، چه می کنید؟ به عنوان مثال ، شاید شما بخواهید نام شرکت شخصی را از طریق آدرس ایمیل وی بیرون بیاورید. یا شاید می خواهید نام کامل کسی را به یک نام و نام خانوادگی برای الگوهای بازاریابی ایمیل خود جدا کنید.

با تشکر از اکسل ، هر دو امکان پذیر است. ابتدا ستونی را که می خواهید تقسیم کنید برجسته کنید. بعد ، به تب Data بروید و “نوشتار به ستون” را انتخاب کنید. یک ماژول با اطلاعات اضافی ظاهر می شود.

در ابتدا ، شما باید “محدود شده” یا “عرض ثابت” را انتخاب کنید.

  • “محدود شده” به این معنی است که شما می خواهید ستون را بر اساس کاراکترهایی مانند ویرگول ، فاصله یا برگه ها بشکنید.
  • “عرض ثابت” به این معنی است که می خواهید مکان دقیق را در تمام ستون هایی که می خواهید تقسیم رخ دهد انتخاب کنید.

در مثال زیر ، بیایید “Delached” را انتخاب کنیم تا بتوانیم نام کامل را به نام و نام خانوادگی تفکیک کنیم.

سپس ، زمان انتخاب Delimiters فرا رسیده است. این می تواند یک برگه ، نیمه کولون ، ویرگول ، فضا یا چیز دیگری باشد. (به عنوان مثال “چیز دیگری” می تواند علامت “@” باشد که در آدرس ایمیل استفاده می شود.) در مثال ما ، بیایید فضا را انتخاب کنیم. سپس اکسل پیش نمایشی از شکل ظاهری ستون های جدید به شما نشان می دهد.

وقتی از پیش نمایش راضی هستید ، “بعدی” را فشار دهید. این صفحه به شما امکان می دهد در صورت تمایل ، Advanced Formats را انتخاب کنید. پس از پایان کار ، روی “پایان” کلیک کنید.

Excel text to column tool

7. از فرمول ها برای محاسبات ساده استفاده کنید.

علاوه بر انجام محاسبات کاملاً پیچیده ، اکسل می تواند به شما در انجام محاسبات ساده مانند جمع کردن ، کم کردن ، ضرب یا تقسیم هر یک از داده ها کمک کند.

  • برای افزودن ، از علامت + استفاده کنید.
  • برای کم کردن ، از علامت – استفاده کنید.
  • برای ضرب ، از علامت * استفاده کنید.
  • برای تقسیم ، از علامت / استفاده کنید.

برای اطمینان از اینکه ابتدا محاسبات خاصی انجام می شود می توانید از پرانتز نیز استفاده کنید. در مثال زیر (10 + 10 * 10) ، 10 دوم و سوم قبل از اضافه کردن 10 اضافی با هم ضرب می شوند. با این حال ، اگر ما آن را بسازیم (10 + 10) * 10 ، 10 اول و دوم ابتدا با هم جمع می شوند .

Excel simple formulas in action 8. به طور متوسط ​​اعداد را در سلولهای خود دریافت کنید.

اگر میانگین مجموعه ای از اعداد را می خواهید ، می توانید از فرمول = متوسط ​​(Cell1: Cell2) استفاده کنید. اگر می خواهید ستونی از اعداد را جمع بندی کنید ، می توانید از فرمول = SUM (Cell1: Cell2) استفاده کنید.

9. از قالب بندی مشروط استفاده کنید تا سلول ها براساس داده ها به طور خودکار تغییر رنگ دهند.

قالب بندی شرطی به شما امکان می دهد رنگ سلول را براساس اطلاعات موجود در سلول تغییر دهید. به عنوان مثال ، اگر می خواهید اعداد خاصی را که بالاتر از حد متوسط ​​هستند یا در 10٪ بالای داده های صفحه گسترده خود قرار دهید ، می توانید این کار را انجام دهید. اگر می خواهید اشتراکات کد بین ردیف های مختلف در Excel را رنگ کنید ، می توانید این کار را انجام دهید. این به شما کمک می کند تا به سرعت اطلاعاتی را که برای شما مهم هستند مشاهده کنید.

برای شروع ، گروه سلولهایی را که می خواهید از قالب بندی مشروط در آنها استفاده کنید برجسته کنید. سپس ، “قالب بندی مشروط” را از فهرست اصلی انتخاب کرده و منطق خود را از لیست کشویی انتخاب کنید. (اگر می خواهید چیز متفاوتی نیز داشته باشید می توانید قانون خود را ایجاد کنید.) پنجره ای ظاهر می شود که از شما می خواهد اطلاعات بیشتری در مورد قانون قالب بندی خود ارائه دهید. پس از اتمام “تأیید” را انتخاب کنید و باید نتایج خود را به صورت خودکار نشان دهید.

Excel conditional formatting

10. از فرمول IF Excel برای خودکارسازی عملکردهای خاص اکسل استفاده کنید.

گاهی اوقات ، نمی خواهیم تعداد دفعاتی که یک مقدار ظاهر می شود را بشماریم. در عوض ، اگر سلول مربوطه با آن اطلاعات وجود داشته باشد ، می خواهیم اطلاعات مختلفی را وارد سلول کنیم.

به عنوان مثال ، در شرایط زیر ، من می خواهم به هر کسی که در خانه گریفیندور است ، ده امتیاز اعطا کنم. به جای تایپ دستی 10 در کنار نام هر دانشجوی گریفیندور ، می توانم از فرمول IF Excel استفاده کنم و بگویم اگر دانشجو در گریفندور است ، پس باید ده امتیاز کسب کند.

فرمول این است: IF (logical_test، value_if_true، [value_if_false])

نمونه ای که در زیر نشان داده شده است: =IF(D2=”Gryffindor “،” 10 “،” 0 “)

به طور کلی ، فرمول IF خواهد بود (تست منطقی ، مقدار درست ، مقدار نادرست). بیایید هر یک از این متغیرها را جستجو کنیم.

  • Logical_Test : تست منطقی قسمت “IF” عبارت است. در این حالت ، منطق D2 = “Gryffindor” است زیرا ما می خواهیم اطمینان حاصل کنیم که سلول مربوط به دانشجو “Gryffindor” را می گوید. مطمئن شوید که Gryffindor را در علامت های نقل قول قرار دهید.
  • Value_if_True : این همان چیزی است که ما می خواهیم سلول در صورت درست بودن مقدار نشان دهد. در این حالت ، ما می خواهیم سلول “10” را نشان دهد تا 10 امتیاز دانش آموز را نشان دهد. Only use quotation marks if you want the result to be text instead of a number.
  • Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show “0”. Only use quotation marks if you want the result to be text instead of a number.

Excel IF formula in action

Note: In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted to sum the total number of points, I wouldn’t be able to because the 10’s are in quotes, thus making them text and not a number that Excel can sum.

11 Use dollar signs to keep one cell’s formula the same regardless of where it moves.

Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn’t representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.

You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you’re actually referring to a cell that’s five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it’ll adjust the values in the formula based on where it’s moved. But sometimes, we want those values to stay the same no matter whether they’re moved around or not — and we can do that by turning the formula into an absolute formula.

To change the relative formula (=A5+C5) into an absolute formula, we’d precede the row and column values by dollar signs, like this: (=$A$5+$C$5). (Learn more on Microsoft Office’s support page here.)

12 Use the VLOOKUP function to pull data from one area of a sheet to another.

Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?

For example, you might have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.

I have to combine data sets like this a lot — and when I do, the VLOOKUP is my go-to formula.

Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.

The formula: =VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE))

The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.

  • Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
  • Table Array: The table array is the range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
  • Column Number: This tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that “House” is located in. “House” is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the “2” to a “3” so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
  • Approximate Match (TRUE) or Exact Match (FALSE): Use FALSE to ensure you pull in only exact value matches. If you use TRUE, the function will pull in approximate matches.

In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.

Excel VLOOKUP function

So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.

Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.

13. Use INDEX and MATCH formulas to pull data from horizontal columns.

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:

  • VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, using the INDEX and MATCH function will significantly decrease load time in Excel.
  • The INDEX and MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.

So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren’t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I’d choose to do an INDEX and MATCH instead.

Let’s look at an example. Let’s say Sheet 1 contains a list of people’s names and their Hogwarts email addresses, and Sheet 2 contains a list of people’s email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a “Patronus” associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I’d use the INDEX and MATCH formulas instead of VLOOKUP so I wouldn’t have to switch any columns around.

So what’s the formula, then? The formula is actually the MATCH formula nested inside the INDEX formula. You’ll see I differentiated the MATCH formula using a different color here.

The formula: =INDEX(table array, MATCH formula)

This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))

The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Here are the variables:

  • Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, “A” means Column A, which contains the “Patronus” information for each person.
  • Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the “email” column on Sheet 1, which is Column C. So: Sheet1!C:C.
  • Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the “email” column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.

Once you have your variables straight, type in the INDEX and MATCH formulas in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live.

Excel INDEX and MATCH functions in action

14. Use the COUNTIF function to make Excel count words or numbers in any range of cells.

Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.

For example, let’s say I want to count the number of times the word “Gryffindor” appears in my data set.

The formula: =COUNTIF(range, criteria)

The formula with variables from our example below: =COUNTIF(D:D,”Gryffindor”)

In this formula, there are several variables:

  • Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
  • Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”

Simply typing in the COUNTIF formula in any cell and pressing “Enter” will show me how many times the word “Gryffindor” appears in the dataset.

Excel COUNTIF function

15. Combine cells using &.

Databases tend to split out data to make it as exact as possible. For example, instead of having a column that shows a person’s full name, a database might have the data as a first name and then a last name in separate columns. Or, it may have a person’s location separated by city, state, and zip code. In Excel, you can combine cells with different data into one cell by using the “&” sign in your function.

The formula with variables from our example below: =A2&” “&B2

Let’s go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we’d first put our cursor in the blank cell where we want the full name to appear. Next, we’d highlight one cell that contains a first name, type in an “&” sign, and then highlight a cell with the corresponding last name.

But you’re not finished — if all you type in is =A2&B2, then there will not be a space between the person’s first name and last name. To add that necessary space, use the function =A2&” “&B2. The quotation marks around the space tell Excel to put a space in between the first and last name.

To make this true for multiple rows, simply drag the corner of that first cell downward as shown in the example.

Excel combination of cells

16. Add checkboxes.

If you’re using an Excel sheet to track customer data and want to oversee something that isn’t quantifiable, you could insert checkboxes into a column.

For example, if you’re using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you’ve called the respective client.

Here’s how to do it.

Highlight a cell you’d like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Excel checkboxes

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you’re using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you’re tracking, here’s how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn’t working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink.

18 Add drop-down menus.

Sometimes, you’ll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you’re tracking.

Here’s how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

Excel drop-down menu option

From there, you’ll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you’re not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you’ll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor’s Note: This post was originally published in August 2017 but has been updated for comprehensiveness.

New Call-to-Action

نوشته ها مرتبط

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *